C#/ASP.NET完善的DBHelper，配套Model生成器，
支持Oracle、MSSQL、MySQL、SQLite四種數據庫，支持事務，支持對象關系映射；已在多個項目中實際使用。
沒有語法糖，學習成本幾乎為0，拿來即用。
DBHelper類完整代碼：
![]()
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.Objects.DataClasses;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using Models;
using MySql.Data.MySqlClient;
/* ---------------------------------------------
* 作 者：suxiang
* 創建日期：2016年11月23日
* --------------------------------------------- */
namespace DBUtil
{
/// <summary>
/// 數據庫操作類
/// </summary>
public static class DBHelper
{
#region 變量
/// <summary>
/// 數據庫類型
/// </summary>
private static string m_DBType = ConfigurationManager.AppSettings["DBType"];
/// <summary>
/// 數據庫類型
/// </summary>
private static bool m_AutoIncrement = ConfigurationManager.AppSettings["AutoIncrement"].ToLower() == "true" ? true : false;
/// <summary>
/// 數據庫連接字符串
/// </summary>
private static string m_ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
/// <summary>
/// 事務
/// </summary>
[ThreadStatic]
private static DbTransaction m_Tran;
/// <summary>
/// 帶參數的SQL插入和修改語句中，參數前面的符號
/// </summary>
private static string m_ParameterMark = GetParameterMark();
#endregion
#region 生成變量
#region 生成 IDbCommand
/// <summary>
/// 生成 IDbCommand
/// </summary>
private static DbCommand GetCommand()
{
DbCommand command = null;
switch (m_DBType)
{
case "oracle":
command = new OracleCommand();
break;
case "mssql":
command = new SqlCommand();
break;
case "mysql":
command = new MySqlCommand();
break;
case "sqlite":
command = new SQLiteCommand();
break;
}
return command;
}
/// <summary>
/// 生成 IDbCommand
/// </summary>
private static DbCommand GetCommand(string sql, DbConnection conn)
{
DbCommand command = null;
switch (m_DBType)
{
case "oracle":
command = new OracleCommand(sql);
command.Connection = conn;
break;
case "mssql":
command = new SqlCommand(sql);
command.Connection = conn;
break;
case "mysql":
command = new MySqlCommand(sql);
command.Connection = conn;
break;
case "sqlite":
command = new SQLiteCommand(sql);
command.Connection = conn;
break;
}
return command;
}
#endregion
#region 生成 IDbConnection
/// <summary>
/// 生成 IDbConnection
/// </summary>
private static DbConnection GetConnection()
{
DbConnection conn = null;
switch (m_DBType)
{
case "oracle":
conn = new OracleConnection(m_ConnectionString);
break;
case "mssql":
conn = new SqlConnection(m_ConnectionString);
break;
case "mysql":
conn = new MySqlConnection(m_ConnectionString);
break;
case "sqlite":
conn = new SQLiteConnection(m_ConnectionString);
break;
}
return conn;
}
#endregion
#region 生成 IDbDataAdapter
/// <summary>
/// 生成 IDbDataAdapter
/// </summary>
private static DbDataAdapter GetDataAdapter(DbCommand cmd)
{
DbDataAdapter dataAdapter = null;
switch (m_DBType)
{
case "oracle":
dataAdapter = new OracleDataAdapter();
dataAdapter.SelectCommand = cmd;
break;
case "mssql":
dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = cmd;
break;
case "mysql":
dataAdapter = new MySqlDataAdapter();
dataAdapter.SelectCommand = cmd;
break;
case "sqlite":
dataAdapter = new SQLiteDataAdapter();
dataAdapter.SelectCommand = cmd;
break;
}
return dataAdapter;
}
#endregion
#region 生成 m_ParameterMark
/// <summary>
/// 生成 m_ParameterMark
/// </summary>
private static string GetParameterMark()
{
switch (m_DBType)
{
case "oracle":
return ":";
case "mssql":
return "@";
case "mysql":
return "@";
case "sqlite":
return ":";
}
return ":";
}
#endregion
#region 生成 DbParameter
/// <summary>
/// 生成 DbParameter
/// </summary>
private static DbParameter GetDbParameter(string name, object vallue)
{
DbParameter dbParameter = null;
switch (m_DBType)
{
case "oracle":
dbParameter = new OracleParameter(name, vallue);
break;
case "mssql":
dbParameter = new SqlParameter(name, vallue);
break;
case "mysql":
dbParameter = new MySqlParameter(name, vallue);
break;
case "sqlite":
dbParameter = new SQLiteParameter(name, vallue);
break;
}
return dbParameter;
}
#endregion
#endregion
#region 基礎方法
#region 執行簡單SQL語句
#region Exists
public static bool Exists(string sqlString)
{
SqlFilter(ref sqlString);
using (DbConnection conn = GetConnection())
{
using (DbCommand cmd = GetCommand(sqlString, conn))
{
try
{
conn.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return false;
}
else
{
return true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
conn.Close();
}
}
}
}
#endregion
#region 執行SQL語句，返回影響的記錄數
/// <summary>
/// 執行SQL語句，返回影響的記錄數
/// </summary>
/// <param name="sqlString">SQL語句</param>
/// <returns>影響的記錄數</returns>
public static int ExecuteSql(string sqlString)
{
SqlFilter(ref sqlString);
DbConnection conn = m_Tran == null ? GetConnection() : m_Tran.Connection;
using (DbCommand cmd = GetCommand(sqlString, conn))
{
try
{
if (conn.State != ConnectionState.Open) conn.Open();
if (m_Tran != null) cmd.Transaction = m_Tran;
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose();
if (m_Tran == null) conn.Close();
}
}
}
#endregion
#region 執行一條計算查詢結果語句，返回查詢結果
/// <summary>
/// 執行一條計算查詢結果語句，返回查詢結果（object）
/// </summary>
/// <param name="sqlString">計算查詢結果語句</param>
/// <returns>查詢結果（object）</returns>
public static object GetSingle(string sqlString)
{
SqlFilter(ref sqlString);
using (DbConnection conn = GetConnection())
{
using (DbCommand cmd = GetCommand(sqlString, conn))
{
try
{
if (conn.State != ConnectionState.Open) conn.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
}
}
}
}
#endregion
#region 執行查詢語句，返回IDataReader
/// <summary>
/// 執行查詢語句，返回IDataReader ( 注意：調用該方法後，一定要對IDataReader進行Close )
/// </summary>
/// <param name="sqlString">查詢語句</param>
/// <returns>IDataReader</returns>
public static DbDataReader ExecuteReader(string sqlString)
{
SqlFilter(ref sqlString);
DbConnection conn = GetConnection();
DbCommand cmd = GetCommand(sqlString, conn);
try
{
if (conn.State != ConnectionState.Open) conn.Open();
DbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return myReader;
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region 執行查詢語句，返回DataSet
/// <summary>
/// 執行查詢語句，返回DataSet
/// </summary>
/// <param name="sqlString">查詢語句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string sqlString)
{
SqlFilter(ref sqlString);
using (DbConnection conn = GetConnection())
{
DataSet ds = new DataSet();
try
{
conn.Open();
using (DbCommand cmd = GetCommand(sqlString, conn))
{
DbDataAdapter adapter = GetDataAdapter(cmd);
adapter.Fill(ds, "ds");
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return ds;
}
}
#endregion
#region SQL過濾，防注入
/// <summary>
/// SQL過濾，防注入
/// </summary>
/// <param name="sql">sql</param>
public static void SqlFilter(ref string sql)
{
List<string> keywordList = new List<string>() {
"net localgroup",
"net user",
"xp_cmdshell",
"exec",
"execute",
"truncate",
"drop",
"restore",
"create",
"alter",
"rename",
"insert",
"update",
"delete",
"select"};
string ignore = string.Empty;
string upperSql = sql.ToUpper().Trim();
foreach (string keyword in keywordList)
{
if (upperSql.IndexOf(keyword.ToUpper()) == 0)
{
ignore = keyword;
}
}
foreach (string keyword in keywordList)
{
if (ignore.ToUpper() == keyword.ToUpper()) continue;
Regex regex = new Regex(keyword, RegexOptions.IgnoreCase);
sql = regex.Replace(sql, string.Empty);
}
}
#endregion
#endregion
#region 執行帶參數的SQL語句
#region 執行SQL語句，返回影響的記錄數
/// <summary>
/// 執行SQL語句，返回影響的記錄數
/// </summary>
/// <param name="SQLString">SQL語句</param>
/// <returns>影響的記錄數</returns>
public static int ExecuteSql(string SQLString, params DbParameter[] cmdParms)
{
DbConnection conn = m_Tran == null ? GetConnection() : m_Tran.Connection;
using (DbCommand cmd = GetCommand())
{
try
{
PrepareCommand(cmd, conn, m_Tran, SQLString, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
if (m_Tran == null) conn.Close();
}
}
}
#endregion
#region 執行查詢語句，返回IDataReader
/// <summary>
/// 執行查詢語句，返回IDataReader ( 注意：調用該方法後，一定要對IDataReader進行Close )
/// </summary>
/// <param name="strSQL">查詢語句</param>
/// <returns>IDataReader</returns>
public static DbDataReader ExecuteReader(string sqlString, params DbParameter[] cmdParms)
{
DbConnection conn = GetConnection();
DbCommand cmd = GetCommand();
try
{
PrepareCommand(cmd, conn, null, sqlString, cmdParms);
DbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return myReader;
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region 執行查詢語句，返回DataSet
/// <summary>
/// 執行查詢語句，返回DataSet
/// </summary>
/// <param name="sqlString">查詢語句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string sqlString, params DbParameter[] cmdParms)
{
DbConnection conn = GetConnection();
DbCommand cmd = GetCommand();
PrepareCommand(cmd, conn, null, sqlString, cmdParms);
using (DbDataAdapter da = GetDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
conn.Close();
}
return ds;
}
}
#endregion
#region PrepareCommand
private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, string cmdText, DbParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open) conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null) cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
if (cmdParms != null)
{
foreach (DbParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
}
#endregion
#endregion
#endregion
#region 增刪改查
#region 獲取最大編號
/// <summary>
/// 獲取最大編號
/// </summary>
/// <typeparam name="T">實體Model</typeparam>
/// <param name="key">主鍵</param>
public static int GetMaxID<T>(string key)
{
Type type = typeof(T);
string sql = null;
switch (m_DBType)
{
case "oracle":
sql = string.Format("SELECT Max({0}) FROM {1}", key, type.Name);
break;
case "mssql":
sql = string.Format("SELECT Max({0}) FROM {1}", key, type.Name);
break;
case "mysql":
sql = string.Format("SELECT Max({0}) FROM {1}", key, type.Name);
break;
case "sqlite":
sql = string.Format("SELECT Max(cast({0} as int)) FROM {1}", key, type.Name);
break;
}
using (DbConnection conn = GetConnection())
{
using (IDbCommand cmd = GetCommand(sql, conn))
{
try
{
conn.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return 1;
}
else
{
return int.Parse(obj.ToString()) + 1;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
conn.Close();
}
}
}
}
#endregion
#region 添加
/// <summary>
/// 添加
/// </summary>
public static void Insert(object obj)
{
Insert(obj, m_AutoIncrement);
}
/// <summary>
/// 添加
/// </summary>
public static void Insert(object obj, bool autoIncrement)
{
StringBuilder strSql = new StringBuilder();
Type type = obj.GetType();
strSql.Append(string.Format("insert into {0}(", type.Name));
PropertyInfo[] propertyInfoList = GetEntityProperties(type);
List<string> propertyNameList = new List<string>();
int savedCount = 0;
foreach (PropertyInfo propertyInfo in propertyInfoList)
{
if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0 && autoIncrement) return;
if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0)
{
propertyNameList.Add(propertyInfo.Name);
savedCount++;
}
}
strSql.Append(string.Format("{0})", string.Join(",", propertyNameList.ToArray())));
strSql.Append(string.Format(" values ({0})", string.Join(",", propertyNameList.ConvertAll<string>(a => m_ParameterMark + a).ToArray())));
DbParameter[] parameters = new DbParameter[savedCount];
int k = 0;
for (int i = 0; i < propertyInfoList.Length && savedCount > 0; i++)
{
PropertyInfo propertyInfo = propertyInfoList[i];
if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0 && autoIncrement) return;
if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0)
{
object val = propertyInfo.GetValue(obj, null);
DbParameter param = GetDbParameter(m_ParameterMark + propertyInfo.Name, val == null ? DBNull.Value : val);
parameters[k++] = param;
}
}
ExecuteSql(strSql.ToString(), parameters);
}
#endregion
#region 修改
/// <summary>
/// 修改
/// </summary>
public static void Update(object obj)
{
object oldObj = Find(obj, false);
if (oldObj == null) throw new Exception("無法獲取到舊數據");
StringBuilder strSql = new StringBuilder();
Type type = obj.GetType();
strSql.Append(string.Format("update {0} ", type.Name));
PropertyInfo[] propertyInfoList = GetEntityProperties(type);
List<string> propertyNameList = new List<string>();
int savedCount = 0;
foreach (PropertyInfo propertyInfo in propertyInfoList)
{
if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0)
{
object oldVal = propertyInfo.GetValue(oldObj, null);
object val = propertyInfo.GetValue(obj, null);
if (!object.Equals(oldVal, val))
{
propertyNameList.Add(propertyInfo.Name);
savedCount++;
}
}
}
strSql.Append(string.Format(" set "));
DbParameter[] parameters = new DbParameter[savedCount];
StringBuilder sbPros = new StringBuilder();
int k = 0;
for (int i = 0; i < propertyInfoList.Length && savedCount > 0; i++)
{
PropertyInfo propertyInfo = propertyInfoList[i];
if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0)
{
object oldVal = propertyInfo.GetValue(oldObj, null);
object val = propertyInfo.GetValue(obj, null);
if (!object.Equals(oldVal, val))
{
sbPros.Append(string.Format(" {0}={1}{0},", propertyInfo.Name, m_ParameterMark));
DbParameter param = GetDbParameter(m_ParameterMark + propertyInfo.Name, val == null ? DBNull.Value : val);
parameters[k++] = param;
}
}
}
if (sbPros.Length > 0)
{
strSql.Append(sbPros.ToString(0, sbPros.Length - 1));
}
strSql.Append(string.Format(" where {0}='{1}'", GetIdName(obj.GetType()), GetIdVal(obj).ToString()));
if (savedCount > 0)
{
ExecuteSql(strSql.ToString(), parameters);
}
}
#endregion
#region 刪除
/// <summary>
/// 根據Id刪除
/// </summary>
public static void Delete<T>(int id)
{
Type type = typeof(T);
StringBuilder sbSql = new StringBuilder();
DbParameter[] cmdParms = new DbParameter[1];
cmdParms[0] = GetDbParameter(m_ParameterMark + GetIdName(type), id);
sbSql.Append(string.Format("delete from {0} where {2}={1}{2}", type.Name, m_ParameterMark, GetIdName(type)));
ExecuteSql(sbSql.ToString(), cmdParms);
}
/// <summary>
/// 根據Id集合刪除
/// </summary>
public static void BatchDelete<T>(string ids)
{
if (string.IsNullOrWhiteSpace(ids)) return;
Type type = typeof(T);
StringBuilder sbSql = new StringBuilder();
string[] idArr = ids.Split(',');
DbParameter[] cmdParms = new DbParameter[idArr.Length];
sbSql.AppendFormat("delete from {0} where {1} in (", type.Name, GetIdName(type));
for (int i = 0; i < idArr.Length; i++)
{
cmdParms[i] = GetDbParameter(m_ParameterMark + GetIdName(type) + i, idArr[i]);
sbSql.AppendFormat("{1}{2}{3},", type.Name, m_ParameterMark, GetIdName(type), i);
}
sbSql.Remove(sbSql.Length - 1, 1);
sbSql.Append(")");
ExecuteSql(sbSql.ToString(), cmdParms);
}
/// <summary>
/// 根據條件刪除
/// </summary>
public static void Delete<T>(string conditions)
{
if (string.IsNullOrWhiteSpace(conditions)) return;
Type type = typeof(T);
StringBuilder sbSql = new StringBuilder();
SqlFilter(ref conditions);
sbSql.Append(string.Format("delete from {0} where {1}", type.Name, conditions));
ExecuteSql(sbSql.ToString());
}
#endregion
#region 獲取實體
#region 根據實體獲取實體
/// <summary>
/// 根據實體獲取實體
/// </summary>
private static object Find(object obj, bool readCache = true)
{
Type type = obj.GetType();
object result = Activator.CreateInstance(type);
bool hasValue = false;
IDataReader rd = null;
string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, GetIdVal(obj), GetIdName(obj.GetType()));
try
{
rd = ExecuteReader(sql);
PropertyInfo[] propertyInfoList = GetEntityProperties(type);
int fcnt = rd.FieldCount;
List<string> fileds = new List<string>();
for (int i = 0; i < fcnt; i++)
{
fileds.Add(rd.GetName(i).ToUpper());
}
while (rd.Read())
{
hasValue = true;
IDataRecord record = rd;
foreach (PropertyInfo pro in propertyInfoList)
{
if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
{
continue;
}
pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (rd != null && !rd.IsClosed)
{
rd.Close();
rd.Dispose();
}
}
if (hasValue)
{
return result;
}
else
{
return null;
}
}
#endregion
#region 根據Id獲取實體
/// <summary>
/// 根據Id獲取實體
/// </summary>
private static object FindById(Type type, int id)
{
object result = Activator.CreateInstance(type);
IDataReader rd = null;
bool hasValue = false;
string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));
try
{
rd = ExecuteReader(sql);
PropertyInfo[] propertyInfoList = GetEntityProperties(type);
int fcnt = rd.FieldCount;
List<string> fileds = new List<string>();
for (int i = 0; i < fcnt; i++)
{
fileds.Add(rd.GetName(i).ToUpper());
}
while (rd.Read())
{
hasValue = true;
IDataRecord record = rd;
foreach (PropertyInfo pro in propertyInfoList)
{
if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
{
continue;
}
pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (rd != null && !rd.IsClosed)
{
rd.Close();
rd.Dispose();
}
}
if (hasValue)
{
return result;
}
else
{
return null;
}
}
#endregion
#region 根據Id獲取實體
/// <summary>
/// 根據Id獲取實體
/// </summary>
public static T FindById<T>(string id) where T : new()
{
Type type = typeof(T);
T result = (T)Activator.CreateInstance(type);
IDataReader rd = null;
bool hasValue = false;
string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));
try
{
rd = ExecuteReader(sql);
PropertyInfo[] propertyInfoList = GetEntityProperties(type);
int fcnt = rd.FieldCount;
List<string> fileds = new List<string>();
for (int i = 0; i < fcnt; i++)
{
fileds.Add(rd.GetName(i).ToUpper());
}
while (rd.Read())
{
hasValue = true;
IDataRecord record = rd;
foreach (PropertyInfo pro in propertyInfoList)
{
if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
{
continue;
}
pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (rd != null && !rd.IsClosed)
{
rd.Close();
rd.Dispose();
}
}
if (hasValue)
{
return result;
}
else
{
return default(T);
}
}
#endregion
#region 根據sql獲取實體
/// <summary>
/// 根據sql獲取實體
/// </summary>
public static T FindBySql<T>(string sql) where T : new()
{
Type type = typeof(T);
T result = (T)Activator.CreateInstance(type);
IDataReader rd = null;
bool hasValue = false;
try
{
rd = ExecuteReader(sql);
PropertyInfo[] propertyInfoList = GetEntityProperties(type);
int fcnt = rd.FieldCount;
List<string> fileds = new List<string>();
for (int i = 0; i < fcnt; i++)
{
fileds.Add(rd.GetName(i).ToUpper());
}
while (rd.Read())
{
hasValue = true;
IDataRecord record = rd;
foreach (PropertyInfo pro in propertyInfoList)
{
if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
{
continue;
}
pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (rd != null && !rd.IsClosed)
{
rd.Close();
rd.Dispose();
}
}
if (hasValue)
{
return result;
}
else
{
return default(T);
}
}
#endregion
#endregion
#region 獲取列表
/// <summary>
/// 獲取列表
/// </summary>
public static List<T> FindListBySql<T>(string sql) where T : new()
{
List<T> list = new List<T>();
object obj;
IDataReader rd = null;
try
{
rd = ExecuteReader(sql);
if (typeof(T) == typeof(int))
{
while (rd.Read())
{
list.Add((T)rd[0]);
}
}
else if (typeof(T) == typeof(string))
{
while (rd.Read())
{
list.Add((T)rd[0]);
}
}
else
{
PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();
int fcnt = rd.FieldCount;
List<string> fileds = new List<string>();
for (int i = 0; i < fcnt; i++)
{
fileds.Add(rd.GetName(i).ToUpper());
}
while (rd.Read())
{
IDataRecord record = rd;
obj = new T();
foreach (PropertyInfo pro in propertyInfoList)
{
if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
{
continue;
}
pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
}
list.Add((T)obj);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (rd != null && !rd.IsClosed)
{
rd.Close();
rd.Dispose();
}
}
return list;
}
#endregion
#region 獲取列表
/// <summary>
/// 獲取列表
/// </summary>
public static List<T> FindListBySql<T>(string sql, params DbParameter[] cmdParms) where T : new()
{
List<T> list = new List<T>();
object obj;
IDataReader rd = null;
try
{
rd = ExecuteReader(sql, cmdParms);
if (typeof(T) == typeof(int))
{
while (rd.Read())
{
list.Add((T)rd[0]);
}
}
else if (typeof(T) == typeof(string))
{
while (rd.Read())
{
list.Add((T)rd[0]);
}
}
else
{
PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();
int fcnt = rd.FieldCount;
List<string> fileds = new List<string>();
for (int i = 0; i < fcnt; i++)
{
fileds.Add(rd.GetName(i).ToUpper());
}
while (rd.Read())
{
IDataRecord record = rd;
obj = new T();
foreach (PropertyInfo pro in propertyInfoList)
{
if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
{
continue;
}
pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
}
list.Add((T)obj);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (rd != null && !rd.IsClosed)
{
rd.Close();
rd.Dispose();
}
}
return list;
}
#endregion
#region 分頁獲取列表
/// <summary>
/// 分頁(任意entity，盡量少的字段)
/// </summary>
public static PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage) where T : new()
{
PagerModel pagerModel = new PagerModel(currentPage, pageSize);
using (DbConnection connection = GetConnection())
{
connection.Open();
IDbCommand cmd = null;
StringBuilder sb = new StringBuilder();
string commandText = null;
int startRow = 0;
int endRow = 0;
switch (m_DBType)
{
case "oracle":
#region 分頁查詢語句
commandText = string.Format("select count(*) from ({0}) T", sql);
cmd = GetCommand(commandText, connection);
pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());
startRow = pageSize * (currentPage - 1);
endRow = startRow + pageSize;
sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
sb.Append(sql);
if (!string.IsNullOrWhiteSpace(orderby))
{
sb.Append(" ");
sb.Append(orderby);
}
sb.Append(" ) row_limit where rownum <= ");
sb.Append(endRow);
sb.Append(" ) where rownum_ >");
sb.Append(startRow);
#endregion
break;
case "mssql":
#region 分頁查詢語句
commandText = string.Format("select count(*) from ({0}) T", sql);
cmd = GetCommand(commandText, connection);
pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());
startRow = pageSize * (currentPage - 1) + 1;
endRow = startRow + pageSize - 1;
sb.Append(string.Format(@"
select * from
(select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
#endregion
break;
case "mysql":
#region 分頁查詢語句
commandText = string.Format("select count(*) from ({0}) T", sql);
cmd = GetCommand(commandText, connection);
pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());
startRow = pageSize * (currentPage - 1);
sb.Append("select * from (");
sb.Append(sql);
if (!string.IsNullOrWhiteSpace(orderby))
{
sb.Append(" ");
sb.Append(orderby);
}
sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
#endregion
break;
case "sqlite":
#region 分頁查詢語句
commandText = string.Format("select count(*) from ({0}) T", sql);
cmd = GetCommand(commandText, connection);
pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());
startRow = pageSize * (currentPage - 1);
sb.Append(sql);
if (!string.IsNullOrWhiteSpace(orderby))
{
sb.Append(" ");
sb.Append(orderby);
}
sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
#endregion
break;
}
List<T> list = FindListBySql<T>(sb.ToString());
pagerModel.result = list;
}
return pagerModel;
}
#endregion
#region 分頁獲取列表
/// <summary>
/// 分頁(任意entity，盡量少的字段)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
public static PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage, params DbParameter[] cmdParms) where T : new()
{
PagerModel pagerModel = new PagerModel(currentPage, pageSize);
using (DbConnection connection = GetConnection())
{
connection.Open();
IDbCommand cmd = null;
StringBuilder sb = new StringBuilder();
string commandText = null;
int startRow = 0;
int endRow = 0;
switch (m_DBType)
{
case "oracle":
#region 分頁查詢語句
commandText = string.Format("select count(*) from ({0}) T", sql);
cmd = GetCommand(commandText, connection);
foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());
startRow = pageSize * (currentPage - 1);
endRow = startRow + pageSize;
sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
sb.Append(sql);
if (!string.IsNullOrWhiteSpace(orderby))
{
sb.Append(" ");
sb.Append(orderby);
}
sb.Append(" ) row_limit where rownum <= ");
sb.Append(endRow);
sb.Append(" ) where rownum_ >");
sb.Append(startRow);
#endregion
break;
case "mssql":
#region 分頁查詢語句
commandText = string.Format("select count(*) from ({0}) T", sql);
cmd = GetCommand(commandText, connection);
foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());
startRow = pageSize * (currentPage - 1) + 1;
endRow = startRow + pageSize - 1;
sb.Append(string.Format(@"
select * from
(select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
#endregion
break;
case "mysql":
#region 分頁查詢語句
commandText = string.Format("select count(*) from ({0}) T", sql);
cmd = GetCommand(commandText, connection);
foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());
startRow = pageSize * (currentPage - 1);
sb.Append("select * from (");
sb.Append(sql);
if (!string.IsNullOrWhiteSpace(orderby))
{
sb.Append(" ");
sb.Append(orderby);
}
sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
#endregion
break;
case "sqlite":
#region 分頁查詢語句
commandText = string.Format("select count(*) from ({0}) T", sql);
cmd = GetCommand(commandText, connection);
foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());
startRow = pageSize * (currentPage - 1);
sb.Append(sql);
if (!string.IsNullOrWhiteSpace(orderby))
{
sb.Append(" ");
sb.Append(orderby);
}
sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
#endregion
break;
}
List<T> list = FindListBySql<T>(sb.ToString(), cmdParms);
pagerModel.result = list;
}
return pagerModel;
}
#endregion
#region 分頁獲取列表
/// <summary>
/// 分頁(任意entity，盡量少的字段)
/// </summary>
public static DataSet FindPageBySql(string sql, string orderby, int pageSize, int currentPage, out int totalCount, params DbParameter[] cmdParms)
{
DataSet ds = null;
using (DbConnection connection = GetConnection())
{
connection.Open();
IDbCommand cmd = null;
StringBuilder sb = new StringBuilder();
string commandText = null;
int startRow = 0;
int endRow = 0;
totalCount = 0;
switch (m_DBType)
{
case "oracle":
#region 分頁查詢語句
commandText = string.Format("select count(*) from ({0}) T", sql);
cmd = GetCommand(commandText, connection);
foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
totalCount = int.Parse(cmd.ExecuteScalar().ToString());
startRow = pageSize * (currentPage - 1);
endRow = startRow + pageSize;
sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");
sb.Append(sql);
if (!string.IsNullOrWhiteSpace(orderby))
{
sb.Append(" ");
sb.Append(orderby);
}
sb.Append(" ) row_limit where rownum <= ");
sb.Append(endRow);
sb.Append(" ) where rownum_ >");
sb.Append(startRow);
#endregion
break;
case "mssql":
#region 分頁查詢語句
commandText = string.Format("select count(*) from ({0}) T", sql);
cmd = GetCommand(commandText, connection);
foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
totalCount = int.Parse(cmd.ExecuteScalar().ToString());
startRow = pageSize * (currentPage - 1) + 1;
endRow = startRow + pageSize - 1;
sb.Append(string.Format(@"
select * from
(select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTable
where rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));
#endregion
break;
case "mysql":
#region 分頁查詢語句
commandText = string.Format("select count(*) from ({0}) T", sql);
cmd = GetCommand(commandText, connection);
foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
totalCount = int.Parse(cmd.ExecuteScalar().ToString());
startRow = pageSize * (currentPage - 1);
sb.Append("select * from (");
sb.Append(sql);
if (!string.IsNullOrWhiteSpace(orderby))
{
sb.Append(" ");
sb.Append(orderby);
}
sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
#endregion
break;
case "sqlite":
#region 分頁查詢語句
commandText = string.Format("select count(*) from ({0}) T", sql);
cmd = GetCommand(commandText, connection);
foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm);
totalCount = int.Parse(cmd.ExecuteScalar().ToString());
startRow = pageSize * (currentPage - 1);
sb.Append(sql);
if (!string.IsNullOrWhiteSpace(orderby))
{
sb.Append(" ");
sb.Append(orderby);
}
sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);
#endregion
break;
}
ds = Query(sql, cmdParms);
}
return ds;
}
#endregion
#region getReaderValue 轉換數據
/// <summary>
/// 轉換數據
/// </summary>
private static Object getReaderValue(Object rdValue, Type ptype)
{
if (ptype == typeof(double))
return Convert.ToDouble(rdValue);
if (ptype == typeof(decimal))
return Convert.ToDecimal(rdValue);
if (ptype == typeof(int))
return Convert.ToInt32(rdValue);
if (ptype == typeof(long))
return Convert.ToInt64(rdValue);
if (ptype == typeof(DateTime))
return Convert.ToDateTime(rdValue);
if (ptype == typeof(Nullable<double>))
return Convert.ToDouble(rdValue);
if (ptype == typeof(Nullable<decimal>))
return Convert.ToDecimal(rdValue);
if (ptype == typeof(Nullable<int>))
return Convert.ToInt32(rdValue);
if (ptype == typeof(Nullable<long>))
return Convert.ToInt64(rdValue);
if (ptype == typeof(Nullable<DateTime>))
return Convert.ToDateTime(rdValue);
return rdValue;
}
#endregion
#region 獲取主鍵名稱
/// <summary>
/// 獲取主鍵名稱
/// </summary>
public static string GetIdName(Type type)
{
PropertyInfo[] propertyInfoList = GetEntityProperties(type);
foreach (PropertyInfo propertyInfo in propertyInfoList)
{
if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0)
{
return propertyInfo.Name;
}
}
return "Id";
}
#endregion
#region 獲取主鍵值
/// <summary>
/// 獲取主鍵名稱
/// </summary>
public static object GetIdVal(object val)
{
string idName = GetIdName(val.GetType());
if (!string.IsNullOrWhiteSpace(idName))
{
return val.GetType().GetProperty(idName).GetValue(val, null);
}
return 0;
}
#endregion
#region 獲取實體類屬性
/// <summary>
/// 獲取實體類屬性
/// </summary>
private static PropertyInfo[] GetEntityProperties(Type type)
{
List<PropertyInfo> result = new List<PropertyInfo>();
PropertyInfo[] propertyInfoList = type.GetProperties();
foreach (PropertyInfo propertyInfo in propertyInfoList)
{
if (propertyInfo.GetCustomAttributes(typeof(EdmRelationshipNavigationPropertyAttribute), false).Length == 0
&& propertyInfo.GetCustomAttributes(typeof(BrowsableAttribute), false).Length == 0)
{
result.Add(propertyInfo);
}
}
return result.ToArray();
}
#endregion
#region 獲取基類
/// <summary>
/// 獲取基類
/// </summary>
public static Type GetBaseType(Type type)
{
while (type.BaseType != null && type.BaseType.Name != typeof(Object).Name)
{
type = type.BaseType;
}
return type;
}
#endregion
#endregion
#region 事務
#region 開始事務
/// <summary>
/// 開始事務
/// </summary>
public static void BeginTransaction()
{
DbConnection conn = GetConnection();
if (conn.State != ConnectionState.Open) conn.Open();
m_Tran = conn.BeginTransaction();
}
#endregion
#region 提交事務
/// <summary>
/// 提交事務
/// </summary>
public static void CommitTransaction()
{
if (m_Tran == null) return; //防止重復提交
DbConnection conn = m_Tran.Connection;
try
{
m_Tran.Commit();
}
catch (Exception ex)
{
m_Tran.Rollback();
}
finally
{
if (conn.State == ConnectionState.Open) conn.Close();
m_Tran.Dispose();
m_Tran = null;
}
}
#endregion
#region 回滾事務(出錯時調用該方法回滾)
/// <summary>
/// 回滾事務(出錯時調用該方法回滾)
/// </summary>
public static void RollbackTransaction()
{
if (m_Tran == null) return; //防止重復回滾
DbConnection conn = m_Tran.Connection;
m_Tran.Rollback();
if (conn.State == ConnectionState.Open) conn.Close();
}
#endregion
#endregion
}
}
View Code
說明：DBHelper中對事務變量private static DbTransaction m_Tran使用了[ThreadStatic]標簽，以支持多用戶並發；但是如果是單個用戶使用多線程並發請求服務器，可能這種方式的數據庫事務是不支持的，不過一般項目沒有這種需求，如果有請使用HttpContext.Current.Items改寫或者其它方法改寫。
Web.config配置：
![]()
<connectionStrings>
<add name="DefaultConnection" connectionString="server=localhost;database=netcms3.0;user id=root;password=root;character set=gbk;" />
</connectionStrings>
<appSettings>
<!--數據庫類型-->
<add key="DBType" value="mysql"/>
<!--數據庫自增-->
<add key="AutoIncrement" value="false"/>
</appSettings>
View Code
說明：對於SQL Server數據庫，通過<add key="AutoIncrement" value="false"/>來設置是否使用數據庫自增。
DBHelper類庫需要引用的程序集：
除VS2012自帶的DLL外需要的DLL：
MySql.Data.dll
System.Data.SQLite.dll
其中Models類庫如下：
說明：Models目錄中的類及其屬性和數據庫中的表和字段是完全對應的，Models全部由生成器生成，並且不允許手動修改。ExtModels目錄中的類是擴展類，主要用於查詢與顯示，比如表中存的是code，但你需要關聯查詢另一張表中的name，就可以在這個擴展類中擴展一個用於顯示的name字段。Models和ExtModels目錄的中類都是partial修飾。
PagerModel類：
![]()
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace Models
{
/// <summary>
/// 分頁
/// </summary>
public class PagerModel
{
#region 字段
/// <summary>
/// 當前頁數
/// </summary>
public int page { get; set; }
/// <summary>
/// 每頁記錄數
/// </summary>
public int rows { get; set; }
/// <summary>
/// 排序字段
/// </summary>
public string sort { get; set; }
/// <summary>
/// 排序的方式asc，desc
/// </summary>
public string order { get; set; }
/// <summary>
/// 記錄
/// </summary>
public object result { get; set; }
/// <summary>
/// 記錄數
/// </summary>
public int totalRows { get; set; }
#endregion
#region 構造函數
public PagerModel()
{
}
/// <summary>
///
/// </summary>
/// <param name="page">當前頁數</param>
/// <param name="rows">每頁記錄數</param>
public PagerModel(int page, int rows)
{
this.page = page;
this.rows = rows;
}
#endregion
#region 擴展字段
/// <summary>
/// 總頁數
/// </summary>
public int pageCount
{
get
{
return (totalRows - 1) / rows + 1;
}
}
/// <summary>
/// 上一頁
/// </summary>
public int prePage
{
get
{
if (page - 1 > 0)
{
return page - 1;
}
return 1;
}
}
/// <summary>
/// 下一頁
/// </summary>
public int nextPage
{
get
{
if (page + 1 < pageCount)
{
return page + 1;
}
return pageCount;
}
}
#endregion
}
}
View Code
IsIdAttribute類：
![]()
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Models
{
/// <summary>
/// 標識該屬性是主健
/// </summary>
[Serializable, AttributeUsage(AttributeTargets.Property | AttributeTargets.Class)]
public class IsIdAttribute : Attribute
{
}
}
View Code
IsDBFieldAttribute類：
![]()
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Models
{
/// <summary>
/// 標識該屬性是數據庫字段
/// </summary>
[Serializable, AttributeUsage(AttributeTargets.Property | AttributeTargets.Class)]
public class IsDBFieldAttribute : Attribute
{
}
}
View Code
Models示例：
![]()
using System;
using System.Collections.Generic;
using System.Linq;
namespace Models
{
/// <summary>
/// 內容詳情
/// </summary>
[Serializable]
public partial class cms_content
{
/// <summary>
/// 編號
/// </summary>
[IsId]
[IsDBField]
public int id { get; set; }
/// <summary>
/// 所屬欄目ID
/// </summary>
[IsDBField]
public int? channelId { get; set; }
/// <summary>
/// 標題
/// </summary>
[IsDBField]
public string title { get; set; }
/// <summary>
/// 內容
/// </summary>
[IsDBField]
public string contents { get; set; }
/// <summary>
/// 作者
/// </summary>
[IsDBField]
public string author { get; set; }
/// <summary>
/// 閱讀次數
/// </summary>
[IsDBField]
public int? readCount { get; set; }
/// <summary>
/// 發布時間
/// </summary>
[IsDBField]
public DateTime? publishTime { get; set; }
/// <summary>
/// 發布者
/// </summary>
[IsDBField]
public int? publishUserId { get; set; }
/// <summary>
/// 審核(0待審1通過2不通過)
/// </summary>
[IsDBField]
public int? audit { get; set; }
/// <summary>
/// 審核人
/// </summary>
[IsDBField]
public int? auditUserId { get; set; }
/// <summary>
/// 審核時間
/// </summary>
[IsDBField]
public DateTime? auditTime { get; set; }
/// <summary>
/// 頁面關鍵詞
/// </summary>
[IsDBField]
public string keywords { get; set; }
/// <summary>
/// 頁面描述
/// </summary>
[IsDBField]
public string description { get; set; }
/// <summary>
/// 頁面鏈接
/// </summary>
[IsDBField]
public string pageUrl { get; set; }
/// <summary>
/// 內容封面
/// </summary>
[IsDBField]
public string imgUrl { get; set; }
/// <summary>
/// 是否鏈接(0否1是)
/// </summary>
[IsDBField]
public int? isPageUrl { get; set; }
/// <summary>
/// 模板(模板文件名，例：content.html)
/// </summary>
[IsDBField]
public string template { get; set; }
/// <summary>
/// 推薦(1推薦0不推薦)
/// </summary>
[IsDBField]
public int? recommend { get; set; }
}
}
View Code
ExtModels示例：
![]()
using System;
using System.Collections.Generic;
using System.Linq;
namespace Models
{
/// <summary>
/// 內容詳情
/// </summary>
public partial class cms_content
{
/// <summary>
/// 欄目名稱
/// </summary>
public string channelName { get; set; }
/// <summary>
/// 用戶顯示名
/// </summary>
public string showName { get; set; }
/// <summary>
/// 審核狀態
/// </summary>
public string dispAudit
{
get
{
switch (this.audit ?? 0)
{
case 0:
return "待審核";
case 1:
return "審核通過";
case 2:
return "審核不通過";
}
return "error";
}
set { }
}
/// <summary>
/// 在當前頁中的索引
/// </summary>
public int curPageSort { get; set; }
/// <summary>
/// 是否為空，模板使用，0不為空1為空
/// </summary>
public int isNull { get; set; }
}
}
View Code
如何使用：
說明：支持參數化的增刪改查，推薦使用參數化的增刪改查；非參數化的增刪改查過濾了部分數據庫關鍵字以防止SQL注入，但可能仍然不安全。下面例子中的添加、修改、根據ID刪除、根據ID集合批量刪除都是參數化的，示例中的查詢和其它方式的刪除不是參數化的，DBHelper提供了相關的參數化查詢和執行SQL。
添加：
![]()
/// <summary>
/// 添加
/// </summary>
public void Insert(object obj)
{
DBHelper.Insert(obj);
}
View Code
說明：SQL Server數據庫可以使用自增，Oracle數據庫可以使用Sequence，小系統可以使用DBHelper自帶的GetMaxID方法。
獲取最大ID(當然，ID一般采用自增，對於並發量極少的系統，或單機系統，為了省事，可以這樣做)：
![]()
/// <summary>
/// GetMaxId
/// </summary>
public int GetMaxId()
{
return DBHelper.GetMaxID<BS_Template>("id");
}
View Code
修改：
![]()
/// <summary>
/// 修改
/// </summary>
public void Update(object obj)
{
DBHelper.Update(obj);
}
View Code
刪除：
根據ID刪除：
![]()
/// <summary>
/// 刪除
/// </summary>
public void Del(int id)
{
DBHelper.Delete<BS_Template>(id);
}
View Code
根據ID集合批量刪除：
![]()
/// <summary>
/// 刪除
/// </summary>
public void BatchDelete(string ids)
{
DBHelper.BatchDelete<BS_Template>(ids);
}
View Code
根據條件刪除：
![]()
/// <summary>
/// 刪除
/// </summary>
public void Delete(string conditions)
{
DBHelper.Delete<BS_Template>(conditions);
}
View Code
其它方式的刪除請使用：
public static int ExecuteSql(string sqlString)
根據ID查詢實體：
![]()
public cms_content Get(int id)
{
return DBHelper.FindById<cms_content>(id.ToString());
}
View Code
查詢：
![]()
public List<cms_content> GetListAll()
{
StringBuilder sql = new StringBuilder(string.Format(@"
select content.*, channel.title as channelName, user.showName
from cms_content content
left join cms_channel channel on channel.id=content.channelId
left join CMS_sys_user user on user.id=content.publishUserId
where content.audit=1
order by publishTime desc,id desc"));
return DBHelper.FindListBySql<cms_content>(sql.ToString());
}
View Code
說明：ExtModels下的擴展Model，可以支持查詢數據庫中不存在的字段，並映射填充到實體類。
分頁查詢：
![]()
public List<cms_content> GetList(ref PagerModel pager, int channelId, string title, int audit)
{
StringBuilder sql = new StringBuilder(string.Format(@"
select content.*, channel.title as channelName, user.showName
from cms_content content
left join cms_channel channel on channel.id=content.channelId
left join CMS_sys_user user on user.id=content.publishUserId
where 1=1 "));
if (channelId != -1)
{
sql.AppendFormat(" and content.channelId = {0}", channelId);
}
if (!string.IsNullOrWhiteSpace(title))
{
sql.AppendFormat(" and content.title like '%{0}%'", title);
}
if (audit != -1)
{
sql.AppendFormat(" and content.audit = {0}", audit);
}
string orderby = string.Format("order by content.publishTime desc,id desc");
pager = DBHelper.FindPageBySql<cms_content>(sql.ToString(), orderby, pager.rows, pager.page);
return pager.result as List<cms_content>;
}
View Code
數據庫事務：
![]()
try
{
DBHelper.BeginTransaction(); //開啟數據庫事務
//在這裡寫增刪改操作
DBHelper.CommitTransaction(); //提交數據庫事務
}catch(Exception ex)
{
DBHelper.RollbackTransaction(); //回滾數據庫事務
}
View Code
DAL層增刪改查示例：
![]()
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using DBUtil;
using Models;
namespace DAL
{
/// <summary>
/// 內容詳情管理
/// </summary>
public class ContentDal
{
#region 獲取列表
/// <summary>
/// 獲取列表
/// </summary>
public List<cms_content> GetList(ref PagerModel pager, int channelId, string title, int audit)
{
StringBuilder sql = new StringBuilder(string.Format(@"
select content.*, channel.title as channelName, user.showName
from cms_content content
left join cms_channel channel on channel.id=content.channelId
left join CMS_sys_user user on user.id=content.publishUserId
where 1=1 "));
if (channelId != -1)
{
sql.AppendFormat(" and content.channelId = {0}", channelId);
}
if (!string.IsNullOrWhiteSpace(title))
{
sql.AppendFormat(" and content.title like '%{0}%'", title);
}
if (audit != -1)
{
sql.AppendFormat(" and content.audit = {0}", audit);
}
string orderby = string.Format("order by content.publishTime desc,id desc");
pager = DBHelper.FindPageBySql<cms_content>(sql.ToString(), orderby, pager.rows, pager.page);
return pager.result as List<cms_content>;
}
#endregion
#region 獲取列表
/// <summary>
/// 獲取列表
/// </summary>
/// <param name="pager">分頁</param>
/// <param name="channel">欄目，可以是欄目ID或欄目名稱</param>
/// <param name="where">where語句</param>
public List<cms_content> GetList(ref PagerModel pager, string channel, string where)
{
StringBuilder sql = new StringBuilder(string.Format(@"
select content.*, channel.title as channelName, user.showName
from cms_content content
left join cms_channel channel on channel.id=content.channelId
left join CMS_sys_user user on user.id=content.publishUserId
where content.audit=1 "));
if (!string.IsNullOrWhiteSpace(where))
{
sql.AppendFormat(" and {0}", where);
}
if (!string.IsNullOrWhiteSpace(channel))
{
ChannelDal channelDal = new ChannelDal();
if (Regex.IsMatch(channel, @"^\d+$")) //數字，即欄目ID
{
string channelIds = channelDal.GetChildIds(Convert.ToInt32(channel));
sql.AppendFormat(" and channelId in ({0})", channelIds);
}
else //非數字，即欄目名稱
{
string channelIds = channelDal.GetChildIds(channel);
sql.AppendFormat(" and channelId in ({0})", channelIds);
}
}
string orderby = string.Format("order by publishTime desc,id desc");
if (pager.rows > 0)
{
PagerModel pagerModel = DBHelper.FindPageBySql<cms_content>(sql.ToString(), orderby, pager.rows, pager.page);
pager.totalRows = pagerModel.totalRows;
pager.result = pagerModel.result;
List<cms_content> list = pagerModel.result as List<cms_content>;
int i = 1;
list.ForEach(a =>
{
a.curPageSort = i++;
});
return pagerModel.result as List<cms_content>;
}
else
{
return DBHelper.FindListBySql<cms_content>(sql.ToString() + orderby);
}
}
#endregion
#region 獲取列表
/// <summary>
/// 獲取列表
/// </summary>
public List<cms_content> GetListAll()
{
StringBuilder sql = new StringBuilder(string.Format(@"
select content.*, channel.title as channelName, user.showName
from cms_content content
left join cms_channel channel on channel.id=content.channelId
left join CMS_sys_user user on user.id=content.publishUserId
where content.audit=1
order by publishTime desc,id desc"));
return DBHelper.FindListBySql<cms_content>(sql.ToString());
}
#endregion
#region 獲取總數
/// <summary>
/// 獲取總數
/// </summary>
public int GetAllCount()
{
StringBuilder sql = new StringBuilder(string.Format(@"
select count(*)
from cms_content content
where content.audit=1"));
return Convert.ToInt32(DBHelper.GetSingle(sql.ToString()));
}
#endregion
#region 獲取
/// <summary>
/// 獲取
/// </summary>
public cms_content Get(int id)
{
return DBHelper.FindById<cms_content>(id.ToString());
}
/// <summary>
/// 根據channelId獲取一條內容詳情
/// </summary>
public cms_content GetByChannelId(int channelId)
{
return DBHelper.FindBySql<cms_content>(string.Format("select * from cms_content where channelId={0} and audit=1", channelId));
}
/// <summary>
/// 獲取
/// </summary>
public cms_content GetNext(int id)
{
cms_content current = Get(id);
return DBHelper.FindBySql<cms_content>(string.Format("select * from cms_content where id<{0} and channelId={1} and audit=1 order by id desc limit 0,1", current.id, current.channelId));
}
/// <summary>
/// 獲取
/// </summary>
public cms_content GetPre(int id)
{
cms_content current = Get(id);
return DBHelper.FindBySql<cms_content>(string.Format("select * from cms_content where id>{0} and channelId={1} and audit=1 order by id asc limit 0,1", current.id, current.channelId));
}
#endregion
#region 添加
/// <summary>
/// 添加
/// </summary>
public void Insert(cms_content model)
{
model.id = DBHelper.GetMaxID<cms_content>("id");
DBHelper.Insert(model);
}
#endregion
#region 修改
/// <summary>
/// 修改
/// </summary>
public void Update(cms_content model)
{
DBHelper.Update(model);
}
#endregion
#region 刪除
/// <summary>
/// 刪除
/// </summary>
public void Del(string ids)
{
DBHelper.BatchDelete<cms_content>(ids);
}
#endregion
}
}
View Code
例子代碼下載：http://files.cnblogs.com/files/s0611163/DBHelperDemo.zip
說明：例子程序是Winform，該DBHelper也適用於Web項目。
配套Model生成器下載：http://files.cnblogs.com/files/s0611163/Model%E7%94%9F%E6%88%90%E5%99%A8.zip
如果您不明白我為什麼要寫DBHelper，為什麼沒有語法糖，為什麼查詢和分頁查詢要使用原生SQL，看看下面我們ERP項目中的代碼，你會明白，當然，這個項目設計的確實不好：
1、
![]()
/// <summary>
/// 統計SQL
/// </summary>
public override string GetReportSql(string formData)
{
ProductInvoiceModel search = JsonConvert.DeserializeObject<ProductInvoiceModel>(formData);
StringBuilder sql = new StringBuilder(string.Format(@"
select * from
(select distinct
'出庫單' as '報表類型',
pos.billNo as '單據編號',
pos.projectCode as '項目編號',
pro.projectName as '項目名稱',
ma.subProject as '項目子項',
case when cs.conMemberCode is not NULL then cs.conMemberCode else mat.materialCode end as '構件編碼',
case when cmd.memberName is not NULL then cmd.memberName else mat.materialName end as '材料名稱',
case when cs.conMemberCode is not NULL then '構件' else '物料' end as '類型',
mad.model as '規格型號',
dic1.dicItem as '單位',
posd.qty as '數量',
CONVERT(varchar(100), pos.billDate, 23) as '日期'
from Pro_ProductOutStorageDet posd
left join Pro_ProductOutStorage pos on pos.id=posd.parentId
left join Pro_MatAllotDet mad on posd.matAllotDetId=mad.id
left join Pro_MatAllot ma on ma.id=mad.parentID and ma.status=2
left join Pro_MatStock ms on ms.id=mad.matStockId and mad.memberType=1
left join Pro_ConStock cs on cs.id=mad.matStockId and mad.memberType=0
left join sys_material mat on mat.materialCode=ms.materailCode
left join Pro_ProductInstorage pi on pi.billNo=cs.billNo
left join Pro_ProductInstorageDet pid on pid.parentId=pi.id
left join Pro_ConMember cm on cm.billCode=pi.conMemberBillNo
left join Pro_ConMemberDet cmd on cmd.id=pid.conMemberDetId
left join Pro_Info pro on pos.projectCode=pro.projectNum
left join sys_dicDetail dic1 on dic1.dicItemcode=mad.unitCode
union all
select distinct
'XXX出庫單' as '報表類型',
pos.billNo as '單據編號',
pos.projectCode as '項目編號',
pro.projectName as '項目名稱',
'' as '項目子項',
case when cs.conMemberCode is not NULL then cs.conMemberCode else mat.materialCode end as '構件編碼',
case when cmd.memberName is not NULL then cmd.memberName else mat.materialName end as '材料名稱',
case when cs.conMemberCode is not NULL then '構件' else '物料' end as '類型',
case when cmd.model is not NULL then cmd.model else mat.model end as '規格型號',
case when dic1.dicItem is not NULL then dic1.dicItem else dic2.dicItem end as '單位',
posd.qty as '數量',
CONVERT(varchar(100), pos.billDate, 23) as '日期'
from Pro_ProductOut2StorageDet posd
left join Pro_ProductOut2Storage pos on pos.id=posd.parentId
left join Pro_MatStock ms on ms.id=posd.matStockId
left join Pro_ConStock cs on cs.id=posd.conInventoryDetId
left join sys_material mat on mat.materialCode=ms.materailCode
left join Pro_ProductInstorage pi on pi.billNo=cs.billNo
left join Pro_ProductInstorageDet pid on pid.parentId=pi.id
left join Pro_ConMember cm on cm.billCode=pi.conMemberBillNo
left join Pro_ConMemberDet cmd on cmd.id=pid.conMemberDetId
left join Pro_Info pro on pos.projectCode=pro.projectNum
left join sys_dicDetail dic1 on dic1.dicItemcode=mat.unitCode
left join sys_dicDetail dic2 on dic2.dicItemcode=cmd.qtyUnit
union all
select distinct
'生產消耗單' as '報表類型',
pc.billCode as '單據編號',
pro.projectNum as '項目編號',
pro.projectName as '項目名稱',
ll.subProject as '項目子項',
case when cs.conMemberCode is not NULL then cs.conMemberCode else mat.materialCode end as '構件編碼',
case when cmd.memberName is not NULL then cmd.memberName else mat.materialName end as '材料名稱',
case when cs.conMemberCode is not NULL then '構件' else '物料' end as '類型',
case when cmd.model is not NULL then cmd.model else mat.model end as '規格型號',
case when dic1.dicItem is not NULL then dic1.dicItem else dic2.dicItem end as '單位',
pcd.consumeQty as '數量',
CONVERT(varchar(100), pc.billDate, 23) as '日期'
from Pro_ProductConsume pc
left join Pro_ProductConsumeDet pcd on pcd.parentId=pc.id
left join Pro_LingLiaoDet lld on lld.id=pcd.lingLiaoDetId
left join Pro_LingLiao ll on ll.id=lld.parentId
left join Pro_MatStock ms on ms.id=lld.matStockId and lld.memberType=1
left join Pro_ConStock cs on cs.id=lld.matStockId and lld.memberType=0
left join sys_material mat on mat.materialCode=ms.materailCode
left join Pro_ProductInstorage pi on pi.billNo=cs.billNo
left join Pro_ProductInstorageDet pid on pid.parentId=pi.id
left join Pro_ConMember cm on cm.billCode=pi.conMemberBillNo
left join Pro_ConMemberDet cmd on cmd.id=pid.conMemberDetId
left join Pro_ProTask pt on pt.billCode=ll.proTaskCode
left join Pro_Info pro on pt.proInfCode=pro.billCode
left join sys_dicDetail dic1 on dic1.dicItemcode=mat.unitCode
left join sys_dicDetail dic2 on dic2.dicItemcode=cmd.qtyUnit
) T where 1=1 "));
if (!string.IsNullOrEmpty(search.projectName))
{
sql.AppendFormat(" and T.項目名稱 like '%{0}%' ", search.projectName.Trim());
}
if (!string.IsNullOrEmpty(search.projectCode))
{
sql.AppendFormat(" and T.項目編號 like '%{0}%' ", search.projectCode.Trim());
}
if (!string.IsNullOrEmpty(search.btime) && !string.IsNullOrEmpty(search.etime))
{
sql.AppendFormat(" and T.日期 >= '{0}'", search.btime);
}
if (!string.IsNullOrEmpty(search.etime))
{
sql.AppendFormat(" and T.日期 <= '{0}'", search.etime);
}
return sql.ToString();
}
View Code
說明：查詢SQL書寫規范：SQL不能寫的亂七八糟，該換行換行，該對齊對齊。
2、
![]()
public List<MatAllotModel> GeProMatAllotList(ref PagerModel pager, string billCode, string projectNum, string projectName, string remarks, int status)
{
string sql = @"select v.*,ISNULL(v.IsFinish,(case when ISNULL(v.pqty,0)= ISNULL(v.mqty,0) then 1 else 0 end)) as IsEnd from (
select ma.*,e.name + '('+isnull(e.mobilephone,'')+')' as billCreatorName,i.projectName,s.storeHouseName as inStoreName,
(select stuff((select distinct ','+s.storeHouseName from Pro_MatAllotDet mad
join sys_storehouse s on mad.outStorageCode=s.storeHouseCode
where parentId =ma.id for xml path('')),1,1,'')) as outStoreName,I.projectSim,D.dicItem AS typename,ppi.id as InvoiceId,i.projectNum,
(select sum(md.qty) from Pro_MatAllotDet md where md.parentId =ma.id) as mqty,
(select sum(qty) from Pro_ProductOutStorageDet pod
left join Pro_ProductOutStorage po on po.id = pod.parentID
where po.matAllotBillCode = ma.billCode) as pqty
from Pro_MatAllot ma
join sys_storehouse s on ma.inStoreCode=s.storeHouseCode
join sys_employee e on ma.billCreator=e.employeeCode
left join Pro_ProductInvoice ppi on ppi.MatAllotBillCode=ma.billCode
left join Pro_Info i on ma.projectCode = i.projectNum
LEFT JOIN sys_dicDetail D ON D.dicItemcode = I.projectType
) as v where 1=1 ";
if (!string.IsNullOrEmpty(billCode))
{
sql += " and v.billCode like '%" + billCode.Trim() + "%'";
}
if (!string.IsNullOrEmpty(projectName))
{
sql += " and v.projectName like '%" + projectName.Trim() + "%'";
}
if (!string.IsNullOrEmpty(projectNum))
{
sql += " and v.projectNum like '%" + projectNum.Trim() + "%'";
}
if (!string.IsNullOrEmpty(remarks))
{
sql += " and v.remarks like '%" + remarks.Trim() + "%'";
}
sql += " and v.status=" + status + "";
List<MatAllotModel> matAllot = EntityHelper.Default.GetPageEntities<MatAllotModel>(pager.page, pager.rows, sql, "id", pager.sort, pager.order);
pager.totalRows = DBHelper.Default.Count(sql);
pager.result = matAllot;
return matAllot;
}
View Code
3、
![]()
public static List<ConStockDialogMod> GetMaterialData(ref PagerModel pager, string memberName, string storeCode, string parentId, string store)
{
StringBuilder where = new StringBuilder(" and 1=1 ");
if (!string.IsNullOrEmpty(memberName)) //當物料為空時，查詢出所有的結果集，否則查詢出此種相似物料的結果集
where.AppendFormat(" and t3.memberName like '%{0}%' ", memberName.Trim());
if (!string.IsNullOrEmpty(storeCode)) //當物料為空時，查詢出所有的結果集，否則查詢出此種相似物料的結果集
where.AppendFormat(" and SSH.storeHouseCode='{0}' ", storeCode);
if (!string.IsNullOrEmpty(parentId)) //當物料為空時，查詢出所有的結果集，否則查詢出此種相似物料的結果集
where.AppendFormat(" and t3.parentId = {0} ", parentId);
if (!string.IsNullOrEmpty(store))
where.AppendFormat(" and SSH.storeHouseName like '%{0}%' ", store.Trim());
string sql = @"SELECT PM.id,PM.conMemberCode as conMemberCode,PM.price,(PM.qty-PM.lockQty-PM.usedQty) AS stockNum,
PM.instoreTime,PM.billNo,PM.storeCode,PM.lockQty,PM.usedQty,SSH.storeHouseName AS storeName,
t3.allWeight,t3.qtyUnit,t3.weight as weights,t3.memberName,t3.model,t3.qulity,
d1.dicItem as allWeightUnitName,d2.dicItem as qtyUnitName,
d3.dicItem as qulityName,d4.dicItem as weightUnitName,
(select SUM(pt.qty) from Pro_ProductOutstorageDet pt where pt.conInventoryDetId=PM.id)as outQty
from Pro_ConStock PM
join Pro_ConPurInstore cpd on PM.billNo=cpd.billCode
join Pro_ConPurInstoreDet cp on cpd.id=cp.parentId and PM.billId=cp.id
join dbo.Pro_ConGoodsDet t on t.id=cp.conGoodsDetId
join dbo.Pro_ConPurchaseContractDet t1 on t1.id=t.conContractDetId
join dbo.Pro_ConPurchasePlanDet t2 on t2.id=t1.conPlanDetID
join dbo.Pro_ConMemberDet t3 on t3.id=t2.conMemberDetId
left join sys_storehouse SSH on SSH.storeHouseCode=PM.storeCode
left join sys_dicDetail d1 on d1.dicItemcode=t3.allWeightUnit
left join sys_dicDetail d2 on d2.dicItemcode=t3.qtyUnit
left join sys_dicDetail d3 on d3.dicItemcode=t3.qulity
left join sys_dicDetail d4 on d4.dicItemcode=t3.weightUnit
WHERE PM.qty-PM.lockQty-PM.usedQty > 0 " + where +
@" union
SELECT PM.id,PM.conMemberCode as conMemberCode,PM.price,(PM.qty-PM.lockQty-PM.usedQty) AS stockNum,
PM.instoreTime,PM.billNo,PM.storeCode,PM.lockQty,PM.usedQty,
SSH.storeHouseName AS storeName,
t3.allWeight,t3.qtyUnit,t3.weight as weights,t3.memberName,t3.model,t3.qulity,
d1.dicItem as allWeightUnitName,d2.dicItem as qtyUnitName,
d3.dicItem as qulityName,d4.dicItem as weightUnitName,
(select SUM(pt.qty) from Pro_ProductOutstorageDet pt where pt.conInventoryDetId=PM.id)as outQty
from Pro_ConStock PM
join Pro_ProductInstorage cpd on PM.billNo=cpd.billNo
join Pro_ProductInstorageDet cp on cpd.id=cp.parentId and PM.billId=cp.id
join dbo.Pro_ConMemberDet t3 on t3.id=cp.conMemberDetId
left join sys_storehouse SSH on SSH.storeHouseCode=PM.storeCode
left join sys_dicDetail d1 on d1.dicItemcode=t3.allWeightUnit
left join sys_dicDetail d2 on d2.dicItemcode=t3.qtyUnit
left join sys_dicDetail d3 on d3.dicItemcode=t3.qulity
left join sys_dicDetail d4 on d4.dicItemcode=t3.weightUnit
WHERE PM.qty-PM.lockQty-PM.usedQty > 0 " + where;
List<ConStockDialogMod> model = EntityHelper.Default.GetPageEntities<ConStockDialogMod>(pager.page, pager.rows, sql.ToString(), "id", pager.sort, pager.order);
pager.totalRows = DBHelper.Default.Count(sql.ToString());
pager.result = model;
return model;
}
View Code
4、
![]()
/// <summary>
/// Sql
/// </summary>
/// <param name="formData"></param>
/// <returns></returns>
public override string GetReportSql(string formData)
{
TaskAllocationModel search = JsonConvert.DeserializeObject<TaskAllocationModel>(formData);
StringBuilder where = new StringBuilder(" where 1=1 ");
if (!string.IsNullOrEmpty(search.projectCode))
{
where.AppendFormat(" and ta.projectCode like '%{0}%' ", search.projectCode.Trim());
}
if (!string.IsNullOrEmpty(search.projectName))
{
where.AppendFormat(" and pro.projectName like '%{0}%' ", search.projectName.Trim());
}
string sql = @"select ta.billCode as '單號',pro.projectName as '項目名稱',pro.projectNum as '項目編號',ta.desingBillCode as '項目設計任務單',
CASE ta.violent WHEN '1' THEN '是' ELSE '否' END as '是否加急',
CASE ta.taskType WHEN '0' THEN '載荷圖' WHEN '1' THEN '方案圖' WHEN '2' THEN '工程量統計' ELSE '載荷圖與工程量統計' END as '任務類型',
ta.desingContent as '設計內容',ta.auditorScore as '審核人打分',
(select e.RealName
from Wf2Operate oper
left join Wf2Node n on n.id = oper.Wf2NoteID
left join employee e on e.employeecode = oper.Operator
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='設計' and oper.OperationID != -3) as '設計者',
(select e.RealName
from Wf2Operate oper
left join Wf2Node n on n.id = oper.Wf2NoteID
left join employee e on e.employeecode = oper.Operator
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='校對' and oper.OperationID != -3) as '校對者',
(select e.RealName
from Wf2Operate oper
left join Wf2Node n on n.id = oper.Wf2NoteID
left join employee e on e.employeecode = oper.Operator
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='審核' and oper.OperationID != -3) as '審核者',
(select e.RealName
from Wf2Operate oper
left join Wf2Node n on n.id = oper.Wf2NoteID
left join employee e on e.employeecode = oper.Operator
where Wf2InstanceID = ta.LastWf2InstanceID and n.Name='審定' and oper.OperationID != -3) as '審定者',
e.name as '創建人',ta.creatTime as '創建時間'
from Pro_TaskAllocation ta
left join Pro_Info pro on ta.projectCode=pro.projectNum
left join sys_employee e on ta.creator=e.employeeCode
" + where;
return sql.ToString();
}
View Code
