测试发现前面发的那个功能太简单，不能调用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 /// <summary> 10 /// MSSQL数据库操作类 11 /// </summary> 12 public static class SqlHelper 13 { 14 /// <summary> 15 /// 执行MSSQL插入表操作,默认列名：ColumnName=ParameterName.Replace("@","") 16 /// </summary> 17 /// <param name="TableName">表名称</param> 18 /// <param name="parameters">SqlParameter</param> 19 /// <param name="sqlconn">一个SQL连接</param> 20 /// <returns>ExecuteNonQuery执行结果</returns> 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 " + ex.Message); 42 } 43 } 44 45 46 /// <summary> 47 /// 执行MSSQL插入表操作,默认列名：ColumnName=ParameterName.Replace("@","") 48 /// </summary> 49 /// <param name="TableName">表名称</param> 50 /// <param name="parameters">SqlParameter</param> 51 /// <param name="ColumnValues">ColumnValue键值对：弥补SqlParameter无法调用getdate()、year()等内部函数的不足。 52 /// 前后分别为Column和Value，添加在insert语句的column和value部分</param> 53 /// <param name="sqlconn">一个SQL连接</param> 54 /// <returns>ExecuteNonQuery执行结果</returns> 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 " + ex.Message); 80 } 81 } 82 83 /// <summary> 84 /// 执行MSSQL更新表操作,默认列名：ColumnName=ParameterName.Replace("@","") 85 /// </summary> 86 /// <param name="TableName">表名称</param> 87 /// <param name="parameters">SqlParameter</param> 88 /// <param name="sqlconn">一个SQL连接</param> 89 /// <param name="Conditions">查询条件，不需要where,多条件用and分隔,没有条件传入空字串</param> 90 /// <returns>ExecuteNonQuery执行结果</returns> 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 try 101 { 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 " + ex.Message); 111 } 112 } 113 114 /// <summary> 115 /// 执行MSSQL更新表操作,默认列名：ColumnName=ParameterName.Replace("@","") 116 /// </summary> 117 /// <param name="TableName">表名称</param> 118 /// <param name="parameters">SqlParameter</param> 119 /// <param name="ColumnValues">ColumnValue键值对：弥补SqlParameter无法调用getdate()、year()等内部函数的不足。 120 /// 前后分别为Column和Value，添加在insert语句的column和value部分</param> 121 /// <param name="sqlconn">一个SQL连接</param> 122 /// <param name="Conditions">查询条件，不需要where,多条件用and分隔,没有条件传入空字串</param> 123 /// <returns>ExecuteNonQuery执行结果</returns> 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 try 136 { 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 " + ex.Message); 146 } 147 } 148 149 /// <summary> 150 /// 执行MSSQL删除表内数据操作 151 /// </summary> 152 /// <param name="TableName">表名称</param> 153 /// <param name="sqlconn">一个SQL连接</param> 154 /// <param name="Conditions">查询条件，不需要where,多条件用and分隔,没有条件传入空字串</param> 155 /// <returns>ExecuteNonQuery执行结果</returns> 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 try 161 { 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 " + ex.Message); 170 } 171 } 172 173 /// <summary> 174 /// Select查表 175 /// </summary> 176 /// <param name="SqlString">一条完整、直接执行的select语句</param> 177 /// <param name="sqlconn">一个SQL连接</param> 178 /// <returns>DataTable</returns> 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 try 186 { 187 da.Fill(dt); 188 return dt; 189 } 190 catch (Exception ex) 191 { 192 throw new Exception("SelectTable: " + ex.Message); 193 } 194 } 195 } 196 } 197 198 /// <summary> 199 /// Select查值 200 /// </summary> 201 /// <param name="SqlString">一条完整、直接执行的select语句</param> 202 /// <param name="sqlconn">一个SQL连接</param> 203 /// <returns>ExecuteScalar</returns> 204 public static object SelectValue(string SqlString, SqlConnection sqlconn) 205 { 206 try 207 { 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: " + ex.Message); 216 } 217 } 218 219 /// <summary> 220 /// 执行存储过程，无返回值 221 /// </summary> 222 /// <param name="sqlString">存储过程执行语句，需包含Exec </param> 223 /// <param name="parameters">SqlParameter</param> 224 /// <param name="sqlconn">一个SQL连接</param> 225 /// <returns>ExecuteNonQuery执行结果</returns> 226 public static void ExecProcNonReturn(string sqlString, SqlParameter[] parameters, SqlConnection sqlconn) 227 { 228 try 229 { 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 " + ex.Message); 239 } 240 } 241 242 /// <summary> 243 /// 执行存储过程，并直接返回执行的结果 244 /// </summary> 245 /// <param name="sqlString">存储过程执行语句，需包含Exec </param> 246 /// <param name="parameters">SqlParameter</param> 247 /// <param name="sqlconn">一个SQL连接</param> 248 /// <returns>ExecuteNonQuery执行结果</returns> 249 public static object ExecProc(string sqlString, SqlParameter[] parameters, SqlConnection sqlconn) 250 { 251 try 252 { 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 " + ex.Message); 262 } 263 } 264 265 /// <summary> 266 /// 执行存储过程,带一个返回参数并返回此参数的执行结果 267 /// </summary> 268 /// <param name="sqlString">存储过程执行语句，需包含Exec </param> 269 /// <param name="parameters">SqlParameter</param> 270 /// <param name="parameter_out">SqlParameter.Direction = ParameterDirection.Output;</param> 271 /// <param name="sqlconn">一个SQL连接</param> 272 /// <returns>ExecuteNonQuery执行结果</returns> 273 public static object ExecProc(string sqlString, SqlParameter[] parameters, 274 SqlParameter parameter_out, SqlConnection sqlconn) 275 { 276 try 277 { 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 " + ex.Message); 289 } 290 } 291 } 292 } 293 }