数据库接连命令执行(一)

1 using System;
2 using System.Data;
3 using System.Data.SqlClient;
4
5 using System.Collections;
6
7 namespace DAL
8 {
9 /// <summary>
10 /// DataBase 的摘要说明。
11 /// </summary>
12 public class DataBase
13 {
14 private SqlConnection conn;
15 private string _connString = null ;
16 public string ConnectionString
17 {
18 get
19 {
20 return _connString;
21 }
22 set
23 {
24 _connString = value;
25 }
26 }
27 public DataBase()
28 {
29 //
30 // TODO: 在此处添加构造函数逻辑
31 //
32 _connString = System.Configuration.ConfigurationSettings.AppSettings[ " OA_conn " ];
33 }
34
35 #region 数据库连接打开/关闭
36 public void Open() // 打开数据库
37 {
38 if (conn == null )
39 {
40 conn = new SqlConnection(_connString);
41 }
42 if (conn.State == System.Data.ConnectionState.Closed)
43 {
44 conn.Open();
45 }
46 }
47 public void Close() // 关闭数据库
48 {
49 if (conn != null )
50 {
51 conn.Close();
52 }
53 Dispose();
54 }
55 public void Dispose() // 释放资源
56 {
57 if (conn != null )
58 {
59 conn.Dispose();
60 conn = null ;
61 }
62 }
63 #endregion
64
65 #region 执行T-SQL
66 public int ExecuteSqlint( string comdText)
67 {
68 try
69 {
70 Open();
71 int result = 0 ;
72 SqlCommand cmd = CreateSqlCommand(comdText, null );
73 object obj = cmd.ExecuteScalar();
74 if (obj != null && obj.ToString() != "" )
75 result = int .Parse(obj.ToString());
76 cmd.Parameters.Clear();
77 return result;
78 }
79 catch (System.Data.SqlClient.SqlException e)
80 {
81 throw new Exception(e.Message);
82 }
83 finally
84 {
85 Close();
86 }
87 }
88 public int ExecuteSqlint( string comdText,SqlParameter[] prams)
89 {
90 try
91 {
92 int result = 0 ;
93 Open();
94 SqlCommand cmd = CreateSqlCommand(comdText,prams);
95 object obj = cmd.ExecuteScalar();
96 if (obj != null && obj.ToString() != "" )
97 result = int .Parse(obj.ToString());
98 cmd.Parameters.Clear();
99 return result;
100 }
101 catch (System.Data.SqlClient.SqlException e)
102 {
103 throw new Exception(e.Message);
104 }
105 finally
106 {
107 Close();
108 }
109 }
110
111 public SqlDataReader ExecuteSqlDr( string cmdText)
112 {
113 try
114 {
115 Open();
116 SqlCommand cmd = CreateSqlCommand(cmdText, null );
117 SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
118 return dr;
119 }
120 catch (System.Data.SqlClient.SqlException e)
121 {
122 throw new Exception(e.Message);
123 }
124
125 }
126 public SqlDataReader ExecuteSqlDr( string cmdText,SqlParameter[] prams)
127 {
128 try
129 {
130 Open();
131 SqlCommand cmd = CreateSqlCommand(cmdText, prams);
132 SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
133 return dr;
134 }
135 catch (System.Data.SqlClient.SqlException e)
136 {
137 throw new Exception(e.Message);
138 }
139 }
140 public DataSet ExecuteSqlDs( string cmdText)
141 {
142 try
143 {
144 Open();
145 SqlDataAdapter da = new SqlDataAdapter(cmdText,conn);
146 DataSet ds = new DataSet();
147 da.Fill(ds);
148 return ds;
149
150 }
151 catch (System.Data.SqlClient.SqlException e)
152 {
153 throw new Exception(e.Message);
154 }
155 finally
156 {
157 Close();
158 }
159 }
160 public DataSet ExecuteSqlDs( string cmdText,SqlParameter[] prams)
161 {
162 try
163 {
164 Open();
165 SqlCommand cmd = CreateSqlCommand(cmdText,prams);
166 SqlDataAdapter da = new SqlDataAdapter(cmd);
167 // da.SelectCommand=cmd;
168 DataSet ds = new DataSet();
169 da.Fill(ds);
170 return ds;
171
172 }
173 catch (System.Data.SqlClient.SqlException e)
174 {
175 throw new Exception(e.Message);
176 }
177 finally
178 {
179 Close();
180 }
181 }
182 private SqlCommand CreateSqlCommand( string cmdText, SqlParameter[] prams)
183 {
184 // Open();
185 SqlCommand cmd = new SqlCommand(cmdText, conn);
186 cmd.CommandType = CommandType.Text;
187
188 // 依次把参数传入
189 if (prams != null )
190 {
191 foreach (SqlParameter parameter in prams)
192 cmd.Parameters.Add(parameter);
193 }
194 return cmd;
195 }
196 public bool RunSql( string cmdText, SqlParameter[] prams)
197 {
198 SqlTransaction tran = null ;
199 bool isTrue = false ;
200 int i = 0 ;
201 try
202 {
203 Open();
204 SqlCommand cmd = CreateSqlCommand(cmdText, prams);
205 tran = conn.BeginTransaction();
206 cmd.Transaction = tran;
207 i = cmd.ExecuteNonQuery();
208 cmd.Parameters.Clear();
209 if (tran != null )
210 {
211 tran.Commit();
212 }
213 if (i != 0 )
214 isTrue = true ;
215 return isTrue;
216 }
217 catch (System.Data.SqlClient.SqlException e)
218 {
219 if (tran != null )
220 {
221 tran.Rollback();
222 }
223 throw new Exception(e.Message);
224 }
225 finally
226 {
227 this .Close();
228 }
229
230 }
231
232 #region 执行SQL命令返回受影响的行数
233 /// <summary>
234 /// 执行SQL命令返回受影响的行数
235 /// </summary>
236 /// <param name="cmdText"></param>
237 /// <returns></returns>
238 public int RunSqlReInt( string cmdText)
239 {
240 try
241 {
242 int result = 0 ;
243 Open();
244 SqlCommand cmd = CreateSqlCommand(cmdText, null );
245 result = cmd.ExecuteNonQuery();
246 return result;
247 }
248 catch (System.Data.SqlClient.SqlException e)
249 {
250 throw new Exception(e.Message);
251 }
252 finally
253 {
254 Close();
255 }
256 }
257 #endregion

1 　　　　　 #region 执行多条带参数SQL语句,实现数据库事务
2 /// <summary>
3 /// 执行多条带参数SQL语句,实现数据库事务
4 /// </summary>
5 /// <param name="connectionString"> 连接字符串 </param>
6 /// <param name="SqlStringList"> SQL语句的哈希表(Value为该语句的SqlParameter[]) </param>
7 public bool ExecuteSqlTran( string cmdText,Hashtable SqlStringList)
8 {
9 Open();
10 int i = 0 ;
11 SqlTransaction tran = conn.BeginTransaction();
12 SqlCommand cmd = new SqlCommand();
13 try
14 {
15 foreach (DictionaryEntry de in SqlStringList)
16 {
17 SqlParameter[] cmdParms = (SqlParameter[])de.Value;
18 cmd = CreateSqlCommand(cmdText, cmdParms);
19 cmd.Transaction = tran;
20 i = cmd.ExecuteNonQuery();
21 cmd.Parameters.Clear();
22 }
23 tran.Commit();
24 return i > 0 ;
25 }
26 catch (System.Data.SqlClient.SqlException ex)
27 {
28 tran.Rollback();
29 return false ;
30 throw new Exception(ex.Message);
31 }
32
33 }
34 #endregion
35 #endregion
36
37 #region 执行存储过程
38 public SqlDataReader ExecuteProcDr( string procName,SqlParameter[] prams)
39 {
40 try
41 {
42 Open();
43 SqlCommand cmd = CreateProcCommand(procName,prams);
44 SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
45 return dr;
46 }
47 catch (System.Data.SqlClient.SqlException e)
48 {
49 throw new Exception(e.Message);
50 }
51 }
52
53 public DataSet ExecuteProcDs( string procName,SqlParameter[] prams)
54 {
55 try
56 {
57 Open();
58
59 SqlCommand cmd = CreateProcCommand(procName,prams);
60 cmd.CommandTimeout = 1200 ;
61 SqlDataAdapter da = new SqlDataAdapter(cmd);
62 // da.SelectCommand=cmd;
63 DataSet ds = new DataSet();
64 da.Fill(ds);
65 return ds;
66
67 }
68 catch (System.Data.SqlClient.SqlException e)
69 {
70 throw new Exception(e.Message);
71 }
72 finally
73 {
74 this .Close();
75 }
76
77 }
78 public string ExecuteProcStr( string procName, SqlParameter[] prams)
79 {
80 try
81 {
82 Open();
83 SqlCommand cmd = CreateProcCommand(procName, prams);
84 string result = cmd.ExecuteScalar().ToString();
85 return result;
86 }
87 catch (System.Data.SqlClient.SqlException e)
88 {
89 throw new Exception(e.Message);
90 }
91 finally
92 {
93 this .Close();
94 }
95 }
96 private SqlCommand CreateProcCommand( string procName, SqlParameter[] prams) // 执行存储过程
97 {
98 // Open();
99 SqlCommand cmd = new SqlCommand(procName, conn);
100 cmd.CommandType = CommandType.StoredProcedure;
101
102 // 依次把参数传入存储过程
103 if (prams != null )
104 {
105 foreach (SqlParameter parameter in prams)
106 cmd.Parameters.Add(parameter);
107 }
108
109 // 加入返回参数
110 cmd.Parameters.Add(
111 new SqlParameter( " @ReturnValue " , SqlDbType.Int, 4 ,
112 ParameterDirection.ReturnValue, false , 0 , 0 ,
113 string .Empty, DataRowVersion.Default, null ));
114
115 return cmd;
116 }
117 private SqlCommand CreateProcCommand( string procName, SqlParameter[] prams, string returnValue) // 执行存储过程
118 {
119 // Open();
120 SqlCommand cmd = new SqlCommand(procName, conn);
121 cmd.CommandType = CommandType.StoredProcedure;
122
123 // 依次把参数传入存储过程
124 if (prams != null )
125 {
126 foreach (SqlParameter parameter in prams)
127 cmd.Parameters.Add(parameter);
128 }
129 return cmd;
130 }
131 public bool RunProc( string procName, SqlParameter[] prams)
132 {
133 bool tf = false ;
134 SqlTransaction tran = null ;
135 try
136 {
137 Open();
138 SqlCommand cmd = CreateProcCommand(procName, prams);
139 tran = conn.BeginTransaction();
140 cmd.Transaction = tran;
141 if (cmd.ExecuteNonQuery() > 0 )
142 tf = true ;
143 if (tran != null )
144 {
145 tran.Commit();
146 }
147 cmd.Parameters.Clear();
148 return tf;
149 }
150 catch (System.Data.SqlClient.SqlException e)
151 {
152 if (tran != null )
153 {
154 tran.Rollback();
155 }
156 throw new Exception(e.Message);
157 }
158 finally
159 {
160 this .Close();
161 }
162 }
163
164 public bool RunProc( string procName, SqlParameter[] prams, out string value)
165 {
166 bool tf = false ;
167 SqlTransaction tran = null ;
168 string returnValue = "" ;
169 try
170 {
171 Open();
172 SqlCommand cmd = CreateProcCommand(procName, prams, "" );
173 tran = conn.BeginTransaction();
174 cmd.Transaction = tran;
175 if (cmd.ExecuteNonQuery() > 0 )
176 tf = true ;
177 value = cmd.Parameters[ " @ReturnValue " ].Value.ToString();
178 if (tran != null )
179 {
180 tran.Commit();
181 }
182 cmd.Parameters.Clear();
183 return tf;
184 }
185 catch (System.Data.SqlClient.SqlException e)
186 {
187 if (tran != null )
188 {
189 tran.Rollback();
190 }
191 throw new Exception(e.Message);
192 }
193 finally
194 {
195 this .Close();
196 }
197 }
198 #endregion

1 #region 参数的输入，输出，返回
2 public SqlParameter MakeInParam( string ParamName, SqlDbType DbType, int Size, object Value)
3 {
4 return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
5 }
6
7 public SqlParameter MakeOutParam( string ParamName, SqlDbType DbType, int Size)
8 {
9 return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null );
10 }
11
12 public SqlParameter MakeReturnParam( string ParamName, SqlDbType DbType, int Size)
13 {
14 return MakeParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null );
15 }
16
17 public SqlParameter MakeParam( string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
18 {
19 SqlParameter param;
20
21 if (Size > 0 )
22 param = new SqlParameter(ParamName, DbType, Size);
23 else
24 param = new SqlParameter(ParamName, DbType);
25
26 param.Direction = Direction;
27 if ( ! (Direction == ParameterDirection.Output && Value == null ))
28 param.Value = Value;
29
30 return param;
31 }
32 #endregion
33
34 }
35 }

转载于:https://www.cnblogs.com/weiqt/articles/2000750.html