/// <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
}