DBHelper.cs
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Configuration; 6 using System.Data.SqlClient; 7 using System.Data; 8 9 namespace APMS.Test 10 { 11 /// <summary> 12 /// DBHelper.cs 13 /// </summary> 14 public class DBHelper 15 { 16 private static string ConnectionString = ConfigurationManager.AppSettings["DbHelperConnectionString"]; 17 private static SqlConnection conn; 18 19 public DBHelper() 20 { 21 22 } 23 24 /// <summary> 25 /// 创建SqlConnection连接 26 /// </summary> 27 /// <returns>返回SqlConnection对象</returns> 28 public static SqlConnection CreateConnection() 29 { 30 conn = new SqlConnection (ConnectionString); 31 conn.Open(); 32 return conn; 33 } 34 35 /// <summary> 36 /// 执行存储过程 37 /// </summary> 38 /// <param name="storedProcedure">存储过程语句</param> 39 /// <returns>返回SqlCommand对象</returns> 40 public SqlCommand GetStoredProcCommand(string storedProcedure) 41 { 42 SqlCommand cmd = new SqlCommand (storedProcedure,conn); 43 cmd.CommandType= CommandType.StoredProcedure; 44 return cmd; 45 } 46 /// <summary> 47 /// 执行Sql语句 48 /// </summary> 49 /// <param name="sqlQuery">Sql语句</param> 50 /// <returns>返回SqlCommand对象</returns> 51 public SqlCommand GetSqlStringCommand(string sqlQuery) 52 { 53 SqlCommand cmd = new SqlCommand (sqlQuery,conn); 54 cmd.CommandType= CommandType.Text; 55 return cmd; 56 } 57 58 #region 增加参数 59 /// <summary> 60 /// 增加参数 61 /// </summary> 62 /// <param name="cmd">cmd对象</param> 63 /// <param name="ParametersCollection">SqlParameterCollection数据集合</param> 64 public void AddParameterCollection(SqlCommand cmd, SqlParameterCollection ParametersCollection) 65 { 66 foreach (SqlParameter Parameters in ParametersCollection) 67 { 68 cmd.Parameters.Add(Parameters); 69 } 70 } 71 /// <summary> 72 /// 增加参数 73 /// </summary> 74 /// <param name="cmd">cmd对象</param> 75 /// <param name="parameterName">parameterName</param> 76 /// <param name="sqlType">类型</param> 77 /// <param name="size">大小</param> 78 public void AddInParameter(SqlCommand cmd, string parameterName, SqlDbType sqlType, int size) 79 { 80 SqlParameter parameters = cmd.CreateParameter(); 81 parameters.SqlDbType = sqlType; 82 parameters.ParameterName = parameterName; 83 parameters.Size = size; 84 parameters.Direction = ParameterDirection.Input; 85 cmd.Parameters.Add(parameters); 86 } 87 88 public void AddInParameter(SqlCommand cmd, string parameterName, SqlDbType sqlType, object value) 89 { 90 SqlParameter parameters = cmd.CreateParameter(); 91 parameters.SqlDbType = sqlType; 92 parameters.ParameterName = parameterName; 93 parameters.Value = value; 94 parameters.Direction = ParameterDirection.Input; 95 cmd.Parameters.Add(parameters); 96 } 97 public void AddInParameter(SqlCommand cmd, string parameterName, SqlDbType sqlType) 98 { 99 SqlParameter parameters = cmd.CreateParameter(); 100 parameters.SqlDbType = sqlType; 101 parameters.ParameterName = parameterName; 102 parameters.Direction= ParameterDirection.Input; 103 cmd.Parameters.Add(parameters); 104 } 105 public SqlParameter GetParameter(SqlCommand cmd, string parameterName) 106 { 107 return cmd.Parameters[parameterName]; 108 } 109 110 #endregion 111 112 #region 执行 113 /// <summary> 114 /// 执行SqlCommand命令,返回DataSet对象 115 /// </summary> 116 /// <param name="cmd">SqlCommand对象</param> 117 /// <returns>返回DataSet对象</returns> 118 public DataSet ExecuteDataSet(SqlCommand cmd) 119 { 120 SqlDataAdapter da = new SqlDataAdapter (cmd); 121 DataSet ds = new DataSet (); 122 da.Fill(ds); 123 return ds; 124 } 125 /// <summary> 126 /// 执行SqlCommand命令,返回DataTable对象 127 /// </summary> 128 /// <param name="cmd">SqlCommand命令</param> 129 /// <returns>返回DataTable对象</returns> 130 public DataTable ExecuteDataTable(SqlCommand cmd) 131 { 132 SqlDataAdapter da = new SqlDataAdapter (cmd); 133 DataTable dt = new DataTable (); 134 da.Fill(dt); 135 return dt; 136 } 137 /// <summary> 138 /// 执行SqlCommand命令,返回SqlDataReader对象 139 /// </summary> 140 /// <param name="cmd">SqlCommand命令</param> 141 /// <returns>返回SqlDataReader对象</returns> 142 public SqlDataReader ExecuteReader(SqlCommand cmd) 143 { 144 SqlConnection conn = DBHelper.CreateConnection(); 145 SqlDataReader reader = cmd.ExecuteReader(); 146 return reader; 147 } 148 /// <summary> 149 /// 执行SqlCommand命令,返回Int 150 ///</summary> 151 /// <param name="cmd">SqlCommand命令</param> 152 /// <returns>返回Int</returns> 153 public int ExecuteNonQuery(SqlCommand cmd) 154 { 155 SqlConnection sqlconn = DBHelper.CreateConnection(); 156 int ret = cmd.ExecuteNonQuery(); 157 sqlconn.Close(); 158 return ret; 159 } 160 161 /// <summary> 162 /// 执行SqlCommand命令,返回Object 163 /// </summary> 164 /// <param name="cmd">SqlCommand命令</param> 165 /// <returns>返回Object</returns> 166 public object ExecuteScalar(SqlCommand cmd) 167 { 168 SqlConnection sqlconn = DBHelper.CreateConnection(); 169 object ret = cmd.ExecuteScalar(); 170 sqlconn.Close(); 171 return ret; 172 } 173 #endregion 174 175 /*********************示例**************************************** 176 1)直接执行sql语句 177 178 DbHelper db = new DbHelper(); 179 DbCommand cmd = db.GetSqlStringCommond("insert t1 (id)values(‘haha‘)"); 180 db.ExecuteNonQuery(cmd); 181 2)执行存储过程 182 183 DbHelper db = new DbHelper(); 184 DbCommand cmd = db.GetStoredProcCommond("t1_insert"); 185 db.AddInParameter(cmd, "@id", DbType.String, "heihei"); 186 db.ExecuteNonQuery(cmd); 187 3)返回DataSet 188 189 DbHelper db = new DbHelper(); 190 DbCommand cmd = db.GetSqlStringCommond("select * from t1"); 191 DataSet ds = db.ExecuteDataSet(cmd); 192 4)返回DataTable 193 194 DbHelper db = new DbHelper(); 195 DbCommand cmd = db.GetSqlStringCommond("t1_findall"); 196 DataTable dt = db.ExecuteDataTable(cmd); 197 5)输入参数/输出参数/返回值的使用(比较重要哦) 198 199 DbHelper db = new DbHelper(); 200 DbCommand cmd = db.GetStoredProcCommond("t2_insert"); 201 db.AddInParameter(cmd, "@timeticks", DbType.Int64, DateTime.Now.Ticks); 202 db.AddOutParameter(cmd, "@outString", DbType.String, 20); 203 db.AddReturnParameter(cmd, "@returnValue", DbType.Int32); 204 205 db.ExecuteNonQuery(cmd); 206 207 string s = db.GetParameter(cmd, "@outString").Value as string;//out parameter 208 int r = Convert.ToInt32(db.GetParameter(cmd, "@returnValue").Value);//return value 209 210 6)DataReader使用 211 212 DbHelper db = new DbHelper(); 213 DbCommand cmd = db.GetStoredProcCommond("t2_insert"); 214 db.AddInParameter(cmd, "@timeticks", DbType.Int64, DateTime.Now.Ticks); 215 db.AddOutParameter(cmd, "@outString", DbType.String, 20); 216 db.AddReturnParameter(cmd, "@returnValue", DbType.Int32); 217 218 using (DbDataReader reader = db.ExecuteReader(cmd)) 219 { 220 dt.Load(reader); 221 } 222 string s = db.GetParameter(cmd, "@outString").Value as string;//out parameter 223 int r = Convert.ToInt32(db.GetParameter(cmd, "@returnValue").Value);//return value 224 225 226 7)事务的使用.(项目中需要将基本的数据库操作组合成一个完整的业务流时,代码级的事务是必不可少的哦) 227 228 以上我们好像没有指定数据库连接字符串,大家如果看下DbHelper的代码,就知道要使用它必须在config中配置两个参数,如下: 229 230 pubic void DoBusiness() 231 { 232 using (Trans t = new Trans()) 233 { 234 try 235 { 236 D1(t); 237 throw new Exception();//如果有异常,会回滚滴 238 D2(t); 239 t.Commit(); 240 } 241 catch 242 { 243 t.RollBack(); 244 } 245 } 246 } 247 public void D1(Trans t) 248 { 249 DbHelper db = new DbHelper(); 250 DbCommand cmd = db.GetStoredProcCommond("t2_insert"); 251 db.AddInParameter(cmd, "@timeticks", DbType.Int64, DateTime.Now.Ticks); 252 db.AddOutParameter(cmd, "@outString", DbType.String, 20); 253 db.AddReturnParameter(cmd, "@returnValue", DbType.Int32); 254 255 if (t == null) db.ExecuteNonQuery(cmd); 256 else db.ExecuteNonQuery(cmd,t); 257 258 string s = db.GetParameter(cmd, "@outString").Value as string;//out parameter 259 int r = Convert.ToInt32(db.GetParameter(cmd, "@returnValue").Value);//return value 260 } 261 public void D2(Trans t) 262 { 263 DbHelper db = new DbHelper(); 264 DbCommand cmd = db.GetSqlStringCommond("insert t1 (id)values(‘..‘)"); 265 if (t == null) db.ExecuteNonQuery(cmd); 266 else db.ExecuteNonQuery(cmd, t); 267 } 268 <appSettings> 269 <add key="DbHelperProvider" value="System.Data.SqlClient"/> 270 <add key="DbHelperConnectionString" value="Data Source=(local);Initial Catalog=DbHelperTest;Persist Security Info=True;User ID=sa;Password=sa"/> 271 appSettings>* */ 272 } 273 } 274