/// <summary> /// Exec Procedure /// </summary> public DbOutParameter ExecProcedure(DbInParameter _DbInParameter) { DbOutParameter rtn = new DbOutParameter(); if (this._s == DBStatus.Begin_Trans) { sqlcomm = new SqlCommand(_DbInParameter.StoreProcureName, this.sqlconn, this.trans); } else { sqlcomm = new SqlCommand(_DbInParameter.StoreProcureName, this.sqlconn); } sqlcomm.CommandType = CommandType.StoredProcedure; // sqlcomm.CommandTimeout = 10000000; foreach (SqlParameter sp in _DbInParameter.SQLParameter) { sqlcomm.Parameters.Add(sp); } try { if (_DbInParameter.IsReturnDataSet) { SqlDataAdapter sqlDa = new SqlDataAdapter(); DataSet ds = new DataSet(); sqlDa.SelectCommand = sqlcomm; sqlDa.Fill(ds); rtn.ReturnDataSet = ds; } else { rtn.ExecteSuccess = sqlcomm.ExecuteNonQuery(); // success >0 } //get parameter of out foreach (SqlParameter sp in sqlcomm.Parameters) { if (sp.Direction == ParameterDirection.Output || sp.Direction == ParameterDirection.InputOutput || sp.Direction == ParameterDirection.ReturnValue) { if (!rtn.ReturnDic.ContainsKey(sp.ParameterName)) { rtn.ReturnDic.Add(sp.ParameterName, sp.Value); } } } } finally { sqlcomm.Cancel(); sqlcomm = null; } return(rtn); }
public static DbOutParameter ExecuteSP(xsSpParameter spPara) { DbInParameter dbInPara = new DbInParameter(); DbOutParameter dbOutPara = new DbOutParameter(); dbInPara.StoreProcureName = spPara.SPName; //set sp name dbInPara.IsReturnDataSet = spPara.IsReturnDataSet; //set sp return dbInPara.AddParameter(spPara.SpParameters); //set spparameter DbAccess Dao = new DbAccess(); Dao.Open(spPara.SqlConnectString); dbOutPara = Dao.ExecProcedure(dbInPara); Dao.Close(); return(dbOutPara); }
/// <summary> /// Get data /// </summary> /// <param name="_DbInParameter"></param> /// <returns></returns> public DbOutParameter ExecuteQuery(DbInParameter _DbInParameter) { DbOutParameter rtn = new DbOutParameter(); if (sqlcomm == null) { sqlcomm = new SqlCommand(_DbInParameter.SQL, this.sqlconn); //sqlcomm.CommandTimeout = 10000000; } else { sqlcomm.CommandText = _DbInParameter.SQL; } if (_DbInParameter.SQLParameter != null) { foreach (SqlParameter sp in _DbInParameter.SQLParameter) { sqlcomm.Parameters.Add(sp); } } if (_s == DBStatus.Begin_Trans) { sqlcomm.Transaction = this.trans; } try { SqlDataAdapter sqlDa = new SqlDataAdapter(); DataSet ds = new DataSet(); sqlDa.SelectCommand = sqlcomm; sqlDa.Fill(ds); rtn.ReturnDataSet = ds; } catch (Exception ex) { throw ex; } finally { sqlcomm.Cancel(); sqlcomm = null; } return(rtn); }
/// <summary> /// execute sql /// </summary> /// <param name="sqlparm"></param> /// <returns></returns> public static void Execute(xsSqlParameter sqlparm) { DbInParameter dbInPara = new DbInParameter(); dbInPara.SQL = sqlparm.SQL; //add sql parameter dbInPara.AddParameter(sqlparm.SqlPara); DbAccess Dao = new DbAccess(); Dao.Open(sqlparm.SqlConnectString); Dao.BeginTrans(); Dao.ExecuteNoQuery(dbInPara); Dao.Commit(); Dao.Close(); }
/// <summary> /// get dataset /// </summary> /// <param name="strsql">sql string</param> /// <param name="SqlPara">parameter</param> /// <returns></returns> public static DataSet GetDataSet(xsSqlParameter sqlparm) { DbInParameter dbInPara = new DbInParameter(); dbInPara.SQL = sqlparm.SQL; //add sql parameter dbInPara.AddParameter(sqlparm.SqlPara); DbAccess Dao = new DbAccess(); Dao.Open(sqlparm.SqlConnectString); DataSet dsRtn = Dao.ExecuteQuery(dbInPara).ReturnDataSet; Dao.Close(); return(dsRtn); }
/// <summary> /// sql of ExecuteNoQuery /// </summary> /// <param name="_DbInParameter"></param> public void ExecuteNoQuery(DbInParameter _DbInParameter) { if (sqlcomm == null) { sqlcomm = new SqlCommand(_DbInParameter.SQL, this.sqlconn); //sqlcomm.CommandTimeout = 10000000; } else { sqlcomm.CommandText = _DbInParameter.SQL; } if (_DbInParameter.SQLParameter != null) { foreach (SqlParameter sp in _DbInParameter.SQLParameter) { sqlcomm.Parameters.Add(sp); } } if (_s == DBStatus.Begin_Trans) { sqlcomm.Transaction = this.trans; } try { sqlcomm.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { sqlcomm.Cancel(); sqlcomm = null; } }