public void ExecuteSqlDataSet(ref DataSet dataSet, string sql, string tableName) { SACommand cmd = new SACommand(); this.Connect(); SADataAdapter da = new SADataAdapter(); cmd.CommandTimeout = this.CommandTimeout; cmd.Connection = _connection; if (_transaction != null) { cmd.Transaction = _transaction; } cmd.CommandText = sql; cmd.CommandType = CommandType.Text; da.SelectCommand = cmd; da.Fill(dataSet, tableName); da.Dispose(); cmd.Dispose(); if (this.AutoCloseConnection) { this.Disconnect(); } }
public void ExecuteSPDataSet(ref DataSet dataSet, string procedureName, string tableName) { SACommand cmd = new SACommand(); this.Connect(); SADataAdapter da = new SADataAdapter(); cmd.CommandTimeout = this.CommandTimeout; cmd.CommandText = procedureName; cmd.Connection = _connection; if (_transaction != null) { cmd.Transaction = _transaction; } cmd.CommandType = CommandType.StoredProcedure; this.CopyParameters(cmd); da.SelectCommand = cmd; da.Fill(dataSet, tableName); _parameterCollection = cmd.Parameters; da.Dispose(); cmd.Dispose(); if (this.AutoCloseConnection) { this.Disconnect(); } }
public DataSet ExecuteSqlDataSet(string sql) { SACommand cmd = new SACommand(); this.Connect(); SADataAdapter da = new SADataAdapter(); DataSet ds = new DataSet(); cmd.CommandTimeout = this.CommandTimeout; cmd.Connection = _connection; if (_transaction != null) { cmd.Transaction = _transaction; } cmd.CommandText = sql; cmd.CommandType = CommandType.Text; da.SelectCommand = cmd; da.Fill(ds); da.Dispose(); cmd.Dispose(); if (this.AutoCloseConnection) { this.Disconnect(); } return(ds); }
//string sql, SAParameterCollection criteria, public virtual IList<AbstractBusinessObject> Query(string tablename, Type boType, SACommand cmd) { //SACommand objSql; //SADataReader dataReader; SADataAdapter adp = new SADataAdapter(); DataSet ds; ArrayList abos = new ArrayList(); // Getting rowcount //sql = "SELECT count(*) as Count FROM "+tablename+" where UserID = @userid"; //objSql = new SACommand(sql, conn); //objSql.Parameters.AddWithValue("@userid", UserID); //dataReader = ExecuteReader(objSql); //dataReader.Read(); //int rowcount = dataReader.GetInt32(0); //dataReader.Close(); //if (rowcount <= 0) // return null; if (BaseTableAdapter._trans != null) cmd.Transaction = _trans; try { // filling the DataSet with the DataAdapter adp.TableMappings.Add("Table", tablename); //add Parameters cmd.CommandType = CommandType.Text; adp.SelectCommand = cmd; ds = new DataSet(tablename); lock(BaseTableAdapter.conn) adp.Fill(ds); // Retrieve all the rows DataRowCollection rows = ds.Tables[0].Rows; if (rows.Count < 1) return null; // Loop through the Columns in the DataSet and map that to the properties of the class IEnumerator columns = ds.Tables[0].Columns.GetEnumerator(); DataColumn datacolumn; DataRow datarow; string cname; object cvalue; ConstructorInfo cons = boType.GetConstructor(Type.EmptyTypes); PropertyInfo[] props = boType.GetProperties(); //( rows.Count _log.Log(String.Format("Querying cmd={0}", cmd.CommandText), "DB", 5 ); for (int r = 0; r < rows.Count; r++) { datarow = rows[r]; AbstractBusinessObject curr = (AbstractBusinessObject)cons.Invoke(null); columns.Reset(); while (columns.MoveNext()) { try { datacolumn = (DataColumn)columns.Current; cname = datacolumn.ColumnName; for (int i = 0; i < props.Length; i++) { if (props[i].Name.ToLower() == cname.ToLower()) { cvalue = Convert.ChangeType(datarow[datacolumn], props[i].PropertyType); props[i].SetValue(curr, cvalue, null); _log.Log(String.Format("\tName={0} Value={1}", cname, cvalue), "DB", 5); break; // break for loop } } } catch (InvalidCastException ivce) { // go to next column } } abos.Add(curr); } } catch (Exception ex) { string logoutput = String.Format("----Error in BaseTableAdapter, Query----\r\n{0}\r\n{1}", ex.Message, ex.StackTrace); _log.Log(logoutput, "DB", 3); _log.Log(logoutput); } finally { adp.Dispose(); } AbstractBusinessObject[] rc = (AbstractBusinessObject[])abos.ToArray(boType); return rc; }
public virtual IList <AbstractBusinessObject> Query(string tablename, Type boType, SACommand cmd) //string sql, SAParameterCollection criteria, { //SACommand objSql; //SADataReader dataReader; SADataAdapter adp = new SADataAdapter(); DataSet ds; ArrayList abos = new ArrayList(); // Getting rowcount //sql = "SELECT count(*) as Count FROM "+tablename+" where UserID = @userid"; //objSql = new SACommand(sql, conn); //objSql.Parameters.AddWithValue("@userid", UserID); //dataReader = ExecuteReader(objSql); //dataReader.Read(); //int rowcount = dataReader.GetInt32(0); //dataReader.Close(); //if (rowcount <= 0) // return null; if (BaseTableAdapter._trans != null) { cmd.Transaction = _trans; } try { // filling the DataSet with the DataAdapter adp.TableMappings.Add("Table", tablename); //add Parameters cmd.CommandType = CommandType.Text; adp.SelectCommand = cmd; ds = new DataSet(tablename); lock (BaseTableAdapter.conn) adp.Fill(ds); // Retrieve all the rows DataRowCollection rows = ds.Tables[0].Rows; if (rows.Count < 1) { return(null); } // Loop through the Columns in the DataSet and map that to the properties of the class IEnumerator columns = ds.Tables[0].Columns.GetEnumerator(); DataColumn datacolumn; DataRow datarow; string cname; object cvalue; ConstructorInfo cons = boType.GetConstructor(Type.EmptyTypes); PropertyInfo[] props = boType.GetProperties(); //( rows.Count _log.Log(String.Format("Querying cmd={0}", cmd.CommandText), "DB", 5); for (int r = 0; r < rows.Count; r++) { datarow = rows[r]; AbstractBusinessObject curr = (AbstractBusinessObject)cons.Invoke(null); columns.Reset(); while (columns.MoveNext()) { try { datacolumn = (DataColumn)columns.Current; cname = datacolumn.ColumnName; for (int i = 0; i < props.Length; i++) { if (props[i].Name.ToLower() == cname.ToLower()) { cvalue = Convert.ChangeType(datarow[datacolumn], props[i].PropertyType); props[i].SetValue(curr, cvalue, null); _log.Log(String.Format("\tName={0} Value={1}", cname, cvalue), "DB", 5); break; // break for loop } } } catch (InvalidCastException ivce) { // go to next column } } abos.Add(curr); } } catch (Exception ex) { string logoutput = String.Format("----Error in BaseTableAdapter, Query----\r\n{0}\r\n{1}", ex.Message, ex.StackTrace); _log.Log(logoutput, "DB", 3); _log.Log(logoutput); } finally { adp.Dispose(); } AbstractBusinessObject[] rc = (AbstractBusinessObject[])abos.ToArray(boType); return(rc); }