(更新时间)2021年5月19日 仓库温控系统(Winform) 07 动态生成sql语句CreateSql
【摘要】
//生成sql语句的通用类
public class CreateSql
{
/// <summary>
/// 生成Insert语句
/// ...
//生成sql语句的通用类
public class CreateSql
{
/// <summary>
/// 生成Insert语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <param name="cols">要插入的必要列</param>
/// <returns></returns>
public static SqlModel GetInsertSqlAndParas<T>(T t, string cols, int isReturn)
{
Type type = typeof(T);
PropertyInfo[] properties = PropertyHelper.GetTypeProperties<T>(cols);
string priName = type.GetPrimary();//获取主键名 标识列（不需要显式插入）
//生成要插入的列 {1} insert into table (Id,Name,Age....) values (@Id,@Name,@Age)
string columns = string.Join(",", properties.Where(p => p.Name != priName).Select(p => $"[{p.GetColName()}]"));
//生成插入的参数{2}
string paraColumns = string.Join(",", properties.Where(p => p.Name != priName).Select(p => $"@{p.GetColName()}"));
//参数数组的生成
SqlParameter[] arrParas = CreateParameters<T>(properties, t);
//sql语句
string sql = $"INSERT INTO [{type.GetTName()}] ({columns}) VALUES ({paraColumns}) ";
if (isReturn == 1)
sql += ";select @@identity";
return new SqlModel() { Sql = sql, SqlParaArray = arrParas };
}
/// <summary>
/// 生成Update语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <param name="cols">包括主键名</param>
/// <returns></returns>
public static SqlModel GetUpdateSqlAndParas<T>(T t, string cols, string strWhere)
{
Type type = typeof(T);
//获取所有属性
PropertyInfo[] properties = PropertyHelper.GetTypeProperties<T>(cols);
string priName = type.GetPrimary();
//生成要更新的列 {1} update 表名 set col1=@col1,col2=@col2,..... where 条件
string columns = string.Join(",", properties.Where(p => p.Name != priName).Select(p => string.Format("[{0}]=@{0}", p.GetColName())));
; //参数数组的生成
SqlParameter[] arrParas = CreateParameters<T>(properties, t);
if (string.IsNullOrEmpty(strWhere))
{
strWhere = $"{priName}=@{priName}";
}
//sql语句
string sql = $"UPDATE [{type.GetTName()}] SET {columns} WHERE {strWhere}";
return new SqlModel() { Sql = sql, SqlParaArray = arrParas };
}
/// <summary>
/// 生成Delete语句 第一个条件前不要加and
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="strWhere"></param>
/// <returns></returns>
public static string CreateDeleteSql<T>(string strWhere)
{
Type type = typeof(T);
string sql = $"DELETE FROM [{type.GetTName()}] WHERE ";
if (!string.IsNullOrEmpty(strWhere))
sql += strWhere;
else
sql += "1=1";
return sql;
}
/// <summary>
/// 生成假删除语句 update
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="strWhere"></param>
/// <param name="isDelete"></param>
/// <returns></returns>
public static string CreateLogicDeleteSql<T>(string strWhere, int isDelete)
{
Type type = typeof(T);
string sql = $"Update [{type.GetTName()}] set IsDeleted={isDelete} WHERE 1=1";
if (!string.IsNullOrEmpty(strWhere))
sql += " and " + strWhere;
return sql;
}
/// <summary>
///生成查询语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="strWhere"></param>
/// <param name="listCols"></param>
/// <returns></returns>
public static string CreateSelectSql<T>(string strWhere, string cols)
{
Type type = typeof(T);
PropertyInfo[] properties = PropertyHelper.GetTypeProperties<T>(cols);
if (string.IsNullOrEmpty(cols))
cols = "*";
if (string.IsNullOrEmpty(strWhere)) strWhere = "1=1";
string sql = $"SELECT {cols} FROM [{type.GetTName()}] WHERE {strWhere}";
return sql;
}
/// <summary>
/// 获取带自编号的select语句，主要用于分页查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="strWhere"></param>
/// <param name="cols"></param>
/// <returns></returns>
public static string CreateRowsSelectSql<T>(string strWhere, string cols)
{
Type type = typeof(T);
PropertyInfo[] properties = PropertyHelper.GetTypeProperties<T>(cols);
string columns = string.Join(",", properties.Select(p => $"[{p.GetColName()}]"));
string priName = type.GetPrimary();//获取主键名
if (string.IsNullOrEmpty(strWhere)) strWhere = "1=1";
string sql = $"SELECT ROW_NUMBER() OVER ( ORDER BY {priName} ASC ) AS Id,{ columns} FROM [{type.GetTName()}] WHERE {strWhere}";
return sql;
}
/// <summary>
/// 获取列名字符串（指定不包含的列）
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="notHaveCols"></param>
/// <returns></returns>
public static string GetColsString<T>(string notHaveCols)
{
List<string> cols = typeof(T).GetProperties().Select(p => p.GetColName()).ToList();
List<string> notCols = new List<string>();
if (!string.IsNullOrEmpty(notHaveCols))
{
notCols = notHaveCols.GetStrList(',', true);
cols = cols.Where(c => !notCols.Contains(c.ToLower())).ToList();
}
string colsStr = string.Join(",", cols);
return colsStr;
}
//生成参数数组
private static SqlParameter[] CreateParameters<T>(PropertyInfo[] properties, T t)
{
SqlParameter[] arrParas = properties.Select(p => new SqlParameter("@" + p.GetColName(), p.GetValue(t) ?? DBNull.Value)).ToArray();
return arrParas;
}
}
- 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
- 152
- 153
- 154
- 155
- 156
- 157
- 158
文章来源: codeboy.blog.csdn.net，作者：愚公搬代码，版权归原作者所有，如需转载，请联系作者。
原文链接：codeboy.blog.csdn.net/article/details/117047647
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）