1 /// <summary>
2 /// ado.net方式执行sql语句
3 /// </summary>
4 public class SqlHelper
5 {
6 /// <summary>
7 /// 执行sql语句，进行查询操作（推荐）
8 /// </summary>
9 /// <param name="sqlStr">sql语句</param>
10 /// <param name="parm">参集合数</param>
11 /// <returns>dataset结果集</returns>
12 public static DataSet ExcuteSqlDataSet(string sqlStr, string conStr, params SqlParameter[] param)
13 {
14 try
15 {
16 conStr = string.IsNullOrWhiteSpace(conStr) ? Connection.SqlConnetionStr() : conStr;
17 using (SqlConnection conn = new SqlConnection(conStr))
18 {
19 conn.Open();
20 using (SqlCommand cmd = conn.CreateCommand())
21 {
22 cmd.CommandText = sqlStr;
23 if (param != null)
24 {
25 foreach (var item in param)
26 {
27 if (!string.IsNullOrWhiteSpace(item.Value.ToString_()))
28 {
29 cmd.Parameters.Add(item);
30 }
31 else
32 {
33 item.Value = DBNull.Value;
34 cmd.Parameters.Add(item);
35 }
36 }
37 }
38 //cmd.Parameters.AddRange(parm);
39 cmd.CommandTimeout = 180;
40 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
41 DataSet dataset = new DataSet();
42 //cmd.Prepare();
43 adapter.Fill(dataset);
44 cmd.Parameters.Clear();
45 return dataset;
46 }
47 }
48 }
49 catch (Exception ex)
50 {
51 throw ex;
52 }
53 }
54
55 /// <summary>
56 /// 执行sql语句，进行查询操作（推荐）
57 /// </summary>
58 /// <param name="sqlStr">sql语句</param>
59 /// <param name="parm">参集合数</param>
60 /// <returns>dataset结果集</returns>
61 public static DataSet ExcuteSqlDataSet2(string sqlStr, string conStr, List<SqlParameter> param)
62 {
63 try
64 {
65 conStr = string.IsNullOrWhiteSpace(conStr) ? Connection.SqlConnetionStr() : conStr;
66 using (SqlConnection conn = new SqlConnection(conStr))
67 {
68 conn.Open();
69 using (SqlCommand cmd = conn.CreateCommand())
70 {
71 cmd.CommandText = sqlStr;
72 if (param != null)
73 {
74 foreach (var item in param)
75 {
76 if (!string.IsNullOrWhiteSpace(item.Value.ToString_()))
77 {
78 cmd.Parameters.Add(item);
79 }
80 else
81 {
82 item.Value = DBNull.Value;
83 cmd.Parameters.Add(item);
84 }
85 }
86 }
87 //cmd.Parameters.AddRange(parm);
88 cmd.CommandTimeout = 180;
89 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
90 DataSet dataset = new DataSet();
91 //cmd.Prepare();
92 adapter.Fill(dataset);
93 cmd.Parameters.Clear();
94 return dataset;
95 }
96 }
97 }
98 catch (Exception ex)
99 {
100 throw ex;
101 }
102 }
103
104 /// <summary>
105 /// 执行sql语句，执行增删改操作
106 /// </summary>
107 /// <param name="sql">sql语句</param>
108 /// <param name="param">参数集合</param>
109 /// <returns>受影响条数</returns>
110 public static int ExecuteNonQuery(string sql, string conStr, params SqlParameter[] param)
111 {
112 try
113 {
114 conStr = string.IsNullOrWhiteSpace(conStr) ? Connection.SqlConnetionStr() : conStr;
115 int n = -1;
116 using (SqlConnection con = new SqlConnection(conStr))
117 {
118 using (SqlCommand cmd = new SqlCommand(sql, con))
119 {
120 if (param != null)
121 {
122 foreach (var item in param)
123 {
124 if (!string.IsNullOrWhiteSpace(item.Value.ToString_()))
125 {
126 cmd.Parameters.Add(item);
127 }
128 else
129 {
130 item.Value = DBNull.Value;
131 cmd.Parameters.Add(item);
132 }
133 }
134 }
135 //cmd.Parameters.AddRange(param);
136 con.Open();
137 n = cmd.ExecuteNonQuery();
138 }
139 }
140 return n;
141 }
142 catch (Exception ex)
143 {
144 throw ex;
145 }
146 }
147
148 /// <summary>
149 /// 执行sql语句，执行增删改操作
150 /// </summary>
151 /// <param name="sql">sql语句</param>
152 /// <param name="param">参数集合</param>
153 /// <returns>受影响条数</returns>
154 public static int ExecuteNonQuery2(string sql, string conStr, List<SqlParameter> param)
155 {
156 try
157 {
158 conStr = string.IsNullOrWhiteSpace(conStr) ? Connection.SqlConnetionStr() : conStr;
159 int n = -1;
160 using (SqlConnection con = new SqlConnection(conStr))
161 {
162 using (SqlCommand cmd = new SqlCommand(sql, con))
163 {
164 if (param != null)
165 {
166 foreach (var item in param)
167 {
168 if (!string.IsNullOrWhiteSpace(item.Value.ToString_()))
169 {
170 cmd.Parameters.Add(item);
171 }
172 else
173 {
174 item.Value = DBNull.Value;
175 cmd.Parameters.Add(item);
176 }
177 }
178 }
179 //cmd.Parameters.AddRange(param);
180 con.Open();
181 n = cmd.ExecuteNonQuery();
182 }
183 }
184 return n;
185 }
186 catch (Exception ex)
187 {
188 throw ex;
189 }
190 }
191
192 /// <summary>
193 /// SqlBulkCopy批量插入数据
194 /// </summary>
195 /// <param name="connectionStr">链接字符串</param>
196 /// <param name="dataTableName">表名</param>
197 /// <param name="sourceDataTable">数据源</param>
198 /// <param name="batchSize">一次事务插入的行数</param>
199 public static void BulkCopy(string connectionStr, string dataTableName, DataTable sourceDataTable, int batchSize = 100000)
200 {
201 connectionStr = string.IsNullOrWhiteSpace(connectionStr) ? Connection.SqlConnetionStr() : connectionStr;
202
203 using (SqlConnection connection = new SqlConnection(connectionStr))
204 {
205 using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionStr, SqlBulkCopyOptions.UseInternalTransaction))
206 {
207 try
208 {
209 sqlBulkCopy.DestinationTableName = dataTableName;
210 sqlBulkCopy.BatchSize = batchSize;
211 for (int i = 0; i < sourceDataTable.Columns.Count; i++)
212 {
213 sqlBulkCopy.ColumnMappings.Add(sourceDataTable.Columns[i].ColumnName, sourceDataTable.Columns[i].ColumnName);
214 }
215 sqlBulkCopy.WriteToServer(sourceDataTable);
216 }
217 catch (Exception ex)
218 {
219 throw ex;
220 }
221 }
222 }
223 }
224
225 /// <summary>
226 /// 执行存储过程，返回DataSet
227 /// </summary>
228 /// <param name="storedProcedure"></param>
229 /// <param name="conStr"></param>
230 /// <param name="param"></param>
231 /// <returns></returns>
232 public static DataSet ExcuteStoredProcedure(string storedProcedure, string conStr, List<SqlParameter> param)
233 {
234 try
235 {
236 conStr = string.IsNullOrWhiteSpace(conStr) ? Connection.SqlConnetionStr() : conStr;
237
238 using (SqlConnection conn = new SqlConnection(conStr))
239 {
240 SqlCommand cmd = conn.CreateCommand();
241 cmd.CommandType = CommandType.StoredProcedure; //指定执行存储过程操作
242 cmd.CommandText = storedProcedure; //存储过程名称
243 if (param != null)
244 {
245 foreach (var item in param)
246 {
247 if (!string.IsNullOrWhiteSpace(item.Value.ToString_()))
248 {
249 cmd.Parameters.Add(item);
250 }
251 else
252 {
253 item.Value = DBNull.Value;
254 cmd.Parameters.Add(item);
255 }
256 }
257 }
258 cmd.CommandTimeout = 180;
259 //方式一，查询回来的结果需要显示在DataGrid之类的控件上
260 DataSet ds = new DataSet();
261 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
262 adapter.Fill(ds);
263 return ds;
264 }
265 }
266 catch (Exception ex)
267 {
268 throw ex;
269 }
270 }
271 }
