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 }
