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