public DBResultSet ExecuteDataTable(string commandText, CommandType commandType)
{
var result = new DBResultSet();
//{
// ds = new DataSet(),
// dt = new DataTable()
//};
lastError = "";
try
{
using (command = GetDataProviderCommand())
{
command.Connection = connection;
command.CommandType = commandType;
command.CommandText = commandText;
adapter = GetDataProviderDataAdapter(command);
IDataReader r = command.ExecuteReader();
result.dr = (DbDataReader)r;
}
}
catch (Exception ex)
{
lastError = ex.Message;
}
return(result);
}
public DBResultSet ExecuteReaderParameterized(string commandText, CommandType commandType, List <SqlParameter> paramList)
{
var returnValue = new DBResultSet();
lastError = "";
try
{
using (command = GetDataProviderCommand())
{
command.Connection = connection;
command.CommandType = commandType;
command.CommandText = commandText;
foreach (SqlParameter p in paramList)
{
command.Parameters.Add(p);
}
IDataReader r = command.ExecuteReader();
returnValue.dr = (DbDataReader)r;
//returnValue.dr.Read();
}
}
catch (Exception ex)
{
lastError = ex.Message;
}
return(returnValue);
}
public DBResultSet ExecuteReaderParameterized(string commandText, CommandType commandType, Dictionary <string, object> paramList)
{
var returnValue = new DBResultSet();
lastError = "";
try
{
using (command = GetDataProviderCommand())
{
command.Connection = connection;
command.CommandType = commandType;
command.CommandText = commandText;
foreach (var item in paramList)
{
var p = command.CreateParameter();
p.ParameterName = item.Key;
p.Value = item.Value ?? DBNull.Value;
command.Parameters.Add(p);
}
returnValue.dr = (DbDataReader)command.ExecuteReader();
returnValue.dr.Read();
}
}
catch (Exception ex)
{
lastError = ex.Message;
}
return(returnValue);
}
public DBResultSet GetDataTableFromSP(string spname, string[] param_name, object[] param_values)
{
var result = new DBResultSet
{
ds = new DataSet(),
dt = new DataTable()
};
try
{
command = GetDataProviderCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = spname;
var paramCount = param_name.Length;
if (param_name.Length > 0 && param_values.Length > 0)
{
for (var i = 0; i < paramCount; i++)
{
var param = command.CreateParameter();
if (param_values[i] == null)
{
param_values[i] = DBNull.Value;
}
param.ParameterName = param_name[i];
param.Value = param_values[i];
command.Parameters.Add(param);
}
}
command.Connection = connection;
result.dr = (DbDataReader)command.ExecuteReader();
result.dr.Read();
result.RowsAffected = result.dr.FieldCount;
//using (command = GetDataProviderCommand())
//{
// command.Connection = connection;
// command.CommandType = CommandType.Text;
// command.CommandText = query;
// result.dr = command.ExecuteReader();
// result.dr.Read();
// result.RowsAffected = result.dr.FieldCount;
//}
}
catch (Exception ex)
{
lastError = "DBConnection ::" + ex.Message;
//throw;
}
return(result);
}
public DBResultSet ExecuteDataTableParameterized(string commandText, CommandType commandType, Hashtable HtParamList)
{
var result = new DBResultSet
{
ds = new DataSet(),
dt = new DataTable()
};
lastError = "";
try
{
using (command = GetDataProviderCommand())
{
command.Connection = connection;
command.CommandType = commandType;
command.CommandText = commandText;
foreach (string Key in HtParamList.Keys)
{
var p = command.CreateParameter();
p.Direction = ParameterDirection.Input;
p.ParameterName = Key;
p.Value = HtParamList[Key] ?? DBNull.Value;
command.Parameters.Add(p);
}
adapter = GetDataProviderDataAdapter(command);
adapter.Fill(result.ds);
result.dt = result.ds.Tables[0];
result.RowsAffected = result.dt.Rows.Count;
//result.dtRowIndex = 0;
}
}
catch (Exception ex)
{
lastError = ex.Message;
}
return(result);
}
public DBResultSet ExecuteReader(string commandText, CommandType commandType)
{
var result = new DBResultSet();
lastError = "";
try
{
using (command = GetDataProviderCommand())
{
command.Connection = connection;
command.CommandType = commandType;
command.CommandText = commandText;
result.dr = (DbDataReader)command.ExecuteReader();
//result.dr.Read();
}
}
catch (Exception ex)
{
lastError = "DBConnection ::" + ex.Message;
//throw;
}
return(result);
}