SqlSugar-执行Sql语句查询实例_sqlsugar 执行存储过程

使用SqlSugar执行sql语句

1.简单查询

SqlSugarClient db => GetInstance();
//执行sql语句，处理
//1.执行sql，转成list
List<teacher> list1 = db.Ado.SqlQuery<teacher>("select * from teacher where tsex=@tsex", new { tsex = "女" });
Console.WriteLine(list1.ToJsonString());
//2.转成dynamic
dynamic list2 = db.Ado.SqlQueryDynamic("select * from UserInfo");
Console.WriteLine(list2.Length);
//3.转成json数据
string list3 = db.Ado.SqlQueryJson("select * from UserInfo");
Console.WriteLine(list3);
//4.返回int
int count = db.Ado.SqlQuery<int>("select count(*) from UserInfo").FirstOrDefault();
Console.WriteLine(count);
 
//5.返回键值对类型
Dictionary<string, string> list4 = db.Ado.SqlQuery<KeyValuePair<string, string>>("select UserID,Name from UserInfo")
    .ToDictionary(q => q.Key, q => q.Value);
Console.WriteLine(list4.ToJsonString());
 
//6.返回List<string[]> 集合
List<string[]> list5 = db.Ado.SqlQuery<string[]>("select  * from teacher where tsex=@tsex", new { tsex = "女" });
Console.WriteLine(list5.ToJsonString());
//返回 DataTable
DataTable dataTable =  db.Ado.GetDataTable("select * from teacher where tsex=@tsex", new { tsex = "女" });
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

2.汇总

SqlSugarClient db => GetInstance();
//更方便的获取第一行第一列
string result1 = db.Ado.GetString(" select  name from UserInfo where UserID=@UserID", new { UserID = 1 });
Console.WriteLine(result1);
int count = db.Ado.GetInt("select count(*) from UserInfo");
Console.WriteLine(count);
double result2 = db.Ado.GetDouble("select avg(degree) from score where cno=@cno ", new System.Data.SqlClient.SqlParameter("@cno", "3-105"));
Console.WriteLine(result2);
 
decimal result3 = db.Ado.GetDecimal(" select avg(degree) from score");
Console.WriteLine(result3);
1
2
3
4
5
6
7
8
9
10
11

3.执行视图 存储过程

SqlSugarClient db => GetInstance();
//执行视图查询
List<student> list1 = db.SqlQuery<student>("select * from V_student");
Console.WriteLine(list1.ToJsonString());
//执行存储过程处理
var pars = SqlSugarTool.GetParameters(new { pageStart = 1, pageEnd = 5, recordCount = 0 });
//禁止清空参数
db.IsClearParameters = false;
pars[2].Direction = System.Data.ParameterDirection.Output;
List<student> list2 = db.SqlQuery<student>("exec proc_PageStudent @pageStart,@pageEnd,@recordCount output", pars);
db.IsClearParameters = true;//启用自动清空参数
var recordCount = pars[2].Value;
Console.WriteLine(list2.ToJsonString());
Console.WriteLine(recordCount);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

4.DataTable转list类

public class DataConvertList<T> where T : new()
    {
        Logger log = new Logger(typeof(DataConvertList<T>));
        /// <summary>
        /// 只轉換一層的實體類對象，實體類中的實體類不參與轉換了
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public List<T> ConvertToList(DataTable dt)
        {
            // 定义集合  
            List<T> ts = new List<T>();
 
            // 获得此模型的类型  
            Type type = typeof(T);
            //定义一个临时变量  
            string tempName = string.Empty;
            //遍历DataTable中所有的数据行  
            foreach (DataRow dr in dt.Rows)
            {
                T t = new T();
                // 获得此模型的公共属性  
                PropertyInfo[] propertys = t.GetType().GetProperties();
                //遍历该对象的所有属性  
                foreach (PropertyInfo pi in propertys)
                {
                    try
                    {
 
                        ModelType modelType = GetModelType(pi.PropertyType);
                        if (modelType == ModelType.Else)//引用类型
                        {
                            Assembly assembly = Assembly.GetExecutingAssembly(); // 获取当前程序集
                            var obj = assembly.CreateInstance(pi.PropertyType.FullName); // 创建类的实例，返回为 object 类型，需要强制类型转换
                            //引用类型 必须对泛型实例化
                            PropertyInfo[] _propertys = obj.GetType().GetProperties();
                            setPropertyData(_propertys, obj, dt, dr);
 
                            pi.SetValue(t, obj, null);
                            continue;
                        }
                        tempName = pi.Name;//将属性名称赋值给临时变量  
                                           //检查DataTable是否包含此列（列名==对象的属性名）    
                                           //if (dt.Columns.ContainsKey(tempName))
                        if (dt.Columns.Contains(tempName))
                        {
                            // 判断此属性是否有Setter  
                            if (!pi.CanWrite) continue;//该属性不可写，直接跳出  
                                                       //取值  
                            object value = dr[tempName];
                            //如果非空，则赋给对象的属性  
                            if (value != DBNull.Value)
                                pi.SetValue(t, value, null);
                        }
                    }
                    catch (Exception e)
                    {
                        log.Error("tempName轉換失敗", e);
                        continue;
                    }
 
                }
                //对象添加到泛型集合中  
                ts.Add(t);
            }
            return ts;
        }
 
        /// <summary>
        /// 設置第二層的對象
        /// </summary>
        /// <param name="propertys"></param>
        /// <param name="t"></param>
        /// <param name="dt"></param>
        /// <param name="dr"></param>
        private void setPropertyData(PropertyInfo[] propertys, object t, DataTable dt, DataRow dr)
        {
            string tempName = string.Empty;
            foreach (PropertyInfo pi in propertys)
            {
                try
                {
 
                    ModelType modelType = GetModelType(pi.PropertyType);
                    if (modelType == ModelType.Else)//引用类型
                    {
                        continue;
                    }
                    tempName = pi.Name;//将属性名称赋值给临时变量  
                                       //检查DataTable是否包含此列（列名==对象的属性名）    
                                       //if (dt.Columns.ContainsKey(tempName))
                    if (dt.Columns.Contains(tempName))
                    {
                        // 判断此属性是否有Setter  
                        if (!pi.CanWrite) continue;//该属性不可写，直接跳出  
                                                   //取值  
                        object value = dr[tempName];
                        //如果非空，则赋给对象的属性  
                        if (value != DBNull.Value)
                            pi.SetValue(t, value, null);
                    }
                }
                catch (Exception e)
                {
                    log.Error("tempName轉換失敗", e);
                    continue;
                }
 
            }
        }
 
        /// <summary>
        /// 类型枚举
        /// </summary>
        private enum ModelType
        {
            //值类型
            Struct,
            Enum,
            //引用类型
            String,
            Object,
            Else
        }
 
        private static ModelType GetModelType(Type modelType)
        {
            //值类型
            if (modelType.IsEnum)
            {
                return ModelType.Enum;
            }
            //值类型
            if (modelType.IsValueType)
            {
                return ModelType.Struct;
            }
            //引用类型 特殊类型处理,c#对string也当做值类型处理
            if (modelType == typeof(string))
            {
                return ModelType.String;
            }
            //引用类型 特殊类型处理
            return modelType == typeof(object) ? ModelType.Object : ModelType.Else;
        }
 
 
 
 
 
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151

其它

单一类
var exp = Expressionable.Create<TestLog>();
exp.AndIF(!string.IsNullOrWhiteSpace(tbSN.Text.Trim()), (s) => s.Sn == tbSN.Text.Trim());

两个类 有关联关系 
{
    var exp = Expressionable.Create<Model1, Model2>();
    exp.AndIF(!string.IsNullOrEmpty(DevType), (s, e) => e.Category == DevType);
    exp.AndIF(!string.IsNullOrEmpty(DevName), (s, e) => e.Name == DevName);
    exp.AndIF(StartTime is not null, (s, e) => s.CreateTime >= StartTime);
    exp.AndIF(EndTime is not null, (s, e) => s.CreateTime <= EndTime.Value.AddDays(1));
    totalData = _sqlSugarClient.Queryable<Model1>()
       .InnerJoin<Model2>((s, e) => s.EID == e.Id)
       .Where(exp.ToExpression())
       .Select((s, e) => new
       {
           e.Name,
		   e.Port,
		   e.IP,
		   e.Category,
           s.CreateTime
       })
        .MergeTable()//将查询出来的结果合并成一个新表
        .GroupBy(it => new { it.Name, it.IP, it.Port,it.CreateTime})//对新表进行分组
        .Select(it => new StatisticsModel()
        {
            Name = it.Name, 
			Category=it.Category,
            Number = SqlFunc.AggregateCount(it.Name),
            StartTime = it.CreateTime,
            EndTime = it.CreateTime
        }).
        ToList();
}
 

this.PageModel.PageIndex = 1;
int num = totalData.Count % this.PageModel.DataCountPerPage;
this.PageModel.MaxPageCount = num == 0 ? totalData.Count / this.PageModel.DataCountPerPage : (totalData.Count / this.PageModel.DataCountPerPage) + 1;
var res = totalData.Take(this.PageModel.DataCountPerPage);
DataList = new ObservableCollection<StatisticsModel>(res);
         
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42