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 /// 执行MSSQL更新表操作,默认列名：ColumnName=ParameterName.Replace("@","") 47 /// 48 /// 表名称 49 /// SqlParameter 50 /// 一个SQL连接 51 /// 查询条件，不需要where,多条件用and分隔,没有条件传入空字串 52 /// ExecuteNonQuery执行结果 53 public static int UpdateCMD(string TableName, SqlParameter[] parameters, 54 SqlConnection sqlconn, string Conditions) 55 { 56 string sql = "Update " + TableName + " Set "; 57 for (int i = 0; i < parameters.Length; i++) 58 sql += parameters[i].ParameterName.Replace("@", "") 59 + "=" + parameters[i].ParameterName + ","; 60 sql = sql.Substring(0, sql.Length - 1) 61 + " Where 1=1 " + (Conditions.Length > 0 ? " and " + Conditions : ""); 62 try 63 { 64 SqlCommand cmd = new SqlCommand(sql, sqlconn); 65 cmd.Parameters.AddRange(parameters); 66 if (sqlconn.State != ConnectionState.Open) 67 sqlconn.Open(); 68 return cmd.ExecuteNonQuery(); 69 } 70 catch (Exception ex) 71 { 72 throw new Exception("UpdateCMD:ExecuteNonQuery\n" + ex.Message); 73 } 74 } 75 76 /// 77 /// 执行MSSQL表删除操作 78 /// 79 /// 表名称 80 /// 一个SQL连接 81 /// 查询条件，不需要where,多条件用and分隔,没有条件传入空字串 82 /// ExecuteNonQuery执行结果 83 public static int DeleteCMD(string TableName, SqlConnection sqlconn, string Conditions) 84 { 85 string sql = "Delete From " + TableName + " Where 1=1 " 86 + (Conditions.Length > 0 ? " and " + Conditions : ""); 87 try 88 { 89 SqlCommand cmd = new SqlCommand(sql, sqlconn); 90 if (sqlconn.State != ConnectionState.Open) 91 sqlconn.Open(); 92 return cmd.ExecuteNonQuery(); 93 } 94 catch (Exception ex) 95 { 96 throw new Exception("DeleteCMD:ExecuteNonQuery\n" + ex.Message); 97 } 98 } 99 100 /// 101 /// Select查表102 /// 103 /// 一条完整、直接执行的select语句104 /// 一个SQL连接105 /// DataTable 106 public static DataTable SelectTable(string SqlString,SqlConnection sqlconn)107 {108 using (DataTable dt = new DataTable())109 {110 using (SqlDataAdapter da = new SqlDataAdapter(SqlString, sqlconn))111 {112 try113 {114 da.Fill(dt);115 return dt;116 }117 catch (Exception ex)118 {119 throw new Exception("SelectTable:\n" + ex.Message);120 }121 }122 }123 }124 125 /// 126 /// Select查值127 /// 128 /// 一条完整、直接执行的select语句129 /// 一个SQL连接130 /// ExecuteScalar 131 public static object SelectValue(string SqlString, SqlConnection sqlconn)132 {133 try134 {135 SqlCommand cmd = new SqlCommand(SqlString, sqlconn);136 if (sqlconn.State != ConnectionState.Open)137 sqlconn.Open();138 return cmd.ExecuteScalar();139 }140 catch (Exception ex)141 {142 throw new Exception("SelectValue:\n" + ex.Message);143 }144 }145 146 /// 147 /// 执行存储过程，无返回值148 /// 149 /// 存储过程执行语句，需包含Exec 150 /// SqlParameter151 /// 一个SQL连接152 /// ExecuteNonQuery执行结果 153 public static void ExecProcNonReturn(string sqlString, SqlParameter[] parameters, SqlConnection sqlconn)154 {155 try156 {157 SqlCommand cmd = new SqlCommand(sqlString, sqlconn);158 cmd.Parameters.AddRange(parameters);159 if (sqlconn.State != ConnectionState.Open)160 sqlconn.Open();161 cmd.ExecuteNonQuery();162 }163 catch (Exception ex)164 {165 throw new Exception("ExecProcNonReturn:ExecuteNonQuery\n" + ex.Message);166 }167 }168 169 /// 170 /// 执行存储过程，并直接返回执行的结果171 /// 172 /// 存储过程执行语句，需包含Exec 173 /// SqlParameter174 /// 一个SQL连接175 /// ExecuteNonQuery执行结果 176 public static object ExecProc(string sqlString, SqlParameter[] parameters, SqlConnection sqlconn)177 {178 try179 {180 SqlCommand cmd = new SqlCommand(sqlString, sqlconn);181 cmd.Parameters.AddRange(parameters);182 if (sqlconn.State != ConnectionState.Open)183 sqlconn.Open();184 return cmd.ExecuteScalar();185 }186 catch (Exception ex)187 {188 throw new Exception("ExecProc:ExecuteScalar\n" + ex.Message);189 }190 }191 192 /// 193 /// 执行存储过程,带一个返回参数并返回此参数的执行结果194 /// 195 /// 存储过程执行语句，需包含Exec 196 /// SqlParameter197 /// SqlParameter.Direction = ParameterDirection.Output;198 /// 一个SQL连接199 /// ExecuteNonQuery执行结果 200 public static object ExecProc(string sqlString, SqlParameter[] parameters, 201 SqlParameter parameter_out, SqlConnection sqlconn)202 {203 try204 { 205 SqlCommand cmd = new SqlCommand(sqlString, sqlconn);206 cmd.Parameters.AddRange(parameters);207 cmd.Parameters.Add(parameter_out);208 if (sqlconn.State != ConnectionState.Open)209 sqlconn.Open();210 cmd.ExecuteNonQuery();211 return parameter_out.Value;212 }213 catch (Exception ex)214 {215 throw new Exception("ExecProc:ExecuteNonQuery\n" + ex.Message);216 }217 }218 }219 }220 }