SqlSugar-执⾏Sql语句查询实例
使⽤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 = "⼥" });
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);
sql语句查询不包含
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);
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;        }
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。