//--名称：SQL数据库访问基类
//--功能：一般ADO.NET原理，数据装箱坼箱及通用数据库控件绑定
//--背景：针对一些个人形式外包，要求快速开发的数据库中小型项目特写此类供页面直接调用
//--创建人：KingWei
//--创建日期：2010-02-20
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Web.UI.WebControls;
namespace KingWei
{
/// <summary>
/// 数据库访问基类(for SQL)
/// </summary>
public class DBHelper:IDisposable
{
#region 成员
private SqlConnection Conn = null;
private SqlTransaction tran = null;
/// <summary>
/// 事务标识
/// </summary>
public bool IsTran { get; set; }
#endregion
#region 构造函数,SqlConnection对象初始化
public DBHelper()
{
Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
}
public DBHelper(string ConnectionKey)
{
Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionKey].ConnectionString);
}
#endregion
#region 数据库事务
/// <summary>
/// 事务开始
/// </summary>
public void BeginTran()
{
OpenDB();
tran = Conn.BeginTransaction(IsolationLevel.ReadCommitted);
IsTran = true;
}
/// <summary>
/// 回滚事务
/// </summary>
public void RollbackTran()
{
tran.Rollback();
IsTran = false;
}
/// <summary>
/// 提交事务
/// </summary>
public void CommitTran()
{
tran.Commit();
IsTran = false;
}
#endregion
#region SqlParameter对象创建
private SqlParameter CreateSqlParameter(string paraName,DbType paraType,int paraSize, ParameterDirection paraDirection, object paraValue)
{
SqlParameter para = new SqlParameter();
para.DbType = paraType;
para.Direction = paraDirection;
para.ParameterName = paraName;
if (paraSize > 0)
{
para.Size = paraSize;
}
para.Value = paraValue;
return para;
}
public SqlParameter CreateInSqlParameter(string paraName, DbType paraType, object paraValue)
{
return CreateSqlParameter(paraName, paraType, 0, ParameterDirection.Input, paraValue);
}
public SqlParameter CreateInSqlParameter(string paraName, DbType paraType,int paraSize, object paraValue)
{
return CreateSqlParameter(paraName, paraType, paraSize, ParameterDirection.Input, paraValue);
}
public SqlParameter CreateOutSqlParameter(string paraName, DbType paraType, object paraValue)
{
return CreateSqlParameter(paraName, paraType, 0, ParameterDirection.Output, paraValue);
}
public SqlParameter CreateOutSqlParameter(string paraName, DbType paraType, int paraSize, object paraValue)
{
return CreateSqlParameter(paraName, paraType, paraSize, ParameterDirection.Output, paraValue);
}
#endregion
#region 常用ADO.NET方法
/// <summary>
/// OpenDB
/// </summary>
private void OpenDB()
{
if (Conn.State != ConnectionState.Open)
{
try
{
Conn.Open();
}
catch (SqlException ex)
{
throw ex;
}
}
}
/// <summary>
/// 初始化一个SqlCommand对象
/// </summary>
private void CreateCommand(SqlCommand cmd,CommandType cmdType, string cmdText, SqlParameter[] SqlParas)
{
if (IsTran)
{
cmd.Transaction = tran;
}
else
{
OpenDB();
}
cmd.Connection = Conn;
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;
if (SqlParas.Length > -1)
{
foreach (SqlParameter p in SqlParas)
{
cmd.Parameters.Add(p);
}
}
}
/// <summary>
/// 执行SQL返回一个DataSet
/// </summary>
public DataSet ExecuteQuery(CommandType cmdType,string cmdText,SqlParameter[] SqlParas)
{
using (SqlCommand cmd = new SqlCommand())
{
CreateCommand(cmd, cmdType, cmdText, SqlParas);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
}
/// <summary>
/// 执行SQL返回受影响的行数
/// </summary>
public int ExecuteNonQuery(CommandType cmdType, string cmdText, SqlParameter[] SqlParas)
{
using (SqlCommand cmd = new SqlCommand())
{
CreateCommand(cmd, cmdType, cmdText, SqlParas);
return cmd.ExecuteNonQuery();
}
}
/// <summary>
/// 重载一：执行SQL返回第一行第一列的值
/// </summary>
public object ExecuteScalar(CommandType cmdType, string cmdText, SqlParameter[] SqlParas)
{
using (SqlCommand cmd = new SqlCommand())
{
CreateCommand(cmd, cmdType, cmdText, SqlParas);
return cmd.ExecuteScalar();
}
}
/// <summary>
/// 重载二：执行SQL返回第一行第一列的值,可传参取代返回值为NULL的情况
/// </summary>
public string ExecuteScalar(CommandType cmdType, string cmdText, SqlParameter[] SqlParas,string WhenNull)
{
using (SqlCommand cmd = new SqlCommand())
{
CreateCommand(cmd, cmdType, cmdText, SqlParas);
object result = cmd.ExecuteScalar();
return result == null?WhenNull:result.ToString();
}
}
/// <summary>
/// 执行一段SQL，返回一个DataReader对象
/// </summary>
public SqlDataReader ExecuteDataReader(CommandType cmdType, string cmdText, SqlParameter[] SqlParas)
{
using (SqlCommand cmd = new SqlCommand())
{
CreateCommand(cmd, cmdType, cmdText, SqlParas);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
/// <summary>
/// 常用分页方法
/// </summary>
/// <param name="PageSize">页面大小</param>
/// <param name="RecordCount">记录总量</param>
/// <param name="CurruntPageIndex">当前位置</param>
/// <param name="TableName">表名/视图名</param>
/// <param name="Condition">查询条件</param>
/// <param name="IsAsc">是否升序排序</param>
/// <param name="OrderBy">按哪些字段排序</param>
/// <returns></returns>
private SqlDataReader GetPageSql(string condition, Int16 pageSize, Int16 pageIndex, string tbNames, string sortNames, bool sortType)
{
System.Text.StringBuilder PageSql = new System.Text.StringBuilder();
string tbname, tbsortname, type;
type = sortType ? "ASC" : "DESC";
tbname = tbNames.ToUpper().IndexOf("SELECT") >= 0 ? "(" + tbNames + ")" + " as DBHelper" : tbNames + " as DBHelper";
tbsortname = tbNames.ToUpper().IndexOf("SELECT") >= 0 ? "(" + tbNames + ") as DBHelperID" : tbNames + " as DBHelperID";
if (pageIndex == 1)
{
PageSql.Append("select top " + pageSize.ToString() + " DBHelper.* from " + tbname + (!string.IsNullOrEmpty(condition) ? " where " + condition : string.Empty) + " order by " + sortNames + " " + type);
}
else
{
PageSql.AppendFormat("Select top {0} DBHelper.* from ", pageSize);
PageSql.AppendFormat("{0}", tbname);
PageSql.AppendFormat(" where DBHelper.{0} not in(select top {1} DBHelperID.{0}",
sortNames.Substring(sortNames.LastIndexOf(",") + 1, sortNames.Length - sortNames.LastIndexOf(",") - 1),
pageSize * (pageIndex - 1));
PageSql.AppendFormat(" from {0}", tbsortname);
if (!string.IsNullOrEmpty(condition))
{
PageSql.AppendFormat(" where {0} order by {1} {2}) and {0}", condition, sortNames, type);
}
else
{
PageSql.AppendFormat(" order by {0} {1})", sortNames, type);
}
PageSql.AppendFormat(" order by {0} {1}", sortNames, type);
}
return ExecuteDataReader(CommandType.Text, PageSql.ToString(), null);
}
/// <summary>
/// 手动关闭数据库连接对象
/// </summary>
public void CloseDB()
{
if (!object.Equals(Conn, null) && Conn.State != ConnectionState.Closed)
{
Conn.Close();
}
}
#endregion
#region 数据类型转换
public string ToStr(object obj)
{
if (object.Equals(obj, DBNull.Value) || string.IsNullOrEmpty(obj.ToString()))
return "";
else
return obj.ToString();
}
public int ToInt(object obj)
{
if (object.Equals(obj,DBNull.Value)||object.Equals(obj, null) || string.IsNullOrEmpty(obj.ToString()))
return 0;
else
return Convert.ToInt32(obj);
}
public Int16 ToInt16(object obj)
{
if (object.Equals(obj, DBNull.Value) || object.Equals(obj, null) || string.IsNullOrEmpty(obj.ToString()))
return 0;
else
return Convert.ToInt16(obj);
}
public double ToDouble(object obj)
{
if (object.Equals(obj, DBNull.Value) || object.Equals(obj, null) || string.IsNullOrEmpty(obj.ToString()))
return 0;
else
return Convert.ToDouble(obj);
}
public Single ToSingle(object obj)
{
if (object.Equals(obj, DBNull.Value) || object.Equals(obj, null) || string.IsNullOrEmpty(obj.ToString()))
return 0;
else
return Convert.ToSingle(obj);
}
public bool ToBool(object obj)
{
if (object.Equals(obj, DBNull.Value) || object.Equals(obj, null))
return false;
else
return Convert.ToBoolean(obj);
}
public DateTime ToDateTime(object obj)
{
try
{
DateTime dt;
DateTime.TryParse(Convert.ToString(obj), out dt);
return dt;
}
catch
{
return DateTime.MinValue;
}
}
public DateTime? ToNullDate(object obj)
{
if (object.Equals(obj, DBNull.Value))
return null;
else
try
{
DateTime dt;
DateTime.TryParse(Convert.ToString(obj), out dt);
return dt;
}
catch
{
return null;
}
}
public int? ToNullInt(object obj)
{
if (object.Equals(obj, DBNull.Value) || object.Equals(obj, null) || string.IsNullOrEmpty(obj.ToString()))
return null;
else
return Convert.ToInt32(obj);
}
public Int16? ToNullInt16(object obj)
{
if (object.Equals(obj, DBNull.Value) || object.Equals(obj, null) || string.IsNullOrEmpty(obj.ToString()))
return null;
else
return Convert.ToInt16(obj);
}
public double? ToNulldouble(object obj)
{
if (object.Equals(obj, DBNull.Value) || object.Equals(obj, null) || string.IsNullOrEmpty(obj.ToString()))
return null;
else
return Convert.ToDouble(obj);
}
public Single? ToNullSingle(object obj)
{
if (object.Equals(obj, DBNull.Value) || object.Equals(obj, null) || string.IsNullOrEmpty(obj.ToString()))
return null;
else
return Convert.ToSingle(obj);
}
#endregion
#region 常用控件数据绑定
public enum SelType
{
ByValue,
ByText
}
/// <summary>
/// 列表型数据控件绑定
/// </summary>
public void ListBind(ListControl LstCtrl,object Lst)
{
LstCtrl.DataSource = Lst;
LstCtrl.DataBind();
}
/// <summary>
/// 绑定GridView
/// </summary>
public void GrdBind(GridView grdView, object Lst)
{
grdView.DataSource = Lst;
grdView.DataBind();
}
/// <summary>
/// 绑定GridView，并为指定的一列加上序号
/// </summary>
public void GrdBind(GridView grdView, object Lst, int InsertNo)
{
GrdBind(grdView, Lst);
for (int i = 0; i < grdView.Rows.Count; i++)
{
grdView.Rows[i].Cells[InsertNo].Text = (i + 1).ToString();
}
}
/// <summary>
/// 绑定DropDownList
/// </summary>
public void DdlBind(DropDownList ddlList, object Lst)
{
ddlList.DataSource = Lst;
ddlList.DataBind();
}
/// <summary>
/// 绑定DropDownList,指定文本及值的绑定项
/// </summary>
public void DdlBind(DropDownList ddlList, Object Lst, string TextField, string ValueField)
{
ddlList.DataSource = ddlList;
ddlList.DataTextField = TextField;
ddlList.DataValueField = ValueField;
ddlList.DataBind();
}
/// <summary>
/// 绑定DropDownList,指定文本及值的绑定项，插入一个名为defaultStr的默认项
/// </summary>
public void DdlBind(DropDownList ddlList, Object Lst, string TextField, string ValueField, string defaultStr)
{
DdlBind(ddlList, Lst, TextField, ValueField);
ddlList.Items.Insert(0, defaultStr);
}
/// <summary>
/// 绑定DropDownList,指定文本及值的绑定项，使DropDownList选择默认的值
/// </summary>
public void DdlBind(DropDownList ddlList, Object Lst, string TextField, string ValueField,SelType FindType, string FindStr)
{
DdlBind(ddlList, Lst, TextField, ValueField);
int selectIndex = -1;
for (int i = 0; i < ddlList.Items.Count; i++)
{
switch (FindType)
{
case SelType.ByText:
if (ddlList.Items[i].Text == FindStr)
{
selectIndex= i;
}
break;
case SelType.ByValue:
if (ddlList.Items[i].Value == FindStr)
{
selectIndex = i;
}
break;
}
if (selectIndex > -1)
{
ddlList.SelectedIndex = selectIndex;
break;
}
}
}
#endregion
#region IDisposable 成员
public void Dispose()
{
if(Conn != null)
Conn.Dispose();
if (tran != null)
tran.Dispose();
}
#endregion
}
}