namespace Saga.LiveChain.SA.DataAccess
{
using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Configuration;
// using Saga.LivePortal.SystemFramework.LogManager;
// using Saga.LivePortal.SystemFramework.ExceptionManager;
/// <summary>
/// Structure of Connection parameter
/// </summary>
public struct SConnParameterSql
{
/// <summary>
/// Set up DataSet.
/// </summary>
public DataSet ds;
/// <summary>
/// Table name in this dataset.
/// </summary>
public string MappingTableName;
/// <summary>
/// Primary key for this table.
/// </summary>
public DataColumn[] primarykey;
/// <summary>
/// Command Text for the Connection.
/// </summary>
public string commandtext;
/// <summary>
/// Command Typefor the Connection.
/// </summary>
public CommandType commandtype;
/// <summary>
/// Parameter the Connection
/// </summary>
public SqlParameter[] parameters;
}
/// <summary>
/// General method of connect database
/// </summary>
public class DBCommonSQL
{
private SqlConnection connect = new SqlConnection();
private SqlCommand command = new SqlCommand();
private SqlDataAdapter da = new SqlDataAdapter();
private SqlTransaction transaction = null;
/// <summary>
/// Initialize the connetion
/// </summary>
public DBCommonSQL()
{
connect = new SqlConnection(this.ConnectionString);
command.Connection = (SqlConnection)connect;
da = new SqlDataAdapter(command);
}
/*
* 第二个连接串,
* 没有参数是构造第一个
* 有参数时构造第二个
public DBCommonSQL(string DB)
{
if (DB=="BBS")
{
connect = new SqlConnection(this.ConnectionStringBBS);
command.Connection = (SqlConnection)connect;
da = new SqlDataAdapter(command);
}
}
*/
/// <summary>
/// Get the strong-type dataset
/// </summary>
/// <param name="connParameter"></param>
/// <returns>DataSet</returns>
internal DataSet GetStrongTypeDataSet(SConnParameterSql connParameter)
{
//if the provided connection is not open, we will open it
try
{
if (connect.State != ConnectionState.Open)
{
connect.Open();
}
command.CommandText = connParameter.commandtext;
command.CommandType = connParameter.commandtype;
// connParameter.ds.Tables[connParameter.MappingTableName].PrimaryKey = connParameter.primarykey;
command.Parameters.Clear();
if (connParameter.parameters != null)
{
foreach (SqlParameter parameter in connParameter.parameters)
{
command.Parameters.Add(parameter);
}
}
da.SelectCommand = command;
//da.FillSchema(connParameter.ds, SchemaType.Mapped, connParameter.MappingTableName);
da.Fill(connParameter.ds, connParameter.MappingTableName);
connParameter.ds.RemotingFormat = SerializationFormat.Binary;
return connParameter.ds;
}
catch (Exception e)
{
CatchException(e);
return null;
}
finally
{
command.Dispose();
da.Dispose();
if (connect.State == ConnectionState.Open)
{
connect.Close();
}
}
}
/// <summary>
/// Get the DataTable
/// </summary>
/// <param name="connParameter"></param>
/// <returns>DataSet</returns>
internal DataTable GetDataTable(SConnParameterSql connParameter)
{
//if the provided connection is not open, we will open it
try
{
if (connect.State != ConnectionState.Open)
{
connect.Open();
}
command.CommandText = connParameter.commandtext;
command.CommandType = connParameter.commandtype;
command.Parameters.Clear();
if (connParameter.parameters != null)
{
foreach (SqlParameter parameter in connParameter.parameters)
{
command.Parameters.Add(parameter);
}
}
da.SelectCommand = command;
da.Fill(connParameter.ds);
return connParameter.ds.Tables[0];
}
catch (Exception e)
{
CatchException(e);
return null;
}
finally
{
command.Dispose();
da.Dispose();
if (connect.State == ConnectionState.Open)
{
connect.Close();
}
}
}
/// <summary>
/// Get DataReader
/// </summary>
/// <param name="connParameter"></param>
/// <returns>IDataReader</returns>
internal IDataReader GetDataReader(SConnParameterSql connParameter)
{
//if the provided connection is not open, we will open it
try
{
if (connect.State != ConnectionState.Open)
{
connect.Open();
}
command.CommandText = connParameter.commandtext;
command.CommandType = connParameter.commandtype;
SqlDataReader dr = null;
command.Parameters.Clear();
if (connParameter.parameters != null)
{
foreach (SqlParameter parameter in connParameter.parameters)
{
command.Parameters.Add(parameter);
}
}
dr = command.ExecuteReader(CommandBehavior.CloseConnection);
GetOutParameters(connParameter.parameters, command);
return dr;
}
catch (Exception e)
{
CatchException(e);
return null;
}
finally
{
command.Dispose();
if (connect.State == ConnectionState.Open)
{
connect.Close();
}
}
}
/// <summary>
/// operate the database by StoredProcedure with update,deleteal,add
/// </summary>
/// <param name="connParameter"></param>
internal void EditTableWithReturnValue(SConnParameterSql connParameter)
{
EditTable(connParameter, true);
}
/// <summary>
/// operate the database by StoredProcedure with update,deleteal,add
/// </summary>
/// <param name="connParameter"></param>
internal void EditTableNoReturnValue(SConnParameterSql connParameter)
{
EditTable(connParameter, false);
}
/// <summary>
/// operate the database by StoredProcedure with update,deleteal,add
/// </summary>
/// <param name="connParameter"></param>
/// <param name="isReturnValue"></param>
private void EditTable(SConnParameterSql connParameter, bool isReturnValue)
{
try
{
command.CommandText = connParameter.commandtext;
command.CommandType = connParameter.commandtype;
//Add parameters values
command.Parameters.Clear();
if (connParameter.parameters != null)
{
foreach (SqlParameter parameter in connParameter.parameters)
{
command.Parameters.Add(parameter);
}
}
//if the provided connection is not open, we will open it
if (connect.State != ConnectionState.Open)
{
connect.Open();
}
//Commit Value
command.ExecuteNonQuery();
//Get return parameters va
if (isReturnValue)
GetOutParameters(connParameter.parameters, command);
}
catch (Exception e)
{
CatchException(e);
}
finally
{
command.Dispose();
if (connect.State == ConnectionState.Open)
{
connect.Close();
}
}
}
/// <summary>
/// catch exception
/// </summary>
/// <param name="ex"></param>
internal void CatchException(Exception ex)
{
//throw ex;
//System.Diagnostics.Debug.Write(ex);
try
{
// AppService.ProcessException(ex);
}
catch
{
//LogManager lm = new LogManager();
// lm.FillLogEntry(1000, 5, ex.Message, LogManager.Category.DataAccessEvents, TraceEventType.Error);
// lm.DoLogging();
// throw ex2;
}
}
internal int ExecuteScalar(SConnParameterSql connParameter)
{
//if the provided connection is not open, we will open it
try
{
if (connect.State != ConnectionState.Open)
{
connect.Open();
}
command.CommandText = connParameter.commandtext;
command.CommandType = connParameter.commandtype;
command.Parameters.Clear();
if (connParameter.parameters != null)
{
foreach (SqlParameter parameter in connParameter.parameters)
{
command.Parameters.Add(parameter);
}
}
da.SelectCommand = command;
return (int)command.ExecuteScalar();
}
catch (Exception e)
{
CatchException(e);
return 0;
}
finally
{
command.Dispose();
da.Dispose();
if (connect.State == ConnectionState.Open)
{
connect.Close();
}
}
}
#region Get output parameters values
/// <summary>
/// Get output parameters values
/// </summary>
/// <param name="parameters"></param>
/// <param name="cmd"></param>
private void GetOutParameters(SqlParameter[] parameters, SqlCommand cmd)
{
foreach (SqlParameter p in parameters)
{
if (p.Direction == ParameterDirection.Output ||
p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.ReturnValue)
{
p.Value = cmd.Parameters[p.ParameterName].Value;
}
}
}
#endregion
#region Connection String
/// <summary>
/// Get the Connection String
/// </summary>
public string ConnectionString
{
get
{
// return System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
return ConfigurationSettings.AppSettings["ConnectionString"];
}
}
public string ConnectionStringBBS
{
get
{
// return System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
return ConfigurationSettings.AppSettings["ConnectionStringBBS"];
}
}
#endregion
#region Begin, Commit, Rollback Transactions, Close Conn
/// <summary>
/// Begin transaction
/// </summary>
public void BeginTransaction()
{
if (transaction != null)
{
throw new System.NotSupportedException("Does not support nested transaction.");
}
if (connect.State != ConnectionState.Open)
{
connect.Open();
}
transaction = connect.BeginTransaction();
}
/// <summary>
/// Commit Transaction
/// </summary>
public void CommitTransaction()
{
if (null == transaction)
{
//throw new DBAccess.TransactionObjectInvalidException();
}
transaction.Commit();
transaction.Dispose();
transaction = null;
connect.Close();
}
/// <summary>
/// Rollback Transaction
/// </summary>
public void RollbackTransaction()
{
if (null == transaction)
{
//throw new DBAccess.TransactionObjectInvalidException();
}
transaction.Rollback();
transaction.Dispose();
transaction = null;
connect.Close();
}
/// <summary>
/// Close connection when transaction had been finnaly
/// </summary>
public void CloseConnForTransactionFinnaly()
{
if (connect.State != ConnectionState.Closed)
{
connect.Close();
}
}
#endregion
#region IDisposable members
/// <summary>
/// Dispose transaction
/// </summary>
public void Dispose()
{
if (transaction != null)
{
transaction.Dispose();
}
}
#endregion
}
}