/**/ /// <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); } }