先创建一个基本的SqlHelperBase,用于继承和实现数据库操作功能:
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace RongZi.DBUtility
{
public class SqlHelperBase
{
#region 属性
public string ConnStr;
private SqlConnection conn = null;//数据库链接对象
private SqlCommand cmd = null;//数据库操作
private SqlDataReader sdr = null;//数据集
#endregion 属性
#region 基本用法
/// <summary>
/// 链接数据库
/// </summary>
/// <returns></returns>
private SqlConnection GetConn()
{
conn = new SqlConnection(ConnStr);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
return conn;
}
/// <summary>
/// 增删改 INSERT|DELETE|UPDATE
/// SqlParameter[] paras = new SqlParameter[]{new SqlParameter("@Name",DBNull.Value),};
/// </summary>
public int ExecuteNonQuery(string sqlStr, SqlParameter[] sqlparas = null)
{
using (cmd = new SqlCommand(sqlStr, GetConn()))
{
//cmd.CommandTimeout = 600;
if (sqlparas != null) cmd.Parameters.AddRange(sqlparas);
int res = cmd.ExecuteNonQuery();
conn.Close();
return res;
}
}
/// <summary>
/// 查 SELECT
/// SqlParameter[] paras = new SqlParameter[]{new SqlParameter("@Name",DBNull.Value),};
/// </summary>
public DataTable ExecuteReader(string sqlStr, SqlParameter[] sqlparas = null)
{
using (cmd = new SqlCommand(sqlStr, GetConn()))
{
cmd.CommandTimeout = 600;
DataTable dt = new DataTable();
if (sqlparas != null) cmd.Parameters.AddRange(sqlparas);
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
conn.Close();
return dt;
}
}
/// <summary>
/// 执行查询,并返回查询所返回的结果集中第一行的第一列。 如:
/// insert一条数据,返回自增的id值:insert into table([NAME])values ('名字');Select @@Identity;
/// </summary>
public object ExecuteScalar(string sqlStr, SqlParameter[] sqlparas = null)
{
using (cmd = new SqlCommand(sqlStr, GetConn()))
{
cmd.CommandTimeout = 600;
if (sqlparas != null) cmd.Parameters.AddRange(sqlparas);
object res = cmd.ExecuteScalar();
conn.Close();
return res;
}
}
#endregion 基本用法
#region 存储过程
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="proc">存储过程名称</param>
/// <param name="spArr">参数</param>
/// <returns>受影响的行数</returns>
public int ExecuteNonQuery_ForProc(string proc, SqlParameter[] spArr = null)
{
cmd = new SqlCommand(proc, GetConn());
//cmd.CommandTimeout = 600;
cmd.CommandType = CommandType.StoredProcedure;
if (spArr != null) cmd.Parameters.AddRange(spArr);
int i = cmd.ExecuteNonQuery();
conn.Close();
return i;
}
/// <summary>
/// 执行存储过程-带输出参数(最后一个参数)
/// </summary>
/// <param name="proc">存储过程名称</param>
/// <param name="spArr">参数</param>
/// <returns>输出参数的值</returns>
public object ExecuteNonQuery_ForProc_Output(string ProcName, SqlParameter[] parm)
{
cmd = new SqlCommand(ProcName, GetConn());
//cmd.CommandTimeout = 600;
//最后一个参数为输出参数
parm[parm.Length - 1].Direction = ParameterDirection.Output;
cmd.CommandType = CommandType.StoredProcedure;
if (parm != null)
{
cmd.Parameters.AddRange(parm);
}
int result = cmd.ExecuteNonQuery();
conn.Close();
//返回输出的参数,看存储过程中定义的输出参数是什么类型,这里就转换成什么类型
return parm[parm.Length - 1].Value;
}
#endregion
#region 事物
/// <summary>
/// 事物-执行多条操作(INSERT|UPDATE|DELETE)语句
/// </summary>
/// <param name="Sqlstr">sql语句数组</param>
/// <returns></returns>
public int ExecTran(string[] Sqlstr)
{
conn = GetConn();
SqlTransaction tran = conn.BeginTransaction();//开始事物
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
//cmd.CommandTimeout = 600;
try
{
int count = Sqlstr.Length;
for (int i = 0; i < count; i++)
{
cmd.CommandText = Sqlstr[i];
cmd.ExecuteNonQuery();
}
tran.Commit();//提交事物
return 1;
}
catch
{
tran.Rollback();//回滚事物
return 0;
}
finally
{
conn.Close();
tran.Dispose();
}
}
#endregion
#region 常用方法
public DataTable SELECT(string sqlStr, SqlParameter[] sqlparas = null)
{
return ExecuteReader(sqlStr, sqlparas);
}
public int COUNT(string sqlStr, SqlParameter[] sqlparas = null)
{
int i = 0;
object obj = ExecuteScalar(sqlStr, sqlparas);
int.TryParse(obj.ToString(), out i);
return i;
}
public int INSERT(string sqlStr, SqlParameter[] sqlparas = null)
{
return ExecuteNonQuery(sqlStr, sqlparas);
}
public int UPDATE(string sqlStr, SqlParameter[] sqlparas = null)
{
return ExecuteNonQuery(sqlStr, sqlparas);
}
public int DELETE(string sqlStr, SqlParameter[] sqlparas = null)
{
return ExecuteNonQuery(sqlStr, sqlparas);
}
public int DELETE(string table, string key, object keyValue)
{
string sqlStr = "DELETE FROM [" + table + "] WHERE [" + key + "]='" + keyValue + "'";
return ExecuteNonQuery(sqlStr);
}
#endregion
#region 特殊方法
public DataTable Query(string sqlStr)
{
return ExecuteReader(sqlStr);
}
public DataTable Query(string sqlStr, SqlParameter[] sqlparas = null)
{
return ExecuteReader(sqlStr, sqlparas);
}
/// <summary>
/// 执行Insert、Update、Delete,返回操作的行数
/// </summary>
public int ExecuteSql(string sqlStr, SqlParameter[] sqlparas = null)
{
return ExecuteNonQuery(sqlStr, sqlparas);
}
/// <summary>
/// 执行Insert,并返回新增的主键id
/// </summary>
public object GetSingle(string sqlStr, SqlParameter[] sqlparas = null)
{
return ExecuteScalar(sqlStr, sqlparas);
}
/// <summary>
/// 判断是否存在
/// </summary>
public bool Exists(string sqlStr, SqlParameter[] sqlparas = null)
{
DataTable dt = ExecuteReader(sqlStr, sqlparas);
if (dt != null && dt.Rows.Count > 0)
{
return true;
}
else
{
return false;
}
}
//获取数据
public DataTable GetData(string sqlStr, SqlParameter[] sqlparas = null)
{
return ExecuteReader(sqlStr, sqlparas);
}
/// <summary>
/// 简单单表查询
/// </summary>
/// <param name="TableName">必填</param>
/// <param name="Top"></param>
/// <param name="fldName"></param>
/// <param name="strWhere"></param>
/// <param name="orderBy"></param>
/// <returns></returns>
public DataTable GetData(string TableName, int Top = 0, string fldName = "", string strWhere = "", string orderBy = "")
{
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(orderBy))
{
strSql.Append(" ORDER BY " + orderBy);
}
DataTable dt = ExecuteReader(strSql.ToString());
return dt;
}
public int GetCount(string TableName, string strWhere = "")
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select COUNT(*) from " + TableName);
if (!string.IsNullOrEmpty(strWhere))
{
strSql.Append(" where " + strWhere);
}
int i = 0;
object obj = ExecuteScalar(strSql.ToString());
int.TryParse(obj.ToString(), out i);
return i;
}
/// <summary>
/// SQL分页查询
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="fldName">显示字段</param>
/// <param name="strWhere">where条件</param>
/// <param name="orderBy">排序</param>
/// <param name="PageSize">每页多少数据</param>
/// <param name="PageIndex">当前第几页</param>
/// <returns></returns>
public DataTable PAGING(string TableName, string fldName, string strWhere, string orderBy, int PageSize = 500, 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,", orderBy);
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 ");
if (!string.IsNullOrEmpty(orderBy))
{
strSql.Append(" ORDER BY " + orderBy);
}
DataTable dt = ExecuteReader(strSql.ToString());
return dt;
}
/// <summary>
/// SQL分页查询
/// 该语法支持SQL Server2012及以上版本
/// </summary>
/// <param name="TableName">表名(必填)</param>
/// <param name="fldName">显示字段(缺省为:*)</param>
/// <param name="strWhere">查询条件</param>
/// <param name="orderBy">排序条件</param>
/// <param name="PageSize">每页行数</param>
/// <param name="PageIndex">第几页</param>
/// <returns></returns>
public DataTable PAGING_2012(string TableName, string fldName, string strWhere, string orderBy = "", int PageSize = 500, int PageIndex = 1)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT ");
if (!string.IsNullOrWhiteSpace(fldName))
{
strSql.AppendFormat(fldName + " FROM {0}", TableName);//指定字段
}
else
{
strSql.AppendFormat(" * FROM {0}", TableName);//全部字段
}
if (!string.IsNullOrWhiteSpace(strWhere))
{
strSql.Append(" WHERE " + strWhere);
}
if (!string.IsNullOrWhiteSpace(orderBy))
{
strSql.AppendFormat(" order by {0} offset {1} rows fetch next {2} rows only ", orderBy, (PageIndex - 1) * PageSize, PageSize);
}
else
{
strSql.AppendFormat(" order by 1 offset {0} rows fetch next {1} rows only ", (PageIndex - 1) * PageSize, PageSize);//默认以第1个字段排序
}
//解释一下:offset N rows 跳过前N条,fetch next M rows only 获取下面的M条信息。
DataTable dt = ExecuteReader(strSql.ToString());
return dt;
}
/// <summary>
/// SQL分页查询
/// 该语法支持SQL Server2012及以上版本
/// </summary>
/// <param name="sqlStr">sql语句</param>
/// <param name="orderBy">排序</param>
/// <param name="PageSize">每页行数</param>
/// <param name="PageIndex">第几页</param>
/// <returns></returns>
public DataTable PAGING_2012(string sqlStr, string orderBy = "", int PageSize = 500, int PageIndex = 1)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(sqlStr);
if (!string.IsNullOrWhiteSpace(orderBy))
{
strSql.AppendFormat(" order by {0} offset {1} rows fetch next {2} rows only ", orderBy, (PageIndex - 1) * PageSize, PageSize);
}
else
{
strSql.AppendFormat(" order by 1 offset {0} rows fetch next {1} rows only ", (PageIndex - 1) * PageSize, PageSize);//默认以第1个字段排序
}
//解释一下:offset N rows 跳过前N条,fetch next M rows only 获取下面的M条信息。
DataTable dt = ExecuteReader(strSql.ToString());
return dt;
}
/// <summary>
/// DataTable分页查询
/// </summary>
/// <param name="data">DataTable</param>
/// <param name="allPage">一共有几页</param>
/// <param name="PageSize">每页几行数据</param>
/// <param name="PageIndex">第几页</param>
/// <returns></returns>
public DataTable GetDataTablePage(DataTable data, out int allPage, int PageSize = 500, int PageIndex = 1)
{
allPage = data.Rows.Count / PageSize;
allPage += data.Rows.Count % PageSize == 0 ? 0 : 1;
DataTable dt = data.Clone();
int startIndex = PageIndex * PageSize;
int endIndex = startIndex + PageSize > data.Rows.Count ? data.Rows.Count : startIndex + PageSize;
if (startIndex < endIndex)
{
for (int i = startIndex; i < endIndex; i++)
{
dt.ImportRow(data.Rows[i]);
}
}
return dt;
}
/// <summary>
/// 获取最大ID
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="idRow">主键的列名</param>
/// <param name="addOne">是否+1</param>
/// <returns></returns>
public int GetMaxID(string tableName, string idRow, bool addOne = false)
{
int i = 0;
string sql = "SELECT ISNULL(MAX([" + idRow + "]),0) FROM [" + tableName + "]";
object obj = ExecuteScalar(sql);
int.TryParse(obj.ToString(), out i);
if (addOne == true)//+1
{
return i + 1;
}
return i;
}
#endregion
}
}
创建SqlHelper子类,继承父类,仅仅给父类提供数据库链接字符串:
using System.Configuration;
public class TestSqlHelper : SqlHelperBase//①继承父类SqlHelperBase
{
public TestSqlHelper()//②创建构造函数
{
base.ConnStr = ConfigurationManager.ConnectionStrings["TianmaoData"].ConnectionString;//③改写父类连接字符串
}
}
调用子类:
public ActionResult Test()
{
string sql = "SELECT COUNT(*) cc FROM [TableName]";
TestSqlHelper ts = new TestSqlHelper();
ViewBag.COUNT = ts.COUNT(sql);
return View();
}
说明:
- 父类不可有 static,静态的不会被子类继承,值永远是null;
- 子类在“构造函数”里修改父类的变量值;
- 子类修改父类的值用:base.父类的参数=新值;
- 举例:base.ConnStr = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;