C#SQL文の生成を簡略化するヘルプクラスは、「テーブル名」とエンティティ「オブジェクト」があれば、追加、削除、変更、検索を実行できます.
21193 ワード
C#SQL文の生成を簡略化するヘルプクラスは、「テーブル名」とエンティティ「オブジェクト」があれば、追加、削除、変更、検索を実行できます.
SQL文の生成を簡略化するヘルプクラスは、sql文、SqlMakerを自動的に生成する責任を負う.cs
SqlHelperクラスは、生成されたsql文、DbHelperSQLの実行を担当する.cs
本文のセットのソースコードDEMOダウンロードアドレス:https://download.csdn.net/download/djk8888/11686101
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 SqlHelper
{
///
/// SQL
///
public class SqlMaker
{
#region
///
/// INSERT ,
///
/// INSERT
///
public int INSERT(string sqlStr)
{
return DbHelperSQL.ExecuteNonQuery(sqlStr);
}
public int INSERT(string sqlStr, SqlParameter[] sqlparas)
{
return DbHelperSQL.ExecuteNonQuery(sqlStr, sqlparas);
}
///
/// INSERT ， ID
/// insert ， id ：insert into table([NAME])values (' ');Select @@Identity;
///
/// INSERT
/// ID
public int INSERT_Return_NewId(string sqlStr)
{
return int.Parse(DbHelperSQL.ExecuteScalar(sqlStr).ToString());
}
public int INSERT_Return_NewId(string sqlStr, SqlParameter[] sqlparas)
{
return int.Parse(DbHelperSQL.ExecuteScalar(sqlStr, sqlparas).ToString());
}
///
/// ,
///
///
///
///
///
public virtual int Add(string TableName, T model)
{
List paralist = new List();//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});", Regex.Replace(column.ToString(), "^,", ""), Regex.Replace(columnvalue.ToString(), "^,", ""));
return DbHelperSQL.ExecuteNonQuery(strSql.ToString(), paralist.ToArray());
}
///
/// , ID
///
///
///
///
/// ID
public virtual int Add_Return_NewId(string TableName, T model)
{
List paralist = new List();//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());
}
#endregion
#region
///
/// DELETE ,
///
/// DELETE
///
public int DELETE(string sqlStr)
{
return DbHelperSQL.ExecuteNonQuery(sqlStr);
}
public int DELETE(string sqlStr, SqlParameter[] sqlparas)
{
return DbHelperSQL.ExecuteNonQuery(sqlStr, sqlparas);
}
///
/// ,
///
///
///
///
public virtual 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
///
/// UPDATE ，
///
/// UPDATE
///
public int UPDATE(string sqlStr)
{
return DbHelperSQL.ExecuteNonQuery(sqlStr);
}
public int UPDATE(string sqlStr, SqlParameter[] sqlparas)
{
return DbHelperSQL.ExecuteNonQuery(sqlStr, sqlparas);
}
///
/// ，
///
///
///
///
///
///
///
public virtual int Update(string TableName, string strWhere, T model)
{
try
{
List paralist = new List();//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
///
/// SELECT
///
/// SELECT
///
public DataTable SELECT(string strSql)
{
return DbHelperSQL.ExecuteReader(strSql);
}
public DataTable SELECT(string strSql, SqlParameter[] sqlparas)
{
return DbHelperSQL.ExecuteReader(strSql, sqlparas);
}
///
/// N , , ( )
///
/// *
/// N
///
///
///
///
public virtual DataTable GetData(string TableName, int Top = 0, string fldName = "", string strWhere = "", string fldOrder = "")
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT ");
if (Top > 0)
{
strSql.AppendFormat(" TOP {0} ", Top);
}
if (!string.IsNullOrEmpty(fldName))
{
strSql.AppendFormat(fldName + " FROM {0}", TableName);
}
else
{
strSql.AppendFormat(" * FROM {0}", TableName);
}
if (!string.IsNullOrEmpty(strWhere))
{
strSql.Append(" WHERE " + strWhere);
}
if (!string.IsNullOrEmpty(fldOrder))
{
strSql.Append(" ORDER BY " + fldOrder);
}
DataTable dt = DbHelperSQL.ExecuteReader(strSql.ToString());
return dt;
}
///
/// ( )
///
/// *
/// *
///
///
///
///
///
public virtual 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.AppendFormat(fldName + " FROM {0}", TableName);
}
else
{
strSql.AppendFormat(" * FROM {0}", 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;
}
///
///
///
///
///
///
public virtual 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);
}
}
///
/// ID
///
/// *
/// *
///
///
public virtual 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;
}
}
///
/// ID( ID+1)
///
/// *
/// *
///
///
public virtual int GetNewID(string TableName, string columnname, string strWhere = "")
{
int MaxID = GetMaxID(TableName, columnname, strWhere);
return MaxID + 1;
}
///
///
///
///
///
///
public virtual 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
///
///
///
///
///
///
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;
}
///
///
///
///
///
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
{
///
/// SqlHelper
///
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;
}
///
/// | | INSERT|DELETE|UPDATE
///
public static int ExecuteNonQuery(String sqlStr)
{
int res;
cmd = new SqlCommand(sqlStr, Getconn());
res = cmd.ExecuteNonQuery();
return res;
}
///
/// | | INSERT|DELETE|UPDATE
///
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;
}
///
/// SELECT
///
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;
}
///
/// SELECT
///
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;
}
///
/// ， ，
/// ：insert ， id ：insert into table([NAME])values (' ');Select @@Identity;
///
public static object ExecuteScalar(string sqlStr)
{
object res;
cmd = new SqlCommand(sqlStr, Getconn());
res = cmd.ExecuteScalar();
conn.Close();
return res;
}
///
/// ， ，
/// ：insert ， id ：insert into table([NAME])values (' ');Select @@Identity;
///
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