当前位置：首页 > news >正文

Ado.NET SQLHelper

news 来源：原创 2024/5/10 3:16:55

  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\n" + ex.Message);
 42                 }
 43             }
 44 
 45             /// <summary>
 46             /// 执行MSSQL更新表操作,默认列名：ColumnName=ParameterName.Replace("@","")
 47             /// </summary>
 48             /// <param name="TableName">表名称</param>
 49             /// <param name="parameters">SqlParameter</param>
 50             /// <param name="sqlconn">一个SQL连接</param>
 51             /// <param name="Conditions">查询条件，不需要where,多条件用and分隔,没有条件传入空字串</param>
 52             /// <returns>ExecuteNonQuery执行结果</returns>
 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             /// <summary>
 77             /// 执行MSSQL表删除操作
 78             /// </summary>
 79             /// <param name="TableName">表名称</param>
 80             /// <param name="sqlconn">一个SQL连接</param>
 81             /// <param name="Conditions">查询条件，不需要where,多条件用and分隔,没有条件传入空字串</param>
 82             /// <returns>ExecuteNonQuery执行结果</returns>
 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             /// <summary>
101             /// Select查表
102             /// </summary>
103             /// <param name="SqlString">一条完整、直接执行的select语句</param>
104             /// <param name="sqlconn">一个SQL连接</param>
105             /// <returns>DataTable</returns>
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                         try
113                         {
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             /// <summary>
126             /// Select查值
127             /// </summary>
128             /// <param name="SqlString">一条完整、直接执行的select语句</param>
129             /// <param name="sqlconn">一个SQL连接</param>
130             /// <returns>ExecuteScalar</returns>
131             public static object SelectValue(string SqlString, SqlConnection sqlconn)
132             {
133                 try
134                 {
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             /// <summary>
147             /// 执行存储过程，无返回值
148             /// </summary>
149             /// <param name="sqlString">存储过程执行语句，需包含Exec </param>
150             /// <param name="parameters">SqlParameter</param>
151             /// <param name="sqlconn">一个SQL连接</param>
152             /// <returns>ExecuteNonQuery执行结果</returns>
153             public static void ExecProcNonReturn(string sqlString, SqlParameter[] parameters, SqlConnection sqlconn)
154             {
155                 try
156                 {
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             /// <summary>
170             /// 执行存储过程，并直接返回执行的结果
171             /// </summary>
172             /// <param name="sqlString">存储过程执行语句，需包含Exec </param>
173             /// <param name="parameters">SqlParameter</param>
174             /// <param name="sqlconn">一个SQL连接</param>
175             /// <returns>ExecuteNonQuery执行结果</returns>
176             public static object ExecProc(string sqlString, SqlParameter[] parameters, SqlConnection sqlconn)
177             {
178                 try
179                 {
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             /// <summary>
193             /// 执行存储过程,带一个返回参数并返回此参数的执行结果
194             /// </summary>
195             /// <param name="sqlString">存储过程执行语句，需包含Exec </param>
196             /// <param name="parameters">SqlParameter</param>
197             /// <param name="parameter_out">SqlParameter.Direction = ParameterDirection.Output;</param>
198             /// <param name="sqlconn">一个SQL连接</param>
199             /// <returns>ExecuteNonQuery执行结果</returns>
200             public static object ExecProc(string sqlString, SqlParameter[] parameters, 
201                 SqlParameter parameter_out, SqlConnection sqlconn)
202             {
203                 try
204                 {                    
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 }