增删改查

封装的增删改查类SqlHelper

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Configuration;
  4 using System.Data;
  5 using System.Data.SqlClient;
  6 using System.Linq;
  7 using System.Text;
  8 
  9 namespace ADO.NET
 10 {
 11     public class SqlHelper : IDBHelper
 12     {
 13         private static string _connString = ConfigurationManager.ConnectionStrings["DataBase"].ConnectionString;
 14         private T SqlExecute<T>(string sql, Func<SqlCommand, T> func)
 15         {
 16             using (SqlConnection conn = new SqlConnection(_connString))
 17             {
 18                 conn.Open();
 19                 SqlCommand cmd = conn.CreateCommand();
 20                 cmd.CommandText = sql;
 21                 cmd.CommandType = CommandType.Text;
 22                 return func(cmd);
 23             }
 24         }
 25 
 26         /// <summary>
 27         /// 根据Id查询
 28         /// </summary>
 29         /// <typeparam name="T"></typeparam>
 30         /// <param name="id">数据表中id自增</param>
 31         /// <returns></returns>
 32         public T QueryById<T>(int id) where T : BaseEntity
 33         {
 34             Type type = typeof(T);
 35             //拿到每一列
 36             string column = string.Join(",", type.GetProperties().Select(p => $"[{p.Name}]"));
 37             string sqlStr = $"select {column} from [{type.Name}] where id=@id";
 38             object t = Activator.CreateInstance(type);
 39             return SqlExecute<T>(sqlStr, cmd =>
 40             {
 41                 cmd.Parameters.Add(new SqlParameter("@id", id));
 42                 var reader = cmd.ExecuteReader();
 43                 while (reader.Read())
 44                 {
 45                     SetValueByProperties(type, reader, t);
 46                     return t as T;
 47                 }
 48                 return null;
 49             });
 50 
 51         }
 52 
 53         /// <summary>
 54         /// 查询全部
 55         /// </summary>
 56         /// <typeparam name="T"></typeparam>
 57         /// <returns></returns>
 58         public List<T> QueryAll<T>() where T : BaseEntity
 59         {
 60             Type type = typeof(T);
 61             string column = string.Join(",", type.GetProperties().Select(p => $"[{p.Name}]"));
 62             string sqlStr = $"select {column} from [{type.Name}]";
 63             List<T> listAll = new List<T>();
 64             return SqlExecute<List<T>>(sqlStr, cmd =>
 65              {
 66                  var reader = cmd.ExecuteReader();
 67                  while (reader.Read())
 68                  {
 69                      object t = Activator.CreateInstance(type);
 70                      SetValueByProperties(type, reader, t);
 71                      listAll.Add(t as T);
 72                  }
 73                  return listAll;
 74              });
 75         }
 76 
 77         /// <summary>
 78         /// 插入操作，主键自增不需插入
 79         /// </summary>
 80         /// <typeparam name="T"></typeparam>
 81         /// <param name="t"></param>
 82         /// <returns></returns>
 83         public bool Insert<T>(T t) where T : BaseEntity
 84         {
 85             Type type = typeof(T);
 86             string columnStr = string.Join(",", type.GetProperties().Where(s => s.Name != "id").Select(p => $"[{p.Name}]"));
 87             string valueStr = string.Join(",", type.GetProperties().Where(s => s.Name != "id").Select(p => $"@{p.Name}"));
 88             string SqlText = $"insert into [{type.Name}] ({columnStr}) values({valueStr})";
 89 
 90             return SqlExecute<bool>(SqlText, cmd =>
 91             {
 92                 SqlParameter[] sqlParam = type.GetProperties().Where(s => s.Name != "id").Select(p =>
 93                 new SqlParameter($"@{p.Name}", p.GetValue(t, null) ?? DBNull.Value)).ToArray();
 94 
 95                 cmd.Parameters.AddRange(sqlParam);
 96                 return cmd.ExecuteNonQuery() > 0;
 97             });
 98         }
 99 
100         /// <summary>
101         /// 根据id删除
102         /// </summary>
103         /// <typeparam name="T"></typeparam>
104         /// <param name="id"></param>
105         /// <returns></returns>
106         public bool DeleteById<T>(int id) where T : BaseEntity
107         {
108             Type type = typeof(T);
109             string sqlStr = $"delete from [{type.Name}] where id={id}";
110             return SqlExecute<bool>(sqlStr, cmd =>
111              {
112                  return cmd.ExecuteNonQuery() > 0;
113              });
114         }
115 
116         /// <summary>
117         /// 根据id更新
118         /// </summary>
119         /// <typeparam name="T"></typeparam>
120         /// <param name="id"></param>
121         /// <returns></returns>
122         public bool UpdateById<T>(int id, T t) where T : BaseEntity
123         {
124             Type type = typeof(T);
125 
126             string[] columnStr = string.Join(",", type.GetProperties()
127                 .Where(s => s.Name != "id" && s.GetValue(t, null) != null)
128                 .Select(p => $"[{p.Name}]")).Split(',');
129 
130             string[] valueStr = string.Join(",", type.GetProperties()
131                 .Where(s => s.Name != "id" && s.GetValue(t, null) != null)
132                 .Select(p => $"@{p.Name}")).Split(',');
133 
134             string setColumn = string.Empty;
135 
136             for (int i = 0; i < columnStr.Length; i++)
137             {
138                 setColumn += $"{columnStr[i]}={valueStr[i]},";
139             }
140             string sqlText = $"update [{type.Name}] set {setColumn.Remove(setColumn.Length - 1, 1)} where id={id}";
141             return SqlExecute(sqlText, cmd =>
142             {
143                 SqlParameter[] sqlParam = type.GetProperties().Where(s => s.Name != "id").Select(p =>
144                 new SqlParameter($"@{p.Name}", p.GetValue(t, null) ?? DBNull.Value)).ToArray();
145 
146                 cmd.Parameters.AddRange(sqlParam);
147                 return cmd.ExecuteNonQuery() > 0;
148             });
149         }
150 
151         /// <summary>
152         /// 根据SqlDataReader读到的值设置 t实例
153         /// </summary>
154         /// <param name="type"></param>
155         /// <param name="reader"></param>
156         /// <param name="t"></param>
157         private void SetValueByProperties(Type type, SqlDataReader reader, object t)
158         {
159             foreach (var item in type.GetProperties())
160             {
161                 if (reader[item.Name] is DBNull)
162                 {
163                     item.SetValue(t, null, null);
164                 }
165                 else
166                 {
167                     item.SetValue(t, reader[item.Name], null);
168                 }
169             }
170         }
171     }
172 }


实现接口：

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 
 6 namespace ADO.NET
 7 {
 8     public interface IDBHelper
 9     {
10         T QueryById<T>(int id) where T : BaseEntity;
11         List<T> QueryAll<T>() where T : BaseEntity;
12         bool Insert<T>(T t) where T : BaseEntity;
13         bool DeleteById<T>(int id) where T : BaseEntity;
14         bool UpdateById<T>(int id, T t) where T : BaseEntity;
15     }
16 }
View Code

创建简单工厂调用SqlHelper类

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Configuration;
 4 using System.Linq;
 5 using System.Reflection;
 6 using System.Text;
 7 
 8 namespace ADO.NET
 9 {
10     class SimpleFactory
11     {
12         private static string facotryName = ConfigurationManager.AppSettings["SqlHelper"];
13         
14         public static IDBHelper GetInstance()
15         {
16            return Assembly.Load("ADO.NET").CreateInstance(facotryName) as IDBHelper;
17         }
18     }
19 }
View Code

封装的业务类

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 
 6 namespace ADO.NET
 7 {
 8     class User:BaseEntity
 9     {
10         public string name { get; set; }
11         public string account { get; set; }
12         public string password { get; set; }
13         public string email { get; set; }
14         public string phone { get; set; }
15         public int? companyId { get; set; }
16         public string companyName { get; set; }
17         public DateTime? lastLoginTime { get; set; }
18         public DateTime? CreateTime { get; set; }
19     }
20 }
View Code

业务类继承的父类，所有业务类共同继承一个只有主键自增的id属性的类

1     public class BaseEntity
2     {
3         public int id { get; set; }
4     }
View Code

最终主程序调用

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 
 6 namespace ADO.NET
 7 {
 8     class Program
 9     {
10         static void Main(string[] args)
11         {
12             
13             User user= SimpleFactory.GetInstance().QueryById<User>(1);
14             Console.WriteLine($"姓名：{user.name}，账号：{user.account},密码：{user.password}");
15             List<User> userList = SimpleFactory.GetInstance().QueryAll<User>();
16             Console.WriteLine("************************************************");
17             foreach (var item in userList)
18             {
19                 Console.WriteLine($"姓名：{item.name}，账号：{item.account},密码：{item.password},email:{item.email},phone:{item.phone}");
20             }
21             Console.WriteLine("************************************************");
22             
23             bool insert = SimpleFactory.GetInstance().Insert<User>(new User()
24             {
25                 password = "ma123",
26                 name ="马六",
27                 email = "456789@123.com",
28                 phone = "236542",
29                 account ="maliu123"
30             });
31             if (insert)
32             {
33                 Console.WriteLine("新增成功");
34             }
35             else
36             {
37                 Console.WriteLine("新增失败");
38             }
39 
40             Console.WriteLine("*********************************************");
41             bool delete = SimpleFactory.GetInstance().DeleteById<User>(5);
42             if (delete)
43             {
44                 Console.WriteLine("删除成功");
45             }
46             else
47             {
48                 Console.WriteLine("删除失败");
49             }
50             bool update = SimpleFactory.GetInstance().UpdateById<User>(3,new User()
51             {
52                 name="阿宝",
53                 account="abao么么哒"
54             });
55             if (update)
56             {
57                 Console.WriteLine("修改成功");
58             }
59             else
60             {
61                 Console.WriteLine("修改失败");
62             }
63             Console.ReadLine();
64         }
65 
66         /*
67          <?xml version="1.0" encoding="utf-8" ?>
68            <configuration>
69               <appSettings>
70                 <add key="SqlHelper" value="ADO.NET.SqlHelper"></add>  //ADO.NET为命名空间
71               </appSettings>
72               <connectionStrings>
73                 <add name="****" connectionString="server=****;uid=****;pwd=****;database=****"/>  //带*的自己填
74               </connectionStrings>
75            </configuration>
76          */
77     }
78 }
View Code
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/AlexOneBlogs/p/7403991.html