测试发现前面发的那个功能太简单，不能调用getdate()等内部函数。
完善后重载了insert和update两个功能，将函数作为字符串传入SQL语句构造，需要的可以试用一下
1 using System; 2 using System.Data; 3 using System.Data.SqlClient; 4 5 namespace RaywindStudio 6 { 7 namespace DAL 8 { 9 /// 10 /// MSSQL数据库操作类 11 /// 12 public static class SqlHelper 13 { 14 /// 15 /// 执行MSSQL插入表操作,默认列名：ColumnName=ParameterName.Replace("@","") 16 /// 17 /// 表名称 18 /// SqlParameter 19 /// 一个SQL连接 20 /// ExecuteNonQuery执行结果 21 public static int InsertCMD(string TableName, SqlParameter[] parameters, 22 SqlConnection sqlconn) 23 { 24 string sql = "Insert into " + TableName + "("; 25 for (int i = 0; i < parameters.Length; i++) 26 sql += parameters[i].ParameterName.Replace("@", "") + ","; 27 sql = sql.Substring(0, sql.Length - 1) + ") Values("; 28 for (int j = 0; j < parameters.Length; j++) 29 sql += parameters[j].ParameterName + ","; 30 sql = sql.Substring(0, sql.Length - 1) + ")"; 31 try 32 { 33 SqlCommand cmd = new SqlCommand(sql, sqlconn); 34 cmd.Parameters.AddRange(parameters); 35 if (sqlconn.State != ConnectionState.Open) 36 sqlconn.Open(); 37 return cmd.ExecuteNonQuery(); 38 } 39 catch (Exception ex) 40 { 41 throw new Exception("InsertCMD:ExecuteNonQuery\n" + ex.Message); 42 } 43 } 44 45 46 /// 47 /// 执行MSSQL插入表操作,默认列名：ColumnName=ParameterName.Replace("@","") 48 /// 49 /// 表名称 50 /// SqlParameter 51 /// ColumnValue键值对：弥补SqlParameter无法调用getdate()、year()等内部函数的不足。 52 /// 前后分别为Column和Value，添加在insert语句的column和value部分 53 /// 一个SQL连接 54 /// ExecuteNonQuery执行结果 55 public static int InsertCMD(string TableName, SqlParameter[] parameters, 56 string[,] ColumnValues, SqlConnection sqlconn) 57 { 58 string sql = "Insert into " + TableName + "("; 59 for (int i = 0; i < parameters.Length; i++) 60 sql += parameters[i].ParameterName.Replace("@", "") + ","; 61 for (int ii = 0; ii < ColumnValues.GetLength(0); ii++) 62 sql += ColumnValues[ii, 0] + ","; 63 sql = sql.Substring(0, sql.Length - 1) + ") Values("; 64 for (int j = 0; j < parameters.Length; j++) 65 sql += parameters[j].ParameterName + ","; 66 for (int jj = 0; jj < ColumnValues.GetLength(0); jj++) 67 sql += ColumnValues[jj, 1] + ","; 68 sql = sql.Substring(0, sql.Length - 1) + ")"; 69 try 70 { 71 SqlCommand cmd = new SqlCommand(sql, sqlconn); 72 cmd.Parameters.AddRange(parameters); 73 if (sqlconn.State != ConnectionState.Open) 74 sqlconn.Open(); 75 return cmd.ExecuteNonQuery(); 76 } 77 catch (Exception ex) 78 { 79 throw new Exception("InsertCMD:ExecuteNonQuery\n" + ex.Message); 80 } 81 } 82 83 /// 84 /// 执行MSSQL更新表操作,默认列名：ColumnName=ParameterName.Replace("@","") 85 /// 86 /// 表名称 87 /// SqlParameter 88 /// 一个SQL连接 89 /// 查询条件，不需要where,多条件用and分隔,没有条件传入空字串 90 /// ExecuteNonQuery执行结果 91 public static int UpdateCMD(string TableName, SqlParameter[] parameters, 92 SqlConnection sqlconn, string Conditions) 93 { 94 string sql = "Update " + TableName + " Set "; 95 for (int i = 0; i < parameters.Length; i++) 96 sql += parameters[i].ParameterName.Replace("@", "") 97 + "=" + parameters[i].ParameterName + ","; 98 sql = sql.Substring(0, sql.Length - 1) 99 + " Where 1=1 " + (Conditions.Length > 0 ? " and " + Conditions : "");100 try101 {102 SqlCommand cmd = new SqlCommand(sql, sqlconn);103 cmd.Parameters.AddRange(parameters);104 if (sqlconn.State != ConnectionState.Open)105 sqlconn.Open();106 return cmd.ExecuteNonQuery();107 }108 catch (Exception ex)109 {110 throw new Exception("UpdateCMD:ExecuteNonQuery\n" + ex.Message);111 }112 }113 114 /// 115 /// 执行MSSQL更新表操作,默认列名：ColumnName=ParameterName.Replace("@","")116 /// 117 /// 表名称118 /// SqlParameter119 /// ColumnValue键值对：弥补SqlParameter无法调用getdate()、year()等内部函数的不足。120 /// 前后分别为Column和Value，添加在insert语句的column和value部分121 /// 一个SQL连接122 /// 查询条件，不需要where,多条件用and分隔,没有条件传入空字串123 /// ExecuteNonQuery执行结果 124 public static int UpdateCMD(string TableName, SqlParameter[] parameters,125 string[,] ColumnValues, SqlConnection sqlconn, string Conditions)126 {127 string sql = "Update " + TableName + " Set ";128 for (int i = 0; i < parameters.Length; i++)129 sql += parameters[i].ParameterName.Replace("@", "")130 + "=" + parameters[i].ParameterName + ",";131 for (int j = 0; j < ColumnValues.GetLength(0); j++)132 sql += ColumnValues[j, 0] + "=" + ColumnValues[j, 1] + ",";133 sql = sql.Substring(0, sql.Length - 1)134 + " Where 1=1 " + (Conditions.Length > 0 ? " and " + Conditions : "");135 try136 {137 SqlCommand cmd = new SqlCommand(sql, sqlconn);138 cmd.Parameters.AddRange(parameters);139 if (sqlconn.State != ConnectionState.Open)140 sqlconn.Open();141 return cmd.ExecuteNonQuery();142 }143 catch (Exception ex)144 {145 throw new Exception("UpdateCMD:ExecuteNonQuery\n" + ex.Message);146 }147 }148 149 /// 150 /// 执行MSSQL删除表内数据操作151 /// 152 /// 表名称153 /// 一个SQL连接154 /// 查询条件，不需要where,多条件用and分隔,没有条件传入空字串155 /// ExecuteNonQuery执行结果 156 public static int DeleteCMD(string TableName, SqlConnection sqlconn, string Conditions)157 {158 string sql = "Delete From " + TableName + " Where 1=1 "159 + (Conditions.Length > 0 ? " and " + Conditions : "");160 try161 {162 SqlCommand cmd = new SqlCommand(sql, sqlconn);163 if (sqlconn.State != ConnectionState.Open)164 sqlconn.Open();165 return cmd.ExecuteNonQuery();166 }167 catch (Exception ex)168 {169 throw new Exception("DeleteCMD:ExecuteNonQuery\n" + ex.Message);170 }171 }172 173 /// 174 /// Select查表175 /// 176 /// 一条完整、直接执行的select语句177 /// 一个SQL连接178 /// DataTable 179 public static DataTable SelectTable(string SqlString, SqlConnection sqlconn)180 {181 using (DataTable dt = new DataTable())182 {183 using (SqlDataAdapter da = new SqlDataAdapter(SqlString, sqlconn))184 {185 try186 {187 da.Fill(dt);188 return dt;189 }190 catch (Exception ex)191 {192 throw new Exception("SelectTable:\n" + ex.Message);193 }194 }195 }196 }197 198 /// 199 /// Select查值200 /// 201 /// 一条完整、直接执行的select语句202 /// 一个SQL连接203 /// ExecuteScalar 204 public static object SelectValue(string SqlString, SqlConnection sqlconn)205 {206 try207 {208 SqlCommand cmd = new SqlCommand(SqlString, sqlconn);209 if (sqlconn.State != ConnectionState.Open)210 sqlconn.Open();211 return cmd.ExecuteScalar();212 }213 catch (Exception ex)214 {215 throw new Exception("SelectValue:\n" + ex.Message);216 }217 }218 219 /// 220 /// 执行存储过程，无返回值221 /// 222 /// 存储过程执行语句，需包含Exec 223 /// SqlParameter224 /// 一个SQL连接225 /// ExecuteNonQuery执行结果 226 public static void ExecProcNonReturn(string sqlString, SqlParameter[] parameters, SqlConnection sqlconn)227 {228 try229 {230 SqlCommand cmd = new SqlCommand(sqlString, sqlconn);231 cmd.Parameters.AddRange(parameters);232 if (sqlconn.State != ConnectionState.Open)233 sqlconn.Open();234 cmd.ExecuteNonQuery();235 }236 catch (Exception ex)237 {238 throw new Exception("ExecProcNonReturn:ExecuteNonQuery\n" + ex.Message);239 }240 }241 242 /// 243 /// 执行存储过程，并直接返回执行的结果244 /// 245 /// 存储过程执行语句，需包含Exec 246 /// SqlParameter247 /// 一个SQL连接248 /// ExecuteNonQuery执行结果 249 public static object ExecProc(string sqlString, SqlParameter[] parameters, SqlConnection sqlconn)250 {251 try252 {253 SqlCommand cmd = new SqlCommand(sqlString, sqlconn);254 cmd.Parameters.AddRange(parameters);255 if (sqlconn.State != ConnectionState.Open)256 sqlconn.Open();257 return cmd.ExecuteScalar();258 }259 catch (Exception ex)260 {261 throw new Exception("ExecProc:ExecuteScalar\n" + ex.Message);262 }263 }264 265 /// 266 /// 执行存储过程,带一个返回参数并返回此参数的执行结果267 /// 268 /// 存储过程执行语句，需包含Exec 269 /// SqlParameter270 /// SqlParameter.Direction = ParameterDirection.Output;271 /// 一个SQL连接272 /// ExecuteNonQuery执行结果 273 public static object ExecProc(string sqlString, SqlParameter[] parameters,274 SqlParameter parameter_out, SqlConnection sqlconn)275 {276 try277 {278 SqlCommand cmd = new SqlCommand(sqlString, sqlconn);279 cmd.Parameters.AddRange(parameters);280 cmd.Parameters.Add(parameter_out);281 if (sqlconn.State != ConnectionState.Open)282 sqlconn.Open();283 cmd.ExecuteNonQuery();284 return parameter_out.Value;285 }286 catch (Exception ex)287 {288 throw new Exception("ExecProc:ExecuteNonQuery\n" + ex.Message);289 }290 }291 }292 }293 }