通用数据访问类
/// <summary>
/// Summary description for BaseBLL.
/// </summary>
public class BaseBLL
{
private string connectionString = "";
private int timeout = 180;
private string connectionKey = "HUANG_SQLCONNECTION";
private string transactionKey = "HUANG_SQLTRANSACTION";
public BaseBLL()
{
connectionString = ConfigurationSettings.AppSettings["connectionString"];
timeout = int.Parse(ConfigurationSettings.AppSettings["timeout"]);
}
#region Properties
private SqlConnection Connection
{
get
{
//System.Web.HttpContext.Current.Response.Write(HttpContext.Current.Session.SessionID + "<br/>");
SqlConnection connection = (SqlConnection)DataCache.GetSession(connectionKey);
if (connection == null)
{
DataCache.SetSession(connectionKey, new SqlConnection(connectionString));
}
connection = (SqlConnection)DataCache.GetSession(connectionKey);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
return connection;
}
set
{
DataCache.SetSession(connectionKey, value);
}
}
private SqlTransaction Transaction
{
get { return (SqlTransaction)DataCache.GetSession(transactionKey); }
set { DataCache.SetSession(transactionKey, value); }
}
#endregion
#region ExecuteNonQuery
protected int ExecuteNonQuery(SqlCommand command)
{
// 当没有开启事务时，执行之后关闭连接，否则在提交事务或者回滚事务中关闭连接。
bool closeConnection = true;
try
{
command.Connection = Connection;
command.CommandTimeout = timeout;
SqlTransaction transaction = Transaction;
if (transaction != null)
{
command.Transaction = transaction;
closeConnection = false;
}
return command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
command.Parameters.Clear();
if (closeConnection && command.Connection.State == ConnectionState.Open)
{
command.Connection.Close();
}
}
}
#endregion
#region ExecuteScalar
protected object ExecuteScalar(SqlCommand command)
{
// 当没有开启事务时，执行之后关闭连接，否则在提交事务或者回滚事务中关闭连接。
bool closeConnection = true;
try
{
command.Connection = Connection;
command.CommandTimeout = timeout;
SqlTransaction transaction = Transaction;
if (transaction != null)
{
command.Transaction = transaction;
closeConnection = false;
}
return command.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
command.Parameters.Clear();
if (closeConnection && command.Connection.State == ConnectionState.Open)
{
command.Connection.Close();
}
}
}
#endregion
#region ExecuteReader
public SqlDataReader ExecuteReader(string commandText, CommandBehavior behavior)
{
SqlConnection conn = new SqlConnection(connectionString);
using (SqlCommand command = new SqlCommand(commandText, conn))
{
command.CommandTimeout = timeout;
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
try
{
return command.ExecuteReader(behavior);
}
catch
{
throw;
}
}
}
protected SqlDataReader ExecuteReader(SqlCommand command)
{
try
{
SqlConnection conn = new SqlConnection(connectionString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
command.Connection = conn;
command.CommandTimeout = timeout;
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
command.Parameters.Clear();
return reader;
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region ExecuteDataSet
protected DataSet ExecuteDataSet(SqlCommand command)
{
command.Connection = Connection;
command.CommandTimeout = timeout;
SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();
da.Fill(ds);
command.Parameters.Clear();
// 关闭连接
if (Connection.State == ConnectionState.Open)
{
Connection.Close();
}
return ds;
}
public DataTable ExecuteDataTable(string sql, int pageNo, int pageSize, out int pageCount, out int rowCount)
{
string commandText = @"
DECLARE @p1 INT,@RowCount INT,@PageCount INT
EXEC sp_cursoropen
@cursor = @p1 OUTPUT,
@stmt = @sql,
@scrollopt = 1,
@ccopt = 1,
@rowcount = @RowCount OUTPUT
IF @PageSize = -1
BEGIN
-- 显示页码
SELECT 1 AS [PageCount], @RowCount AS [RowCount]
EXEC(@sql)
END
ELSE
BEGIN
SET @PageNo = @PageNo + 1
-- 计算总页数
IF @PageSize < 1
SET @PageSize = 10
SET @PageCount = (@RowCount + @PageSize - 1) / @PageSize
IF ISNULL(@PageNo, 0) < 1
SET @PageNo = 1
ELSE IF ISNULL(@PageNo, 0) > @PageCount
SET @PageNo = @PageCount
SET @PageNo = (@PageNo - 1) * @PageSize + 1
-- 显示页码
SELECT @PageCount AS [PageCount], @RowCount AS [RowCount]
-- 显示指定页的数据
EXEC sp_cursorfetch @p1, 16, @PageNo, @PageSize
END
--关闭分页游标
EXEC sp_cursorclose @p1
";
SqlCommand cmd = new SqlCommand(commandText, Connection);
cmd.Parameters.Add(new SqlParameter("@sql", sql));
cmd.Parameters.Add(new SqlParameter("@PageNo", pageNo));
cmd.Parameters.Add(new SqlParameter("@PageSize", pageSize));
DataSet ds = ExecuteDataSet(cmd);
rowCount = (Int32)ds.Tables[1].Rows[0]["RowCount"];
pageCount = (Int32)ds.Tables[1].Rows[0]["PageCount"];
return ds.Tables[2];
}
public DataSet ExecuteDataSet(string sql, int startRecord, int maxRecords, string srcTable)
{
return ExecuteDataSet(new SqlCommand(sql), startRecord, maxRecords, srcTable);
}
protected DataSet ExecuteDataSet(SqlCommand command, int startRecord, int maxRecords, string srcTable)
{
command.Connection = Connection;
command.CommandTimeout = timeout;
SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();
int count = da.Fill(ds, startRecord, maxRecords, srcTable);
command.Parameters.Clear();
// 关闭连接
if (Connection.State == ConnectionState.Open)
{
Connection.Close();
}
return ds;
}
#endregion ExecuteDataSet
#region CRUD
/// <summary>
/// 返回总行数
/// </summary>
/// <param name="table"></param>
/// <param name="where"></param>
/// <returns></returns>
public int GetRowCounts(string table, string where)
{
string sql = string.Format("SELECT COUNT(1) FROM {0} WHERE {1}", table, where);
return (int)ExecuteScalar(new SqlCommand(sql));
}
/// <summary>
/// 新建一行
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public DataRow NewDataRow(string table)
{
string sql = string.Format("SELECT * FROM {0} WHERE 1=2", table);
DataSet ds = ExecuteDataSet(new SqlCommand(sql));
if (ds.Tables.Count > 0)
return ds.Tables[0].NewRow();
else
throw new Exception("Cann't new a datarow.");
}
private DataTable GeCacheSchema(string key)
{
DataTable dt = (DataTable)DataCache.GetCache(key + "_Schema");
if (dt == null)
{
string sql = "select top 1 * from [" + key + "]";
SqlDataReader reader = ExecuteReader(sql, CommandBehavior.CloseConnection | CommandBehavior.KeyInfo);
dt = reader.GetSchemaTable();
reader.Close();
DataCache.SetCache(key + "_Schema", dt);
}
return dt;
}
/// <summary>
/// 增加
/// </summary>
/// <param name="tableName"></param>
/// <param name="row"></param>
/// <returns></returns>
public int Create(string tableName, DataRow row)
{
StringBuilder sb1 = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
sb1.Append("INSERT INTO [" + tableName + "](");
sb2.Append("VALUES(");
DataTable dt = GeCacheSchema(tableName);
bool isIdentity;
string baseName;
object data;
ArrayList parameters = new ArrayList();
foreach (DataRow dr in dt.Rows)
{
isIdentity = Convert.ToBoolean(dr["IsIdentity"]);
baseName = Convert.ToString(dr["BaseColumnName"]);
data = row[baseName];
if (data != DBNull.Value && !isIdentity)
{
if (sb1[sb1.Length - 1] == '(')
{
sb1.Append("[" + baseName + "]");
sb2.Append("@" + baseName);
}
else
{
sb1.Append(", [" + baseName + "]");
sb2.Append(", @" + baseName);
}
parameters.Add(new SqlParameter("@" + baseName, data));
}
}
sb1.Append(")");
sb2.Append(")");
sb1.Append(" " + sb2.ToString());
try
{
SqlCommand command = new SqlCommand(sb1.ToString());
command.CommandTimeout = timeout;
foreach (SqlParameter p in parameters)
{
command.Parameters.Add(p);
}
return ExecuteNonQuery(command);
}
catch
{
throw;
}
}
/// <summary>
/// 检索
/// </summary>
/// <param name="table"></param>
/// <param name="key"></param>
/// <param name="values"></param>
/// <returns></returns>
public DataTable Retrieve(string table, string key, object[] values)
{
return Retrieve(table, key, values, "");
}
public DataTable Retrieve(string table, string key, object[] values, string sort)
{
return Retrieve(table, key, values, "*", sort);
}
public DataTable Retrieve(string table, string key, object[] values, string columns, string sort)
{
key = key.ToLower();
StringBuilder sb1 = new StringBuilder();
string head = string.Format("SELECT {1} FROM {0} WHERE 1=1 ", table, (columns == null || columns.Trim() == "") ? "*" : columns);
sb1.Append(head);
ArrayList parameters = new ArrayList();
string[] ss = key.Split(',');
for(int i =0; i < ss.Length; i ++)
{
string str = ss[i].Trim();
if(str == "") continue;
sb1.Append(string.Format("AND [{0}]=@{1} ", str, str));
parameters.Add(new SqlParameter("@" + str, values[i]));
}
if (sort.Trim() != "") sb1.Append(" order by " + sort.Trim());
SqlCommand command = new SqlCommand(sb1.ToString());
foreach (SqlParameter p in parameters)
{
command.Parameters.Add(p);
}
DataSet ds = ExecuteDataSet(command);
if (ds != null && ds.Tables.Count > 0)
return ds.Tables[0];
else
return null;
}
/// <summary>
/// 修改
/// </summary>
/// <param name="table"></param>
/// <param name="key"></param>
/// <param name="row"></param>
/// <returns></returns>
public int Update(string table, string key, DataRow row)
{
key = key.ToLower();
StringBuilder sb1 = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
string head = string.Format("UPDATE [{0}] SET ", table);
sb1.Append(head);
ArrayList parameters = new ArrayList();
foreach (DataColumn dc in row.Table.Columns)
{
string name = dc.ColumnName.ToLower();
if (row[name] != DBNull.Value)
{
if (key.IndexOf(name) != -1)
{
if (sb2.Length == 0)
{
sb2.Append(" WHERE [" + name + "] = @" + name);
}
else
{
sb2.Append(" AND [" + name + "] = @" + name);
}
}
else
{
if (sb1.ToString() == head)
{
sb1.Append("[" + name + "] = " + "@" + name);
}
else
{
sb1.Append(", [" + name + "] = " + "@" + name);
}
}
SqlParameter param = new SqlParameter("@" + name, row[name]);
parameters.Add(param);
}
}
sb1.Append(sb2.ToString());
SqlCommand command = new SqlCommand(sb1.ToString());
foreach (SqlParameter p in parameters)
{
command.Parameters.Add(p);
}
return ExecuteNonQuery(command);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="table"></param>
/// <param name="key"></param>
/// <param name="values"></param>
/// <returns></returns>
public int Delete(string table, string key, object[] values)
{
key = key.ToLower();
StringBuilder sb1 = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
string head = string.Format("DELETE FROM {0} WHERE 1=1 ", table);
sb1.Append(head);
ArrayList parameters = new ArrayList();
string[] ss = key.Split(',');
for(int i =0; i < ss.Length; i ++)
{
string str = ss[i].Trim();
if(str == "") continue;
sb2.Append(string.Format("AND [{0}]=@{1} ", str, str));
parameters.Add(new SqlParameter("@" + str, values[i]));
}
if (sb2.Length == 0) return 0;
SqlCommand command = new SqlCommand(sb1.Append(sb2.ToString()).ToString());
foreach (SqlParameter p in parameters)
{
command.Parameters.Add(p);
}
return ExecuteNonQuery(command);
}
#endregion
#region Transaction
/// <summary>
/// Begin transaction
/// </summary>
public void BeginTransaction()
{
SqlConnection connection = Connection;
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
SqlTransaction transaction = Transaction;
if (transaction == null)
{
transaction = connection.BeginTransaction();
}
Connection = connection;
Transaction = transaction;
}
/// <summary>
/// Commit transaction
/// </summary>
public void Commit()
{
SqlConnection connection = Connection;
SqlTransaction transaction = Transaction;
if (transaction != null)
{
transaction.Commit();
transaction.Dispose();
transaction = null;
Transaction = null;
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
/// <summary>
/// Rollback transaction
/// </summary>
public void Rollback()
{
SqlConnection connection = Connection;
SqlTransaction transaction = Transaction;
if (transaction != null)
{
transaction.Rollback();
transaction.Dispose();
transaction = null;
Transaction = null;
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
#endregion
}
