C# 简化生成SQL语句帮助类,只要有“表名”和实体“对象”就能执行增、删、改、查:
简化生成SQL语句帮助类,负责自动生成sql语句,SqlMaker.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
namespace StuTests_DAL
{
/// <summary>
/// 制造SQL语句字符串的工厂
/// </summary>
public class SqlStrFactory
{
//public static string ConnStr = string.Empty;//链接字符串,链接不同的数据库,配置于Web.Config中...
#region 增
/// <summary>
/// 直接执行INSERT语句,返回添加条数
/// </summary>
/// <param name="sqlStr">INSERT语句</param>
/// <returns>添加条数</returns>
public static int INSERT(string sqlStr)
{
return ExecuteNonQuery(sqlStr);
}
public static int INSERT(string sqlStr, SqlParameter[] sqlparas)
{
return ExecuteNonQuery(sqlStr, sqlparas);
}
public static int ExecuteNonQuery(string sqlStr)
{
return DbHelperSQL.ExecuteNonQuery(sqlStr);
}
public static int ExecuteNonQuery(string sqlStr, SqlParameter[] sqlparas)
{
return DbHelperSQL.ExecuteNonQuery(sqlStr, sqlparas);
}
/// <summary>
/// 增加一条数据,返回添加条数
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="model">实体对象</param>
/// <param name="TableName">表名</param>
/// <param name="PrimaryKey">自增长主键(需跳过)</param>
/// <returns>添加条数</returns>
public static int INSERT<T>(string TableName, T model, string PrimaryKey = "")
{
List<SqlParameter> paralist = new List<SqlParameter>();//Parameter参数
StringBuilder strSql = new StringBuilder();//SQL语句
StringBuilder column = new StringBuilder();//列字段
StringBuilder columnValue = new StringBuilder();//列字段对应的值
strSql.AppendFormat("INSERT INTO {0}(", TableName);
foreach (PropertyInfo info in typeof(T).GetProperties())
{
PropertyInfo pi = typeof(T).GetProperty(info.Name);
if (pi.GetValue(model, null) == null)
continue;
if (info.Name.ToLower() != PrimaryKey.ToLower().Replace("[", "").Replace("]", ""))//跳过自增长主键
{
column.AppendFormat(",{0}", info.Name);
columnValue.AppendFormat(",@{0}", info.Name);//添加字段
}
SqlParameter para = new SqlParameter("@" + info.Name, pi.GetValue(model, null));
paralist.Add(para);
}
strSql.AppendFormat("{0}) VALUES ({1});", Regex.Replace(column.ToString(), "^,", ""), Regex.Replace(columnValue.ToString(), "^,", ""));
return DbHelperSQL.ExecuteNonQuery(strSql.ToString(), paralist.ToArray());
}
#endregion 增
#region 增(返回新增的主键)
/// <summary>
/// 直接执行INSERT语句,返回新ID
/// insert一条数据,返回自增的id值:insert into table([NAME])values ('名字');Select @@Identity;
/// </summary>
/// <param name="sqlStr">INSERT语句</param>
/// <returns>回新ID</returns>
public static int INSERT_NewId(string sqlStr)
{
return Convert.ToInt32(ExecuteScalar(sqlStr));
}
public static int INSERT_NewId(string sqlStr, SqlParameter[] sqlparas)
{
return Convert.ToInt32(ExecuteScalar(sqlStr, sqlparas));
}
/// <summary>
/// 增加一条数据,返回新的ID
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="model">实体对象</param>
/// <param name="TableName">表名</param>
/// <returns>新的ID</returns>
public static int INSERT_NewId<T>(string TableName, T model)
{
List<SqlParameter> paralist = new List<SqlParameter>();//Parameter参数
StringBuilder strSql = new StringBuilder();//SQL语句
StringBuilder column = new StringBuilder();//列字段
StringBuilder columnvalue = new StringBuilder();//列字段对应的值
strSql.AppendFormat("INSERT INTO {0}(", TableName);
foreach (PropertyInfo info in typeof(T).GetProperties())
{
PropertyInfo pi = typeof(T).GetProperty(info.Name);
if (pi.GetValue(model, null) == null)
continue;
column.AppendFormat(",{0}", info.Name);
columnvalue.AppendFormat(",@{0}", info.Name);//添加字段
SqlParameter para = new SqlParameter("@" + info.Name, pi.GetValue(model, null));
paralist.Add(para);
}
strSql.AppendFormat("{0}) VALUES ({1});SELECT @@Identity;", Regex.Replace(column.ToString(), "^,", ""), Regex.Replace(columnvalue.ToString(), "^,", ""));
var obj = DbHelperSQL.ExecuteScalar(strSql.ToString(), paralist.ToArray());
return int.Parse(obj.ToString());
}
/// <summary>
/// 执行查询,并返回查询所返回的结果集中第一行的第一列
/// </summary>
/// <param name="sqlStr"></param>
/// <returns></returns>
public static object ExecuteScalar(string sqlStr)
{
return DbHelperSQL.ExecuteScalar(sqlStr);
}
public static object ExecuteScalar(string sqlStr, SqlParameter[] sqlparas)
{
return DbHelperSQL.ExecuteScalar(sqlStr, sqlparas);
}
#endregion
#region 删
/// <summary>
/// 直接执行DELETE语句,返回删除条数
/// </summary>
/// <param name="sqlStr">DELETE语句</param>
/// <returns>删除条数</returns>
public static int DELETE(string sqlStr)
{
return ExecuteNonQuery(sqlStr);
}
public static int DELETE(string sqlStr, SqlParameter[] sqlparas)
{
return ExecuteNonQuery(sqlStr, sqlparas);
}
/// <summary>
/// 删除数据,返回删除条数
/// </summary>
/// <param name="TableName"></param>
/// <param name="strWhere"></param>
/// <returns>删除条数</returns>
public static int DELETE(string TableName, string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat("DELETE FROM {0}", TableName);
if (!string.IsNullOrEmpty(strWhere))
{
strSql.Append(" WHERE " + strWhere);
}
int rows = DbHelperSQL.ExecuteNonQuery(strSql.ToString());
return rows;
}
#endregion 删
#region 改
/// <summary>
/// 直接执行UPDATE语句,返回修改条数
/// </summary>
/// <param name="sqlStr">UPDATE语句</param>
/// <returns>修改条数</returns>
public static int UPDATE(string sqlStr)
{
return ExecuteNonQuery(sqlStr);
}
public static int UPDATE(string sqlStr, SqlParameter[] sqlparas)
{
return ExecuteNonQuery(sqlStr, sqlparas);
}
/// <summary>
/// 更新一条数据,返回修改条数
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="model">实体对象</param>
/// <param name="strWhere">条件</param>
/// <param name="TableName">表名</param>
/// <param name="TableName"></param>
/// <returns>修改条数</returns>
public static int UPDATE<T>(string TableName, string strWhere, T model)
{
try
{
List<SqlParameter> paralist = new List<SqlParameter>();//Parameter参数
StringBuilder strSql = new StringBuilder();
StringBuilder column = new StringBuilder();
strSql.AppendFormat("UPDATE {0} SET ", TableName);
foreach (PropertyInfo info in typeof(T).GetProperties())
{
PropertyInfo pi = typeof(T).GetProperty(info.Name);
if (pi.GetValue(model, null) == null) continue;
column.AppendFormat(",{0}=@{0}", info.Name);//添加字段
SqlParameter para = new SqlParameter("@" + info.Name, pi.GetValue(model, null));
para.Value = pi.GetValue(model, null);//设置SqlParameter对应Value
paralist.Add(para);
}
strSql.Append(Regex.Replace(column.ToString(), "^,", ""));//去掉第一个,
if (!string.IsNullOrEmpty(strWhere))
strSql.AppendFormat(" WHERE {0}", strWhere);
int rows = DbHelperSQL.ExecuteNonQuery(strSql.ToString(), paralist.ToArray());
return rows;
}
catch
{
return 0;
}
}
#endregion 改
#region 查
/// <summary>
/// 直接执行SELECT语句
/// </summary>
/// <param name="strSql">SELECT语句</param>
/// <returns>数据集</returns>
public static DataTable SELECT(string strSql)
{
return ExecuteReader(strSql);
}
public static DataTable SELECT(string strSql, SqlParameter[] sqlparas)
{
return ExecuteReader(strSql, sqlparas);
}
public static DataTable ExecuteReader(string strSql)
{
return DbHelperSQL.ExecuteReader(strSql);
}
public static DataTable ExecuteReader(string strSql, SqlParameter[] sqlparas)
{
return DbHelperSQL.ExecuteReader(strSql, sqlparas);
}
/// <summary>
/// 获得N行数据,带条件,带排序(单表查询)
/// </summary>
/// <param name="tableName">*表名</param>
/// <param name="Top">N条</param>
/// <param name="fldName">列</param>
/// <param name="strWhere">查询条件</param>
/// <param name="orderBy">排序字符串</param>
/// <returns>结果集合</returns>
public static DataTable SELECT(string tableName, int Top = 0, string fldName = "", string strWhere = "", string orderBy = "")
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT ");
if (Top > 0)
{
strSql.Append(" TOP " + Top);
}
if (!string.IsNullOrEmpty(fldName))
{
strSql.Append(fldName + " FROM " + tableName);
}
else
{
strSql.Append(" * FROM " + tableName);
}
if (!string.IsNullOrEmpty(strWhere))
{
strSql.Append(" WHERE " + strWhere);
}
if (!string.IsNullOrEmpty(orderBy))
{
strSql.Append(" ORDER BY " + orderBy);
}
DataTable dt = DbHelperSQL.ExecuteReader(strSql.ToString());
return dt;
}
/// <summary>
/// 聚合查询
/// </summary>
/// <param name="tableName">TABLE</param>
/// <param name="top">TOP</param>
/// <param name="fldName">*</param>
/// <param name="strWhere">WHERE</param>
/// <param name="orderBy">ORDER BY</param>
/// <param name="groupBy">GROUP BY</param>
/// <param name="having">HAVING</param>
/// <returns></returns>
public static DataTable GROUP_BY_HAVING(string tableName, int top = 0, string fldName = "", string strWhere = "", string orderBy = "", string groupBy = "", string having = "")
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT ");
if (top > 0)
{
strSql.Append(" TOP " + top);
}
if (!string.IsNullOrEmpty(fldName))
{
strSql.Append(fldName + " FROM " + tableName);
}
else
{
strSql.Append(" * FROM " + tableName);
}
if (!string.IsNullOrEmpty(strWhere))
{
strSql.Append(" WHERE " + strWhere);
}
if (!string.IsNullOrEmpty(groupBy))
{
strSql.Append(" GROUP BY " + groupBy);
}
if (!string.IsNullOrEmpty(having))
{
strSql.Append(" HAVING " + having);
}
if (!string.IsNullOrEmpty(orderBy))
{
strSql.Append(" ORDER BY " + orderBy);
}
DataTable dt = DbHelperSQL.ExecuteReader(strSql.ToString());
return dt;
}
/// <summary>
/// 获得分页数据(单表查询)
/// </summary>
/// <param name="tableName">*表名</param>
/// <param name="fldOrder">*排序字段</param>
/// <param name="fldName">显示字段</param>
/// <param name="strWhere">查询条件</param>
/// <param name="PageSize">每页条数</param>
/// <param name="PageIndex">页码</param>
/// <returns></returns>
public static DataTable GetPageData(string tableName, string fldOrder, string fldName = "", string strWhere = "", int PageSize = 10, int PageIndex = 1)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendLine("DECLARE @PageSize INT;");
strSql.AppendLine("DECLARE @PageIndex INT;");
strSql.AppendLine("SET @PageSize=" + PageSize + ";");
strSql.AppendLine("SET @PageIndex=" + PageIndex + ";");
strSql.Append("SELECT ");
if (!string.IsNullOrEmpty(fldName))
{
strSql.Append(fldName + " FROM (");
}
else
{
strSql.Append(" * FROM (");
}
strSql.AppendFormat("SELECT ROW_NUMBER() OVER(ORDER BY {0}) rownum,", fldOrder);
if (!string.IsNullOrEmpty(fldName))
{
strSql.Append(fldName + " FROM " + tableName);
}
else
{
strSql.Append(" * FROM " + tableName);
}
if (!string.IsNullOrEmpty(strWhere))
{
strSql.AppendLine(" WHERE " + strWhere);
}
strSql.AppendLine(")a");
strSql.AppendLine("WHERE rownum > @PageSize * (@PageIndex - 1) AND rownum <= @PageSize * @PageIndex;");
DataTable dt = DbHelperSQL.ExecuteReader(strSql.ToString());
return dt;
}
/// <summary>
/// 根据条件获取数量
/// </summary>
/// <param name="TableName"></param>
/// <param name="strWhere"></param>
/// <returns></returns>
public static int GetCount(string TableName, string strWhere = "")
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat("SELECT COUNT(*) FROM {0}", TableName);
if (!string.IsNullOrEmpty(strWhere))
{
strSql.AppendFormat(" WHERE {0}", strWhere);
}
object obj = GetSingle(strSql.ToString());
if (obj == null)
{
return 0;
}
else
{
return Convert.ToInt32(obj);
}
}
/// <summary>
/// 获取最大ID
/// </summary>
/// <param name="TableName">*表名</param>
/// <param name="columnname">*列名</param>
/// <param name="strWhere">条件</param>
/// <returns>最大值</returns>
public static int GetMaxID(string TableName, string columnname, string strWhere = "")
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat("SELECT MAX({0}) FROM {1}", columnname, TableName);
if (!string.IsNullOrEmpty(strWhere)) strSql.AppendFormat(" WHERE {0}", strWhere);
object obj = GetSingle(strSql.ToString());
try
{
if (obj == null)
{
return 0;
}
else
{
return Convert.ToInt32(obj);
}
}
catch
{
return 0;
}
}
/// <summary>
/// 获取新的ID(最大ID+1)
/// </summary>
/// <param name="TableName">*表名</param>
/// <param name="columnname">*列名</param>
/// <param name="strWhere">条件</param>
/// <returns></returns>
public static int GetNewID(string TableName, string columnname, string strWhere = "")
{
int MaxID = GetMaxID(TableName, columnname, strWhere);
return MaxID + 1;
}
/// <summary>
/// 是否存在该条件记录
/// </summary>
/// <param name="strWhere"></param>
/// <param name="TableName"></param>
/// <returns></returns>
public static bool IsExists(string TableName, string strWhere = "")
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat("SELECT COUNT(1) FROM {0}", TableName);
if (!string.IsNullOrEmpty(strWhere))
{
strSql.Append(" WHERE " + strWhere);
}
return Exists(strSql.ToString());
}
#endregion 查
#region 私有方法
private static bool Exists(string strSql)
{
object obj = GetSingle(strSql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
private static bool Exists(string strSql, params SqlParameter[] cmdParms)
{
object obj = GetSingle(strSql, cmdParms);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
public static object GetSingle(string strSql)
{
return DbHelperSQL.ExecuteScalar(strSql);
}
public static object GetSingle(string strSql, params SqlParameter[] cmdParms)
{
return DbHelperSQL.ExecuteScalar(strSql, cmdParms);
}
#endregion
#region 公用方法
/// <summary>
/// 判断是否存在某表的某个字段
/// </summary>
/// <param name="columnName">列名称</param>
/// <param name="TableName">表名称</param>
/// <returns>是否存在</returns>
public static bool ColumnExists(string columnName, string tableName)
{
string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
object res = GetSingle(sql);
if (res == null)
{
return false;
}
return Convert.ToInt32(res) > 0;
}
/// <summary>
/// 表是否存在
/// </summary>
/// <param name="TableName"></param>
/// <returns></returns>
public static bool TabExists(string tableName)
{
string strsql = "select count(*) from sysobjects where id = object_id(N'[" + tableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
//string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
object obj = GetSingle(strsql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
#endregion
}
}
SqlHelper类,负责执行生成的sql语句,DbHelperSQL.cs
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace SqlHelper
{
/// <summary>
/// SqlHelper类
/// </summary>
public class DbHelperSQL
{
public static readonly string ConnStr = ConfigurationManager.ConnectionStrings["connectionStr"].ConnectionString;
private static SqlCommand cmd = null;
private static SqlConnection conn = new SqlConnection(ConnStr);
private static SqlDataReader sdr = null;
private static SqlConnection Getconn()
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
return conn;
}
/// <summary>
/// 增|删|改 INSERT|DELETE|UPDATE
/// </summary>
public static int ExecuteNonQuery(String sqlStr)
{
int res;
cmd = new SqlCommand(sqlStr, Getconn());
res = cmd.ExecuteNonQuery();
return res;
}
/// <summary>
/// 增|删|改 INSERT|DELETE|UPDATE
/// </summary>
public static int ExecuteNonQuery(string sqlStr, SqlParameter[] sqlparas)
{
int res;
cmd = new SqlCommand(sqlStr, Getconn());
cmd.Parameters.AddRange(sqlparas);
res = cmd.ExecuteNonQuery();
conn.Close();
return res;
}
/// <summary>
/// 查 SELECT
/// </summary>
public static DataTable ExecuteReader(string sqlStr)
{
DataTable dt = new DataTable();
try
{
cmd = new SqlCommand(sqlStr, Getconn());
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
}
catch { }
return dt;
}
/// <summary>
/// 查 SELECT
/// </summary>
public static DataTable ExecuteReader(string sqlStr, SqlParameter[] sqlparas)
{
DataTable dt = new DataTable();
cmd = new SqlCommand(sqlStr, Getconn());
cmd.Parameters.AddRange(sqlparas);
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
return dt;
}
/// <summary>
/// 执行语句,并返回查询所返回的结果集中第一行的第一列,
/// 如:insert一条数据,返回自增的id值:insert into table([NAME])values ('名字');Select @@Identity;
/// </summary>
public static object ExecuteScalar(string sqlStr)
{
object res;
cmd = new SqlCommand(sqlStr, Getconn());
res = cmd.ExecuteScalar();
conn.Close();
return res;
}
/// <summary>
/// 执行语句,并返回查询所返回的结果集中第一行的第一列,
/// 如:insert一条数据,返回自增的id值:insert into table([NAME])values ('名字');Select @@Identity;
/// </summary>
public static object ExecuteScalar(string sqlStr, SqlParameter[] sqlparas)
{
object res;
cmd = new SqlCommand(sqlStr, Getconn());
cmd.Parameters.AddRange(sqlparas);
res = cmd.ExecuteScalar();
conn.Close();
return res;
}
}
}
本文配套源码DEMO下载地址:https://download.csdn.net/download/djk8888/11686101