虽然日常工作中都是调用别人写好的底层，但是要真正学到技术，还是要懂些底层原理，最好是能自己写底层
一、底层
注：引用命名空间
using System.Data;
using System.Data.SqlClient;1 public class SqlHelper 2 { 3 /// 4 /// 数据库连接字符串 5 /// Data Source=数据库地址;Initial Catalog=数据库名称;Persist Security Info=True;User ID=用户名;Password=密码 6 /// 7 private string _SqlConnectionStr = ""; 8 public string SqlConnectionStr { get { return _SqlConnectionStr; } } 9 public SqlHelper(string connStr) 10 { 11 this._SqlConnectionStr = connStr; 12 } 13 #region 单值查询 14 public string GetSingle(string sqlStr) 15 { 16 using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr)) 17 { 18 using (SqlCommand cmd = new SqlCommand(sqlStr, conn)) 19 { 20 try 21 { 22 conn.Open(); 23 return String.Format("{0}", cmd.ExecuteScalar()); 24 } 25 catch(SqlException e) 26 { 27 throw e; 28 } 29 finally 30 { 31 conn.Close(); 32 } 33 } 34 } 35 } 36 public string GetSingle(string sqlStr,SqlParameter[] cmdParams) 37 { 38 using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr)) 39 { 40 using (SqlCommand cmd = new SqlCommand()) 41 { 42 try 43 { 44 conn.Open(); 45 cmd.Connection = conn; 46 cmd.CommandType = CommandType.Text; 47 cmd.CommandText = sqlStr; 48 cmd.Parameters.AddRange(cmdParams); 49 return String.Format("{0}", cmd.ExecuteScalar()); 50 } 51 catch(SqlException e) 52 { 53 throw e; 54 } 55 finally 56 { 57 conn.Close(); 58 } 59 } 60 } 61 } 62 #endregion 63 64 #region 查询数据集 65 public DataSet Query(string sqlStr) 66 { 67 using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr)) 68 { 69 using (SqlDataAdapter ada = new SqlDataAdapter(sqlStr, conn)) 70 { 71 try 72 { 73 conn.Open(); 74 DataSet ds = new DataSet(); 75 ada.Fill(ds); 76 return ds; 77 } 78 catch(SqlException e) 79 { 80 throw e; 81 } 82 finally 83 { 84 conn.Close(); 85 } 86 } 87 } 88 } 89 public DataSet Query(string sqlStr,SqlParameter[] cmdParams) 90 { 91 using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr)) 92 { 93 using (SqlCommand cmd = new SqlCommand()) 94 { 95 using (SqlDataAdapter ada = new SqlDataAdapter(cmd)) 96 { 97 try 98 { 99 conn.Open();100 cmd.Connection = conn;101 cmd.CommandType = CommandType.Text;102 cmd.CommandText = sqlStr;103 cmd.Parameters.AddRange(cmdParams);104 105 DataSet ds = new DataSet();106 ada.Fill(ds);107 return ds; 108 }109 catch(SqlException e)110 {111 throw e;112 }113 finally114 {115 conn.Close();116 }117 }118 }119 }120 }121 public DataSet RunProcedure(string procName,SqlParameter[] cmdParams)122 {123 using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))124 {125 using (SqlCommand cmd = new SqlCommand())126 {127 using (SqlDataAdapter ada = new SqlDataAdapter(cmd))128 {129 try130 {131 conn.Open();132 cmd.Connection = conn;133 cmd.CommandType = CommandType.StoredProcedure;134 cmd.CommandText = procName;135 cmd.Parameters.AddRange(cmdParams);136 137 DataSet ds = new DataSet();138 ada.Fill(ds);139 return ds;140 }141 catch(SqlException e)142 {143 throw e;144 }145 finally146 {147 conn.Close();148 }149 }150 }151 }152 }153 #endregion154 155 #region 单表查询 156 public DataTable GetQueryData(string sqlStr)157 {158 DataSet ds = Query(sqlStr);159 if (ds != null && ds.Tables.Count > 0)160 return ds.Tables[0];161 return null; 162 }163 public DataTable GetQueryData(string sqlStr ,SqlParameter[] cmdParams)164 {165 DataSet ds = Query(sqlStr, cmdParams);166 if (ds != null && ds.Tables.Count > 0)167 return ds.Tables[0];168 return null;169 }170 public DataTable GetProcData(string procName,SqlParameter[] cmdParams)171 {172 DataSet ds = RunProcedure(procName, cmdParams);173 if (ds != null && ds.Tables.Count > 0)174 return ds.Tables[0];175 return null;176 }177 #endregion178 179 #region 单行查询 180 public DataRow GetQueryRecord(string sqlStr)181 {182 DataTable dt = GetQueryData(sqlStr);183 if (dt != null && dt.Rows.Count > 0)184 return dt.Rows[0];185 return null;186 }187 public DataRow GetQueryRecord(string sqlStr,SqlParameter[] cmdParams)188 {189 DataTable dt = GetQueryData(sqlStr, cmdParams);190 if (dt != null && dt.Rows.Count > 0)191 return dt.Rows[0];192 return null;193 }194 public DataRow GetProcRecord(string procName,SqlParameter[] cmdParams)195 {196 DataTable dt = GetProcData(procName, cmdParams);197 if (dt != null && dt.Rows.Count > 0)198 return dt.Rows[0];199 return null;200 }201 #endregion202 203 #region 使用完应关闭Reader204 public SqlDataReader ExecuteReader(string sqlStr)205 {206 SqlConnection conn = new SqlConnection(this._SqlConnectionStr);207 SqlCommand cmd = new SqlCommand(sqlStr, conn);208 try209 {210 conn.Open();211 return cmd.ExecuteReader(CommandBehavior.CloseConnection);212 }213 catch(SqlException e)214 {215 throw e;216 }217 }218 public SqlDataReader ExecuteReeder(string sqlStr,SqlParameter[] cmdParams)219 {220 SqlConnection conn = new SqlConnection(this._SqlConnectionStr);221 SqlCommand cmd = new SqlCommand();222 try223 {224 conn.Open();225 cmd.Connection = conn;226 cmd.CommandType = CommandType.Text;227 cmd.CommandText = sqlStr;228 cmd.Parameters.AddRange(cmdParams);229 return cmd.ExecuteReader(CommandBehavior.CloseConnection);230 }231 catch(SqlException e)232 {233 throw e;234 }235 }236 #endregion237 238 #region 执行sql语句 239 public int ExecuteSql(string sqlStr)240 {241 using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))242 {243 using (SqlCommand cmd = new SqlCommand(sqlStr, conn))244 {245 try246 {247 conn.Open();248 return cmd.ExecuteNonQuery();249 }250 catch(SqlException e)251 {252 throw e;253 }254 finally255 {256 conn.Close();257 }258 }259 }260 }261 public int ExecuteSql(string sqlStr,SqlParameter[] cmdParams)262 {263 using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))264 {265 using (SqlCommand cmd = new SqlCommand())266 {267 try268 {269 conn.Open();270 cmd.Connection = conn;271 cmd.CommandType = CommandType.Text;272 cmd.CommandText = sqlStr;273 cmd.Parameters.AddRange(cmdParams);274 return cmd.ExecuteNonQuery();275 }276 catch(SqlException e)277 {278 throw e;279 }280 finally281 {282 conn.Close();283 }284 }285 }286 }287 #endregion288 289 #region 执行事务 290 public int ExecuteSqlTran(List sqlStrList)291 {292 using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))293 {294 using (SqlCommand cmd = new SqlCommand())295 {296 using (SqlTransaction tran = conn.BeginTransaction())297 {298 try299 {300 cmd.Connection = conn;301 cmd.CommandType = CommandType.Text; 302 cmd.Transaction = tran;303 conn.Open();304 int count = 0;305 foreach(string sql in sqlStrList)306 {307 cmd.CommandText = sql;308 count += cmd.ExecuteNonQuery();309 }310 tran.Commit();311 return count;312 }313 catch(SqlException e)314 {315 tran.Rollback();316 throw e;317 }318 finally319 {320 conn.Close();321 }322 }323 }324 }325 }326 public int ExecuteSqlTran(List > sqlStrList)327 {328 using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))329 {330 using (SqlCommand cmd = new SqlCommand())331 {332 using (SqlTransaction tran = conn.BeginTransaction())333 {334 try335 {336 cmd.Connection = conn;337 cmd.CommandType = CommandType.Text;338 cmd.Transaction = tran;339 conn.Open();340 int count = 0;341 foreach(var item in sqlStrList)342 {343 cmd.CommandText = item.Key;344 cmd.Parameters.Clear();345 cmd.Parameters.AddRange(item.Value);346 count += cmd.ExecuteNonQuery();347 }348 tran.Commit();349 return count;350 }351 catch(SqlException e)352 {353 tran.Rollback();354 throw e;355 }356 finally357 {358 conn.Close();359 }360 }361 }362 }363 }364 public int ExecuteProc(string procName,SqlParameter[] cmdParams)365 {366 using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))367 {368 using (SqlCommand cmd = new SqlCommand())369 {370 try371 {372 conn.Open();373 cmd.Connection = conn;374 cmd.CommandType = CommandType.StoredProcedure;375 cmd.CommandText = procName;376 cmd.Parameters.AddRange(cmdParams);377 return cmd.ExecuteNonQuery();378 }379 catch(SqlException e)380 {381 throw e;382 }383 finally384 {385 conn.Close();386 }387 }388 } 389 }390 #endregion391 } 二、工厂模式
1 public class DbProvider 2 { 3 private static string _SqlConnectionStr = null; 4 public static string SqlConnectionStr 5 { 6 get 7 { 8 string connStr = _SqlConnectionStr; 9 if (connStr == null)10 connStr = "Data Source=(local);Initial Catalog=testDB;Persist Security Info=True;User ID=sa;Password=123456"; //一般这里是读取项目配置文件里的数据库参数来生成连接字符串11 return connStr;12 }13 }14 public static SqlHelper SqlServer15 {16 get17 {18 return new SqlHelper(SqlConnectionStr);19 }20 }21 } 三、实例使用
1 public class Demo2 {3 public DataTable QueryOrderInfo(string id)4 {5 string sql = "select * from order where id=@id";6 SqlParameter[] cmdParams = new SqlParameter[] { new SqlParameter("@id", id) };7 return DbProvider.SqlServer.GetQueryData(sql, cmdParams);8 }9 }