dapper
查询数据库是否存在数据 select isnull((select top(1) 1 from Menu where ParentId=@ParentId and Deleted='false'),0)
select count(1) from Sys_Admin where 1=1
publicList<MSys_Admin>GetAdminList(MSys_Admin model) { string sqlText ="select count(1) from Sys_Admin where 1=1"; var p =newDynamicParameters(); if(!string.IsNullOrEmpty(model.LoginName)) { sqlText +=" and LoginName like @LoginName"; p.Add("LoginName", model.LoginName+"%"); } if(!string.IsNullOrEmpty(model.Name)) { sqlText +=" and Name like @Name"; p.Add("Name","%"+ model.Name+"%"); } using(var conn =Common.GetConn()) { conn.Open(); var r = conn.Query<MSys_Admin>(sqlText, p); conn.Close(); return r.ToList(); } }
工具类
1 /// <summary> 2 /// 查询操作 3 /// </summary> 4 /// <typeparam name="T">返回集合的类型</typeparam> 5 /// <param name="sql">sql语句</param> 6 /// <param name="param">参数化值</param> 7 /// <returns></returns> 8 public IEnumerable<T> Query<T>(string sql, object param) 9 { 10 IEnumerable<T> _list = default(IEnumerable<T>); 11 if (!string.IsNullOrEmpty(sql)) 12 { 13 try 14 { 15 _list = DbConnection.Query<T>(sql, param); 16 } 17 catch { } 18 } 19 return _list; 20 } 21 22 /// <summary> 23 /// 执行存储过程查询操作 24 /// </summary> 25 /// <typeparam name="T">返回集合的类型</typeparam> 26 /// <param name="storedName">存储过程</param> 27 /// <param name="param">参数化值</param> 28 /// <returns></returns> 29 public IEnumerable<T> QueryStored<T>(string storedName, object param) 30 { 31 IEnumerable<T> _list = default(IEnumerable<T>); 32 if (!string.IsNullOrEmpty(storedName)) 33 { 34 try 35 { 36 _list = DbConnection.Query<T>(storedName,commandType: CommandType.StoredProcedure); 37 } 38 catch { } 39 } 40 return _list; 41 } 42 43 /// <summary> 44 /// 查询操作返回默认第一条数据(如返回null则创建默认类型) 45 /// </summary> 46 /// <typeparam name="T"></typeparam> 47 /// <param name="sql"></param> 48 /// <param name="param"></param> 49 /// <returns></returns> 50 public T FirstOrDefault<T>(string sql, object param) 51 { 52 var model = default(T); 53 if (!string.IsNullOrEmpty(sql)) 54 { 55 try 56 { 57 model = DbConnection.Query<T>(sql, param).FirstOrDefault(); 58 } 59 catch { } 60 } 61 return model == null ? Activator.CreateInstance<T>() : model; 62 } 63 64 65 66 /// <summary> 67 /// 查询一组SQL语句并返回值 68 /// </summary> 69 /// <typeparam name="T1">第一条语句返回集合类型</typeparam> 70 /// <typeparam name="T2">第二条语句返回集合类型</typeparam> 71 /// <param name="sql">sql语句</param> 72 /// <param name="param">参数化值</param> 73 /// <returns></returns> 74 public Tuple<IEnumerable<T1>, IEnumerable<T2>> Query<T1, T2>(string sql, object param) 75 { 76 IEnumerable<T1> _item1 = null; IEnumerable<T2> _item2 = null; 77 if (!string.IsNullOrEmpty(sql)) 78 { 79 try 80 { 81 using (var multi = DbConnection.QueryMultiple(sql, param)) 82 { 83 _item1 = multi.Read<T1>(); 84 _item2 = multi.Read<T2>(); 85 } 86 } 87 catch { } 88 } 89 return Tuple.Create<IEnumerable<T1>, IEnumerable<T2>>(_item1, _item2); 90 } 91 92 /// <summary> 93 /// 查询一组SQL语句并返回值 94 /// </summary> 95 /// <typeparam name="T1">第一条语句返回集合类型</typeparam> 96 /// <typeparam name="T2">第二条语句返回集合类型</typeparam> 97 /// <typeparam name="T3">第三条语句返回集合类型</typeparam> 98 /// <param name="sql">sql语句</param> 99 /// <param name="param">参数化值</param> 100 /// <returns></returns> 101 public Tuple<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>> Query<T1, T2, T3>(string sql, object param) 102 { 103 IEnumerable<T1> _item1 = null; IEnumerable<T2> _item2 = null; IEnumerable<T3> _item3 = null; 104 if (!string.IsNullOrEmpty(sql)) 105 { 106 try 107 { 108 using (var multi = DbConnection.QueryMultiple(sql, param)) 109 { 110 _item1 = multi.Read<T1>(); 111 _item2 = multi.Read<T2>(); 112 _item3 = multi.Read<T3>(); 113 } 114 } 115 catch { } 116 } 117 return Tuple.Create<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>>(_item1, _item2, _item3); 118 } 119 120 121 122 public void Dispose() 123 { 124 if (dbConnection != null) 125 { 126 try 127 { 128 bool isClosed = dbConnection.State == ConnectionState.Closed; 129 if (!isClosed) dbConnection.Close(); 130 //dbConnection.Dispose(); 131 } 132 catch { } 133 } 134 } 135 }
/// <summary> /// 批量插入数据 /// </summary> /// <param name="persons"></param> /// <returns>影响行数</returns> public static int Insert(List<User> users) { using (IDbConnection connection = new SqlConnection(connectionString)) { connection.Execute("insert into User(ID,Name) values(@ID,@Name)", users);
}
}
查询的In操作：
public static List<User> QueryIn(List<Id> Ids) { using (IDbConnection connection = new SqlConnection(connectionString)) { var sql = "select * from Person where id in @ids"; //参数类型是Array的时候，dappper会自动将其转化 return connection.Query<Person>(sql, new { ids = Ids, }).ToList(); } }
