class SqlHelper { //通过ConfigurationManager可以读到App.config中的配置信息 private static string conStr = ConfigurationManager.ConnectionStrings["dbConStr"].ConnectionString; /// <summary> /// 将null转换成数据库中的DBNull /// </summary> /// <param name="value"></param> /// <returns></returns> public static object ToDBValue(object value) { if (value == null) value = DBNull.Value; return value; } /// <summary> /// 从数据库中获得DBNull数据转换成null /// </summary> /// <param name="value"></param> /// <returns></returns> public static object FromDBValue(object value) { if (value == DBNull.Value) value = null; return value; } /// <summary> /// 执行不查询sql语句 /// </summary> /// <param name="sql">要执行的sql语句</param> /// <returns>执行完sql语句后,数据库受影响的行数</returns> public static int ExecuteNonQuery(string sql,params SqlParameter[] parameters) { using (SqlConnection sqlCon = new SqlConnection(conStr)) { sqlCon.Open(); using (SqlCommand sqlCmd = sqlCon.CreateCommand()) { sqlCmd.CommandText = sql; //foreach (SqlParameter param in parameters) //{ // sqlCmd.Parameters.Add(param); //} sqlCmd.Parameters.AddRange(parameters); return sqlCmd.ExecuteNonQuery(); } } } /// <summary> /// 执行查询 /// </summary> /// <param name="sql">要执行的sql语句</param> /// <returns>返回查询结果中的第一行第一列</returns> public static object ExecuteScaler(string sql,params SqlParameter[] parameters) { using (SqlConnection sqlCon = new SqlConnection(conStr)) { sqlCon.Open(); using (SqlCommand sqlCmd = sqlCon.CreateCommand()) { sqlCmd.CommandText = sql; sqlCmd.Parameters.AddRange(parameters); return sqlCmd.ExecuteScalar(); } } } /// <summary> /// 执行离线数据集查询 /// </summary> /// <param name="sql">要执行的sql语句</param> /// <returns>返回离线数据集</returns> public static DataTable ExecuteDataTable(string sql,params SqlParameter[] parameters) { using (SqlConnection sqlCon = new SqlConnection(conStr)) { sqlCon.Open(); using (SqlCommand sqlCmd = sqlCon.CreateCommand()) { sqlCmd.CommandText = sql; sqlCmd.Parameters.AddRange(parameters); SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd); DataSet dataSet = new DataSet(); sqlAdapter.Fill(dataSet); return dataSet.Tables[0]; } } } /// <summary> /// 执行存储过程 /// </summary> /// <param name="storedProc"></param> /// <param name="parameters"></param> /// <returns></returns> public static int ExecuteStoredProc(string storedProc,params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(conStr)) { using (SqlCommand sqlCmd = new SqlCommand(storedProc, conn)) { //sql命令为存储过程 sqlCmd.CommandType = CommandType.StoredProcedure; //添加参数 sqlCmd.Parameters.AddRange(parameters); //晚打开,早关闭 conn.Open(); return sqlCmd.ExecuteNonQuery(); } } } }