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 }
