1 ///
2 /// 数据库帮助类
3 /// vito
4 ///
5 public class DBHelper
6 {
7
8
9 ///
10 /// 数据库连接字符串
11 ///
12 private string connStr = System.Configuration.ConfigurationManager.AppSettings["connStr"].ToString();
13
14
15 ///
16 /// 一个有效的数据库连接对象
17 ///
18 private SqlConnection _MyConnection;
19 public SqlConnection MyConnection
20 {
21 get
22 {
23 if(_MyConnection == null)
24 {
25 _MyConnection = new SqlConnection(connStr);
26 }
27 return _MyConnection;
28 }
29 }
30
31
32 ///
33 /// 返回受影响行数
34 ///
35 /// SQL语句
36 /// 参数集
37 ///
38 public int MyExecuteNonQuery(string cmdText, params SqlParameter[] commandParameters)
39 {
40 int result = 0;
41 SqlCommand cmd = new SqlCommand();
42 SqlConnection conn = MyConnection;
43 try
44 {
45 PrepareCommand(cmd, conn, null, cmdText, commandParameters);
46 result = cmd.ExecuteNonQuery();
47 }
48 catch(SqlException ex)
49 {
50 throw new Exception(ex.Message);
51 }
52 finally
53 {
54 cmd.Parameters.Clear();
55 }
56 return result;
57 }
58
59
60
61 ///
62 /// 返回DataSet
63 ///
64 /// SQL语句
65 /// 参数集
66 ///
67 public DataSet MyExecuteDataset(string cmdText, params SqlParameter[] commandParameters)
68 {
69 DataSet ds = new DataSet();
70 SqlConnection conn = MyConnection;
71 SqlCommand cmd = new SqlCommand();
72 PrepareCommand(cmd, conn, null, cmdText, commandParameters);
73 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
74 {
75 try
76 {
77 da.Fill(ds);
78 }
79 catch (SqlException ex)
80 {
81 throw new Exception(ex.Message);
82 }
83 finally
84 {
85 cmd.Parameters.Clear();
86 }
87 }
88 return ds;
89 }
90
91 ///
92 /// 返回DataReader (使用后请关闭DataReader)
93 ///
94 /// SQL语句
95 /// 参数集
96 ///
97 public SqlDataReader MyExecuteReader(string cmdText, params SqlParameter[] commandParameters)
98 {
99 SqlConnection conn = MyConnection;
100 SqlCommand cmd = new SqlCommand();
101 try
102 {
103 PrepareCommand(cmd,conn,null,cmdText,commandParameters);
104 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//关闭DataReader的同时，Connection也将同时关闭
105 return myReader;
106 }
107 catch(SqlException ex)
108 {
109 throw new Exception(ex.Message);
110 }
111 finally
112 {
113 cmd.Parameters.Clear();
114 }
115 }
116
117 ///
118 /// 返回首行首列的值
119 ///
120 /// SQL语句
121 /// 参数集
122 ///
123 public object MyExecuteScalar(string cmdText,params SqlParameter[] commandParameters)
124 {
125 SqlConnection conn = MyConnection;
126 SqlCommand cmd = new SqlCommand();
127 try
128 {
129 PrepareCommand(cmd,conn,null,cmdText,commandParameters);
130 return cmd.ExecuteScalar();
131 }
132 catch(SqlException ex)
133 {
134 throw new Exception(ex.Message);
135 }
136 finally
137 {
138 cmd.Parameters.Clear();
139 }
140 }
141
142 ///
143 /// 设置SqlCommand
144 ///
145 /// 执行对象
146 /// 连接对象
147 /// 事务
148 /// SQL语句
149 /// 参数集
150 private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
151 {
152 if (conn.State != ConnectionState.Open)
153 conn.Open();
154 cmd.Connection = conn;
155 cmd.CommandText = cmdText;
156 if (trans != null)
157 cmd.Transaction = trans;
158 cmd.CommandType = CommandType.Text;
159 if (cmdParms != null)
160 {
161 foreach (SqlParameter parameter in cmdParms)
162 {
163 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
164 {
165 parameter.Value = DBNull.Value;
166 }
167 cmd.Parameters.Add(parameter);
168 }
169 }
170 }
171 }