随手搭建自己的sql数据库操作类-CSDN博客

参加工作将近一年了，发现自己从来没有写过自己的数据库操作类，基本上都是用的公司框架提供的ORM，但是公司的orm又太大了，不适合自己开发的个人项目使用，于是想写一个最基础的数据库操作类，基本上是操作sql数据库，所以暂时只写sql，别的数据库类型等有时间再补上。

基于抽象的思维，我们需要首先想好这个操作类要干嘛，基本上就是增删改查了，于是我们定义一个接口：ISqlDataBase

 1 using System.Data;
 2 using System.Data.Common;
 3 
 4 namespace SqlDatabase
 5 {
 6     public interface ISqlDataBase
 7     {
 8         object FindObject(string strSql);
 9         object FindObject(string strSql, DbParameter[] dbParameter);
10         DataTable FindTable(string strSql, DbParameter[] dbParameter, string orderField, bool isAsc, int pageSize, int pageIndex, out int total);
11         DataTable FindTable(string strSql, string orderField, bool isAsc, int pageSize, int pageIndex, out int total);
12         int SqlIDU(string sql);
13         DataTable SqlSelect(string sql);
14         DataTable SqlSelect(string sql, DbParameter[] dbParameter);
15     }
16 }

这个借口定义了查询单个对象、查询分页数据集合、查询数据集合、执行增删改的操作。

然后就是sql数据库操作类了，代码如下：

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Data;
  4 using System.Data.Common;
  5 using System.Data.SqlClient;
  6 using System.Text;
  7 using Util;
  8 
  9 namespace SqlDatabase
 10 {
 11     public class SqlDataBase : ISqlDataBase
 12     {
 13         private readonly string connectionString;
 14         
 15         public IDbConnection _dbConnection;
 16         private IDbCommand _dbCommand;
 17 
 18         /// <summary>
 19         /// 创建数据库连接
 20         /// </summary>
 21         /// <param name="connectionString"></param>
 22         /// <returns></returns>
 23         public SqlDataBase(string connection)
 24         {
 25             this.connectionString = connection;
 26             this._dbConnection = new SqlConnection(this.connectionString);
 27             this._dbCommand = _dbConnection.CreateCommand();
 28             //判断数据库连接状态
 29             if (_dbConnection != null && _dbConnection.State != ConnectionState.Closed && _dbConnection.State != ConnectionState.Broken)
 30                 return;
 31             if (_dbConnection == null)
 32                 throw new Exception("数据库连接对象不存在");
 33             if (_dbConnection.State == ConnectionState.Broken)
 34                 _dbConnection.Close();
 35             if (_dbConnection.State == ConnectionState.Closed)
 36                 _dbConnection.Open();
 37         }
 38         public int SqlIDU(string sql)
 39         {
 40             _dbCommand.CommandText = sql;
 41             var result = _dbCommand.ExecuteNonQuery();
 42             _dbCommand.Dispose();
 43             return result;
 44         }
 45         public int SqlIDU(string sql,DbParameter[] dbParameter)
 46         {
 47             _dbCommand.CommandText = sql;
 48             foreach (var parameter in dbParameter)
 49             {
 50                 _dbCommand.Parameters.Add(parameter);
 51             }
 52             var result = _dbCommand.ExecuteNonQuery();
 53             _dbCommand.Parameters.Clear();
 54             _dbCommand.Dispose();
 55             return result;
 56         }
 57         public DataTable SqlSelect(string sql)
 58         {
 59             _dbCommand.CommandText = sql;
 60             var result = DataHelp.IDataReader2DataTable(_dbCommand.ExecuteReader());
 61             _dbCommand.Dispose();
 62             return result;
 63         }
 64         public IEnumerable<T> SqlSelect<T>(string sql) where T:class
 65         {
 66             _dbCommand.CommandText = sql;
 67             var result = DataHelp.IDataReader2DataTable(_dbCommand.ExecuteReader());
 68             _dbCommand.Dispose();
 69             return Util.Util.ToList<T>(result);
 70         }
 71         public DataTable SqlSelect(string sql, DbParameter[] dbParameter)
 72         {
 73             _dbCommand.CommandText = sql;
 74             foreach (var parameter in dbParameter)
 75             {
 76                 _dbCommand.Parameters.Add(parameter);
 77             }
 78             var result = DataHelp.IDataReader2DataTable(_dbCommand.ExecuteReader());
 79             _dbCommand.Dispose();
 80             return result;
 81         }
 82         public DataTable FindTable(string strSql, string orderField, bool isAsc, int pageSize, int pageIndex, out int total)
 83         {
 84             StringBuilder sb = new StringBuilder();
 85             if (pageIndex == 0)
 86             {
 87                 pageIndex = 1;
 88             }
 89             int num = (pageIndex - 1) * pageSize;
 90             int num1 = (pageIndex) * pageSize;
 91             string OrderBy = "";
 92 
 93             if (!string.IsNullOrEmpty(orderField))
 94             {
 95                 if (orderField.ToUpper().IndexOf("ASC") + orderField.ToUpper().IndexOf("DESC") > 0)
 96                 {
 97                     OrderBy = "Order By " + orderField;
 98                 }
 99                 else
100                 {
101                     OrderBy = "Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
102                 }
103             }
104             else
105             {
106                 OrderBy = "order by (select 0)";
107             }
108             sb.Append("Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
109             sb.Append(" As rowNum, * From (" + strSql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");
110             total = Convert.ToInt32(FindObject("Select Count(1) From (" + strSql + ") As t"));
111             var resultTable = SqlSelect(sb.ToString());
112             resultTable.Columns.Remove("rowNum");
113             return resultTable;
114         }
115         public DataTable FindTable(string strSql, DbParameter[] dbParameter, string orderField, bool isAsc, int pageSize, int pageIndex, out int total)
116         {
117             StringBuilder sb = new StringBuilder();
118             if (pageIndex == 0)
119             {
120                 pageIndex = 1;
121             }
122             int num = (pageIndex - 1) * pageSize;
123             int num1 = (pageIndex) * pageSize;
124             string OrderBy = "";
125 
126             if (!string.IsNullOrEmpty(orderField))
127             {
128                 if (orderField.ToUpper().IndexOf("ASC") + orderField.ToUpper().IndexOf("DESC") > 0)
129                 {
130                     OrderBy = "Order By " + orderField;
131                 }
132                 else
133                 {
134                     OrderBy = "Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
135                 }
136             }
137             else
138             {
139                 OrderBy = "order by (select 0)";
140             }
141             sb.Append("Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
142             sb.Append(" As rowNum, * From (" + strSql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");
143             total = Convert.ToInt32(FindObject("Select Count(1) From (" + strSql + ") As t", dbParameter));
144             var resultTable = SqlSelect(sb.ToString(), dbParameter);
145             resultTable.Columns.Remove("rowNum");
146             return resultTable;
147         }
148         #region 拓展操作
149         public object FindObject(string strSql)
150         {
151             _dbCommand.CommandText = strSql;
152             var result = _dbCommand.ExecuteScalar();
153             return result;
154         }
155         public object FindObject(string strSql, DbParameter[] dbParameter)
156         {
157             _dbCommand.CommandText = strSql;
158             dbParameter = new SqlParameter[dbParameter.Length];
159             foreach (var parameter in dbParameter)
160             {
161                 _dbCommand.Parameters.Add(parameter);
162             }
163             var result = _dbCommand.ExecuteScalar();
164             return result;
165         }
166 
167         #endregion
168     }
169 }

在实现过程中会发现需要一些工具类进行转换，于是我们创建一个工具类：

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Data;
 4 using System.Data.SqlClient;
 5 using System.Linq;
 6 using System.Text;
 7 using System.Threading.Tasks;
 8 
 9 namespace Util
10 {
11    public static class DataHelp
12     {
13         public static DataTable IDataReader2DataTable(IDataReader reader)
14         {
15             DataTable objDataTable = new DataTable("Table");
16             int intFieldCount = reader.FieldCount;
17             for (int intCounter = 0; intCounter < intFieldCount; ++intCounter)
18             {
19                 objDataTable.Columns.Add(reader.GetName(intCounter).ToUpper(), reader.GetFieldType(intCounter));
20             }
21             objDataTable.BeginLoadData();
22             object[] objValues = new object[intFieldCount];
23             while (reader.Read())
24             {
25                 reader.GetValues(objValues);
26                 objDataTable.LoadDataRow(objValues, true);
27             }
28             reader.Close();
29             objDataTable.EndLoadData();
30             return objDataTable;
31         }
32     }
33 }

 1 using Newtonsoft.Json;
 2 using Newtonsoft.Json.Converters;
 3 using System.Collections.Generic;
 4 using System.Data;
 5 
 6 namespace Util
 7 {
 8     public static class Util
 9     {
10         public static List<T> ToList<T>(this DataTable dataTable)
11         {
12             string jsonString = JsonConvert.SerializeObject(dataTable, new DataTableConverter());
13             return JsonConvert.DeserializeObject<List<T>>(jsonString);
14         }
15         
16     }
17 }