/**/
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public void ExecuteSqlTran(Hashtable SQLStringList)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
iConn.Open();
using (IDbTransaction iTrans = iConn.BeginTransaction())
{
System.Data.IDbCommand iCmd = GetCommand();
try
{
//循环
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
IDataParameter[] iParms = (IDataParameter[])myDE.Value;
PrepareCommand(out iCmd, iConn, iTrans, cmdText, iParms);
int val = iCmd.ExecuteNonQuery();
iCmd.Parameters.Clear();
}
iTrans.Commit();
}
catch
{
iTrans.Rollback();
throw;
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/**/
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public object GetSingle(string SQLString, params IDataParameter[] iParms)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
System.Data.IDbCommand iCmd = GetCommand();
{
try
{
PrepareCommand(out iCmd, iConn, null, SQLString, iParms);
object obj = iCmd.ExecuteScalar();
iCmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return(null);
}
else
{
return(obj);
}
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
protected void Disconnect()
{
// Disconnect can be called from Dispose and should guarantee no errors
if (!m_bConnected)
{
return;
}
if (m_oTransaction != null)
{
RollbackTransaction(false);
}
if (m_oCommand != null)
{
m_oCommand.Dispose();
m_oCommand = null;
}
if (m_oConnection != null)
{
try
{
m_oConnection.Close();
}
catch
{
}
m_oConnection.Dispose();
m_oConnection = null;
}
m_bConnected = false;
}
public override IDataReader GetDataReader(System.Data.IDbCommand cmd)
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
try
{
cmd.Connection = this.connection;
if (isInTransaction)
{
cmd.Transaction = (OracleTransaction)transaction;
}
OracleDataReader reader = (OracleDataReader)cmd.ExecuteReader();
return(reader);
}
catch (Exception e)
{
throw new Exception("操作数据库失败！参考：" + e.Message);
}
finally
{
cmd.Dispose();
cmd = null;
}
}
/**/
/// <summary>
/// 执行查询语句，向XML文件写入数据
/// </summary>
/// <param name="sqlString">查询语句</param>
/// <param name="xmlPath">XML文件路径</param>
public void WriteToXml(string sqlString, string xmlPath)
{
Query(sqlString).WriteXml(xmlPath);
}
/**/
///// <summary>
///// 执行查询语句
///// </summary>
///// <param name="SqlString">查询语句</param>
///// <returns>DataTable </returns>
//public DataTable ExecuteQuery(string SqlString, string Proc)
//{
// using (System.Data.IDbConnection iConn = this.GetConnection())
// {
// using (System.Data.IDbCommand iCmd = GetCommand(SqlString, iConn))
// {
// iCmd.CommandType = CommandType.StoredProcedure;
// DataSet ds = new DataSet();
// try
// {
// System.Data.IDataAdapter iDataAdapter = this.GetAdapater(SqlString, iConn);
// iDataAdapter.Fill(ds);
// }
// catch (System.Exception e)
// {
// throw new Exception(e.Message);
// }
// finally
// {
// if (iConn.State != ConnectionState.Closed)
// {
// iConn.Close();
// }
// }
// return ds.Tables[0];
// }
// }
//}
/**/
/// <summary>
///
/// </summary>
/// <param name="Sql"></param>
/// <returns></returns>
public DataView ExeceuteDataView(string Sql)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
using (System.Data.IDbCommand iCmd = GetCommand(Sql, iConn))
{
DataSet ds = new DataSet();
try
{
System.Data.IDataAdapter iDataAdapter = this.GetAdapater(Sql, iConn);
iDataAdapter.Fill(ds);
return(ds.Tables[0].DefaultView);
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
#endregion
#region 执行带参数的SQL语句
/**/
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SQLString, params IDataParameter[] iParms)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
System.Data.IDbCommand iCmd = GetCommand();
{
try
{
PrepareCommand(out iCmd, iConn, null, SQLString, iParms);
int rows = iCmd.ExecuteNonQuery();
iCmd.Parameters.Clear();
return(rows);
}
catch (System.Exception E)
{
throw new Exception(E.Message);
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
public override int DoCommand(System.Data.IDbCommand cmd)
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
try
{
int result = 0;
if (isInTransaction)
{
cmd.Transaction = transaction;
}
cmd.Connection = connection;
result = cmd.ExecuteNonQuery();
return(result);
}
catch (Exception e)
{
throw new Exception("操作数据库失败！参考：" + e.Message);
}
finally
{
if (connection.State != ConnectionState.Closed && isInTransaction == false)
{
connection.Close();
}
cmd.Dispose();
cmd = null;
}
}
private void Dispose(bool disposing)
{
if (disposed)
{
return;
}
if (disposing)
{
if (comm != null)
{
comm.Dispose();
}
if (conn != null)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
}
conn = null;
factory = null;
comm = null;
}
disposed = true;
}
public override int InsertRecord(System.Data.IDbCommand cmd)
{
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
if (isInTransaction)
{
cmd.Transaction = (OracleTransaction)transaction;
}
cmd.Connection = connection;
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT @@IDENTITY";
object obj = cmd.ExecuteScalar();
return(Convert.ToInt32(obj == DBNull.Value ? 0 : obj));
}
catch (Exception e)
{
throw new Exception("操作数据库失败！参考：" + e.Message);
}
finally
{
if (connection.State != ConnectionState.Closed && isInTransaction == false)
{
connection.Close();
}
cmd.Dispose();
cmd = null;
}
}
/// <summary>
/// 원재료 재고(CommodityCode = 1) 가 있는 거래처정보
/// 주의: 재질이 동일할때만
/// </summary>
/// <returns></returns>
public DataSet Get원재료재고공급업체()
{
System.Data.IDbCommand sqlSelectCommand = EnterpriseManager.DbFactory.CreateDbCommand();
// Sql Command 명령문
sqlSelectCommand.CommandText =
@"
select CustomerCode, CustomerName
from Customers
where CustomerCode in
(select distinct b.CustomerCode
from stocks a inner join PartSpecs b on a.PartCode = b.PartCode and a.PartSpec = b.PartSpec
where CommodityCode = 1 )
Order by CustomerName
";
sqlSelectCommand.CommandType = System.Data.CommandType.Text;
//
//
// 명령문을 호출한다
DataSet dataset = this.GetDataSet(sqlSelectCommand);
sqlSelectCommand.Dispose();
return(dataset);
}
/// <summary>
/// 执行查询语句，返回IDataReader
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns> IDataReader </returns>
public IDataReader ExecuteReader(string SQLString, params IDataParameter[] iParms)
{
System.Data.IDbConnection iConn = this.GetConnection();
{
System.Data.IDbCommand iCmd = GetCommand();
{
try
{
PrepareCommand(out iCmd, iConn, null, SQLString, iParms);
System.Data.IDataReader iReader = iCmd.ExecuteReader();
iCmd.Parameters.Clear();
return(iReader);
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
public static void Execute(
this System.Data.IDbCommand command)
{
LastCommand = command.CommandText;
command.ExecuteNonQuery();
command.Dispose();
}
public override object GetDataResult(System.Data.IDbCommand cmd)
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
try
{
object Result = null;
if (isInTransaction)
{
cmd.Transaction = transaction;
}
cmd.Connection = connection;
Result = cmd.ExecuteScalar();
return(Result);
}
catch (Exception e)
{
throw new Exception("操作数据库失败！参考：" + e.Message);
}
finally
{
if (connection.State != ConnectionState.Closed && isInTransaction == false)
{
connection.Close();
}
cmd.Dispose();
cmd = null;
}
}
public void Dispose()
{
if (m_command != null)
{
try { m_command.Dispose(); }
finally { m_command = null; }
}
}
}//ExecuteQuery(string sql)
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="Conn">数据库连接对象</param>
/// <returns>返回受影响行数</returns>
static public int Execute(string sql, System.Data.Common.DbConnection Conn)
{
if (Conn == null)
{
DBClassHelper.ErrLog("DBClassHelper.Execute(string sql, System.Data.Common.DbConnection Conn):连接对象为空!");
//return 0;
}
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandTimeout = 180;
cmd.CommandText = sql;
try
{
var count = cmd.ExecuteNonQuery();
cmd.Dispose();
return(count);
}
catch (Exception ex)
{
cmd.Dispose();
if (ex.Message.Contains("死锁"))
{
WriteLog(ex.Message + " 再做列锁循环！Execute");
System.Threading.Thread.Sleep(200);
return(Execute(sql, Conn));
}
else
{
DBClassHelper.ErrLog("DBClassHelper.Execute(string sql, System.Data.Common.DbConnection Conn):" + ex.Message + "/nsql=" + sql);
return(0);
}
}
finally
{
Conn.Close();
}
}//Execute(string sql)
public static void ExecuteReader(
this System.Data.IDbCommand command,
DbReadHandler handle)
{
LastCommand = command.CommandText;
using (var reader = command.ExecuteReader()) {
if (handle != null)
{
handle(reader);
}
command.Dispose();
}
}
public override DataRow GetDataRow(System.Data.IDbCommand cmd)
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
cmd.Connection = this.connection; //添加连接
DataRow r;
OracleDataAdapter adapter = new OracleDataAdapter();
adapter.SelectCommand = (OracleCommand)cmd;
if (isInTransaction)
{
cmd.Transaction = (OracleTransaction)transaction;
}
try
{
DataTable dt = new DataTable();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
r = dt.Rows[0];
}
else
{
r = null;
}
return(r);
}
catch (Exception e)
{
throw new Exception("操作数据库失败！参考：" + e.Message);
}
finally
{
if (connection.State != ConnectionState.Closed && isInTransaction == false)
{
connection.Close();
}
cmd.Dispose();
cmd = null;
adapter.Dispose();
adapter = null;
}
}
public DataSet GetLocationName()
{
System.Data.IDbCommand sqlSelectCommand = EnterpriseManager.DbFactory.CreateDbCommand();
// Sql Command 명령문
sqlSelectCommand.CommandText =
@"select LocationNo, LocationName from location";
sqlSelectCommand.CommandType = System.Data.CommandType.Text;
//
//
// 명령문을 호출한다
DataSet dataset = this.GetDataSet(sqlSelectCommand);
sqlSelectCommand.Dispose();
return(dataset);
}
public DataSet GetCodeName()//@@@
{
System.Data.IDbCommand sqlSelectCommand = EnterpriseManager.DbFactory.CreateDbCommand();
// Sql Command 명령문
sqlSelectCommand.CommandText =
@"select CustomerCode, CustomerName from Customers";
sqlSelectCommand.CommandType = System.Data.CommandType.Text;
//
//
// 명령문을 호출한다
DataSet dataset = this.GetDataSet(sqlSelectCommand);
sqlSelectCommand.Dispose();
return(dataset);
}
public DataSet GetAllData율촌구매(string customerShortName)
{
System.Data.IDbCommand sqlSelectCommand = EnterpriseManager.DbFactory.CreateDbCommand();
// Sql Command 명령문
sqlSelectCommand.CommandText =
@"select CustomerCode, CustomerName, BusinessCode, President, BusinessItem, BusinessType, Phone, CustomerShortName from Customers where CustomerShortName like '%" + customerShortName + "%' and class4 = 1 or class5 = 1 order by CustomerCode ";
sqlSelectCommand.CommandType = System.Data.CommandType.Text;
//
//
// 명령문을 호출한다
DataSet dataset = this.GetDataSet(sqlSelectCommand);
sqlSelectCommand.Dispose();
return(dataset);
}
public DataSet GetFindByCustomerCode(string CustomerCode)
{
System.Data.IDbCommand sqlSelectCommand = EnterpriseManager.DbFactory.CreateDbCommand();
// Sql Command 명령문
sqlSelectCommand.CommandText =
@"select CustomerCode, CustomerName from Customers where customercode like '%" + CustomerCode + "%'";
sqlSelectCommand.CommandType = System.Data.CommandType.Text;
//
//
// 명령문을 호출한다
DataSet dataset = this.GetDataSet(sqlSelectCommand);
sqlSelectCommand.Dispose();
return(dataset);
}
public LocationSet Get구매창고()
{
// Sql Command 명령문
System.Data.IDbCommand sqlSelectCommand = EnterpriseManager.DbFactory.CreateDbCommand();
sqlSelectCommand.CommandText =
@"
select * from Location
where LocationType = 1 and CostCenterLocationFlag = 0
order by LocationNo
";
sqlSelectCommand.CommandType = System.Data.CommandType.Text;
//
//
// 명령문을 호출한다
LocationSet entitySet = ((LocationSet)(this.GetEntitySet(sqlSelectCommand, typeof(LocationSet))));
sqlSelectCommand.Dispose();
return(entitySet);
}
public DataSet GetLocationFromNo(int locationNo)
{
//Sql Command 명령문
System.Data.IDbCommand sqlSelectCommand = EnterpriseManager.DbFactory.CreateDbCommand();
sqlSelectCommand.CommandText =
//@"select LocationNo, LocationName from location where LocationNo = @locationNo like '%" + locationNo + "%'";
@"select LocationNo, LocationName from location where LocationNo like '%" + locationNo + "%'";
sqlSelectCommand.CommandType = System.Data.CommandType.Text;
//System.Data.IDataParameter locationNoParam = EnterpriseManager.DbFactory.CreateDataParameter("@locationNo", typeof(int));
//locationNoParam.Value = locationNo;
//sqlSelectCommand.Parameters.Add(locationNoParam);
//명령문을 호출한다
DataSet dataSet = (DataSet)this.GetDataSet(sqlSelectCommand);
sqlSelectCommand.Dispose();
return(dataSet);
}
/**/
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SQLString, params IDataParameter[] iParms)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
System.Data.IDbCommand iCmd = GetCommand();
{
try
{
PrepareCommand(out iCmd, iConn, null, SQLString, iParms);
int rows = iCmd.ExecuteNonQuery();
iCmd.Parameters.Clear();
return(rows);
}
catch (System.Exception E)
{
throw new Exception(E.Message);
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/**/
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public int ExecuteSqlTran(Hashtable SQLStringList)
{
int i = 1;
using (System.Data.IDbConnection iConn = this.GetConnection())
{
iConn.Open();
using (IDbTransaction iTrans = iConn.BeginTransaction())
{
System.Data.IDbCommand iCmd = GetCommand();
try
{
//循环
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
IDataParameter[] iParms = (IDataParameter[])myDE.Value;
PrepareCommand(out iCmd, iConn, iTrans, cmdText, iParms);
int val = iCmd.ExecuteNonQuery();
iCmd.Parameters.Clear();
}
iTrans.Commit();
}
catch
{
iTrans.Rollback();
i = -1;
throw;
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
return(i);
}
}
}
public DataSet GetLocationData(int locationNo)
{
System.Data.IDbCommand sqlSelectCommand = EnterpriseManager.DbFactory.CreateDbCommand();
sqlSelectCommand.CommandText =
@"
select a.LocationNo, a.LocationName, a.BusinessClassCode, b.CompanyName
from location a inner join BusinessClass b
on a.BusinessClassCode = b.BusinessClassCode where a.LocationNo = @locationNo
";
sqlSelectCommand.CommandType = System.Data.CommandType.Text;
System.Data.IDataParameter locationNoParam = EnterpriseManager.DbFactory.CreateDataParameter("@locationNo", typeof(int));
locationNoParam.Value = locationNo;
sqlSelectCommand.Parameters.Add(locationNoParam);
// 명령문을 호출한다
DataSet dataSet = (DataSet)GetDataSet(sqlSelectCommand);
sqlSelectCommand.Dispose();
return(dataSet);
}
public DataSet GetCustomerData(string CustomerCode)
{
System.Data.IDbCommand sqlSelectCommand = EnterpriseManager.DbFactory.CreateDbCommand();
// Sql Command 명령문
sqlSelectCommand.CommandText =
@"select President, Address, BusinessType, BusinessItem from customers where customercode = @customerCode";
sqlSelectCommand.CommandType = System.Data.CommandType.Text;
//
System.Data.IDataParameter CustomerCodeParam = EnterpriseManager.DbFactory.CreateDataParameter("@customerCode", typeof(string));
CustomerCodeParam.Value = CustomerCode;
sqlSelectCommand.Parameters.Add(CustomerCodeParam);
//
// 명령문을 호출한다
DataSet dataset = this.GetDataSet(sqlSelectCommand);
sqlSelectCommand.Dispose();
return(dataset);
}
/// <summary>
/// 执行查询语句，返回一条记录对应的对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="strSQL">查询语句</param>
/// <returns></returns>
public T query <T>(string strSQL, params IDataParameter[] iParms) where T : class, new()
{
System.Data.IDbConnection iConn = this.GetConnection();
{
System.Data.IDbCommand iCmd = GetCommand();
{
try
{
PrepareCommand(out iCmd, iConn, null, strSQL, iParms);
System.Data.IDataReader iReader = iCmd.ExecuteReader();
iCmd.Parameters.Clear();
if (iReader.Read())
{
PropertyInfo[] pis = typeof(T).GetProperties();
T model = new T();
foreach (PropertyInfo pi in pis)
{
pi.SetValue(model, iReader[pi.Name], null);
}
return(model);
}
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
return(null);
}
public LocationSet GetForLocationType(int locationType)
{
// Sql Command 명령문
System.Data.IDbCommand sqlSelectCommand = EnterpriseManager.DbFactory.CreateDbCommand();
sqlSelectCommand.CommandText =
@"SELECT * FROM Location
WHERE LocationType = @locationType
order by LocationName";
sqlSelectCommand.CommandType = System.Data.CommandType.Text;
//
// 파라미터2값을 만든다
System.Data.IDataParameter locationTypeParam = EnterpriseManager.DbFactory.CreateDataParameter("@locationType", typeof(int));
locationTypeParam.Value = locationType;
sqlSelectCommand.Parameters.Add(locationTypeParam);
//
//
// 명령문을 호출한다
LocationSet entitySet = ((LocationSet)(this.GetEntitySet(sqlSelectCommand, typeof(LocationSet))));
sqlSelectCommand.Dispose();
return(entitySet);
}
//---------------------------------------------------------------------------------------------------------------------------------------------------------
public virtual void Disconnect()
{
// Disconnect can be called from Dispose and should guarantee no errors
// stops a proper reconnection after a timeout
// if(!m_bConnected)
// return;
try {
if (dbTransaction != null)
{
RollbackTransaction(false);
}
if (dbCommand != null)
{
dbCommand.Dispose();
dbCommand = null;
}
if (dbConnection != null)
{
try {
dbConnection.Close();
} catch {
} finally {
dbConnection.Dispose();
dbConnection = null;
}
}
dbConnected = false;
} catch (Exception ex) {
Logger.LogError(6, "Failed to disconnect: " + ex.StackTrace);
}
}
