public DataSet ExecuteResourceDataSet()
{
var ret = new DataSet();
var parms = new List <Param>();
foreach (StoredProcedureParameter parm in _params)
{
switch (parm.DBValueType)
{
case ParameterType.Structured:
parms.Add(Param.createresourceParam(parm.Name, SqlDbType.Structured, parm.Value,
ParameterDirection.In));
break;
case ParameterType.DBString:
if (parm.Direction == ParameterDirection.In)
{
parms.Add(Param.createresourceParam(parm.Name, DbType.String, parm.Value, ParameterDirection.In));
}
else
{
parms.Add(Param.createresourceParam(parm.Name, DbType.String, parm.Value, ParameterDirection.Out));
}
break;
case ParameterType.DBDateTime:
parms.Add(Param.createresourceParam(parm.Name, DbType.DateTime, parm.Value, ParameterDirection.In));
break;
case ParameterType.DBNvar:
parms.Add(Param.createresourceParam(parm.Name, DbType.String, parm.Value, ParameterDirection.In));
break;
//handles any case that is not specificed above with default of appaneding to the string
default:
parms.Add(Param.createresourceParam(parm.Name, DbType.String, parm.Value, ParameterDirection.In));
break;
}
}
using (var sqlConn = new SqlConnection(ReadConnectionString()))
{
try
{
sqlConn.Open();
}
catch
{
throw;
}
using (var sqlCommand = new SqlCommand(_storedProcedureName, sqlConn))
{
foreach (StoredProcedureParameter parm in _params)
{
sqlCommand.Parameters.AddWithValue(parm.Name, parm.Value);
}
sqlCommand.CommandTimeout = _commandTimeOut;
sqlCommand.CommandType = CommandType.StoredProcedure;
var mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = sqlCommand;
mySqlDataAdapter.Fill(ret);
}
}
return(ret);
}
/// <summary>
/// Create the dataset. This should be only visible to the DAL layer. For MS SQL DBs
/// </summary>
/// <returns></returns>
public void ExecuteDataSetWithOutputParam(out DataTable dtRet, out String sID)
{
DataTable ret = null;
String ID = string.Empty;
var parms = new List <Param>();
foreach (StoredProcedureParameter parm in _params)
{
//JBienvenu 19202 2012-12-28 switch: Implemented ParameterTypeAdapter system.
switch (parm.DBValueType)
{
case ParameterType.Structured:
parms.Add(Param.createParam(parm.Name, new StructuredAdapter(), parm.Value,
ParameterDirection.In));
break;
case ParameterType.DBString:
parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In));
break;
case ParameterType.DBDateTime:
parms.Add(Param.createParam(parm.Name, new DateTimeAdapter(), parm.Value, ParameterDirection.In));
break;
case ParameterType.DBNvar:
parms.Add(Param.createParam(parm.Name, new NvarAdapter(), parm.Value, ParameterDirection.In));
break;
//handles any case that is not specificed above with default of appaneding to the string
default:
parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In));
break;
}
}
using (var sqlConn = new SqlConnection(ReadConnectionString()))
{
try
{
sqlConn.Open();
}
catch
{
throw;
}
using (var sqlCommand = new SqlCommand(_storedProcedureName, sqlConn))
{
//JBienvenu 19202 2012-12-28 foreach: Changed to use parms (which were not used at all before). Implemented ParameterTypeAdapter system.
parms.ForEach((parm) => parm.ToSqlParameter(sqlCommand));
SqlParameter myparam3 = sqlCommand.Parameters.Add("@referenceTableType", SqlDbType.VarChar, 10);
myparam3.Direction = System.Data.ParameterDirection.Output;
sqlCommand.CommandTimeout = _commandTimeOut;
sqlCommand.CommandType = CommandType.StoredProcedure;
SqlDataReader reader = sqlCommand.ExecuteReader();
ret = GetDataTableFromDataReader(reader);
reader.Close();
ID = (string)sqlCommand.Parameters["@referenceTableType"].Value;
reader.Close();
}
}
dtRet = ret; sID = ID;
}
/// <summary>
/// Create the dataset. This should be only visible to the DAL layer. For MS SQL DBs
/// </summary>
/// <returns></returns>
public DataSet ExecuteMultipleDataSet()
{
var ret = new DataSet();
var parms = new List <Param>();
foreach (StoredProcedureParameter parm in _params)
{
//JBienvenu 19202 2012-12-28 switch: Implemented ParameterTypeAdapter system.
switch (parm.DBValueType)
{
case ParameterType.Structured:
parms.Add(Param.createParam(parm.Name, new StructuredAdapter(), parm.Value,
ParameterDirection.In));
break;
case ParameterType.DBString:
if (parm.Direction == ParameterDirection.In)
{
parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In));
}
else
{
parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.Out));
}
break;
case ParameterType.DBDateTime:
parms.Add(Param.createParam(parm.Name, new DateTimeAdapter(), parm.Value, ParameterDirection.In));
break;
case ParameterType.DBNvar:
parms.Add(Param.createParam(parm.Name, new NvarAdapter(), parm.Value, ParameterDirection.In));
break;
case ParameterType.DBInteger:
parms.Add(Param.createParam(parm.Name, new IntegerAdapter(), parm.Value, ParameterDirection.In));
break;
//handles any case that is not specificed above with default of appaneding to the string
default:
parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In));
break;
}
}
using (var sqlConn = new SqlConnection(ReadConnectionString()))
{
try
{
sqlConn.Open();
}
catch
{
throw;
}
using (var sqlCommand = new SqlCommand(_storedProcedureName, sqlConn))
{
//JBienvenu 19202 2012-12-28 foreach: Changed to use parms (which were not used at all before). Implemented ParameterTypeAdapter system.
parms.ForEach((parm) => parm.ToSqlParameter(sqlCommand));
sqlCommand.CommandTimeout = _commandTimeOut;
sqlCommand.CommandType = CommandType.StoredProcedure;
var mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = sqlCommand;
mySqlDataAdapter.Fill(ret);
}
}
return(ret);
}
/// <summary>
/// Create the dataset. This should be only visible to the DAL layer. For MS SQL DBs
/// Note: THe core functionality for this procedure has been cloned to executeNonQuery() to handle routines that
/// do not expect any type of return value.
/// </summary>
/// <returns></returns>
private DataTable MSSQL_executeDataSet()
{
var ret = new DataTable();
var parms = new List <Param>();
foreach (StoredProcedureParameter parm in _params)
{
//JBienvenu 19202 2012-12-28 switch: Implemented ParameterTypeAdapter system.
switch (parm.DBValueType)
{
case ParameterType.Structured:
parms.Add(Param.createParam(parm.Name, new StructuredAdapter(), parm.Value,
ParameterDirection.In));
break;
case ParameterType.DBString:
parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In));
break;
case ParameterType.DBDateTime:
parms.Add(Param.createParam(parm.Name, new DateTimeAdapter(), parm.Value, ParameterDirection.In));
break;
case ParameterType.DBNvar:
parms.Add(Param.createParam(parm.Name, new NvarAdapter(), parm.Value, ParameterDirection.In));
break;
case ParameterType.DBVarBinary:
parms.Add(Param.createParam(parm.Name, new VarBinaryAdapter(), parm.Value, ParameterDirection.In));
break;
default: //handles any case that is not specificed above with default of appaneding to the string
parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In));
break;
}
}
using (var sqlConn = new SqlConnection(ReadConnectionString()))
{
try
{
sqlConn.Open();
}
catch
{
throw;
}
using (var sqlCommand = new SqlCommand(_storedProcedureName, sqlConn))
{
//JBienvenu 19202 2012-12-28 foreach: Changed to use parms (which were not used at all before). Implemented ParameterTypeAdapter system.
parms.ForEach((parm) => parm.ToSqlParameter(sqlCommand));
sqlCommand.CommandTimeout = _commandTimeOut;
sqlCommand.CommandType = CommandType.StoredProcedure;
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
{
if (sqlDataReader.VisibleFieldCount > 0)
{
foreach (DataRow dRow in sqlDataReader.GetSchemaTable().Rows)
{
if (!(dRow["ColumnName"].ToString().Trim().Equals("")))
{
ret.Columns.Add(dRow["ColumnName"].ToString());
ret.Columns[dRow["ColumnName"].ToString()].ReadOnly = false;
ret.Columns[dRow["ColumnName"].ToString()].AllowDBNull = true;
ret.Columns[dRow["ColumnName"].ToString()].DataType =
Type.GetType(dRow["DataType"].ToString());
}
}
ret.Load(sqlDataReader);
}
}
}
}
return(ret);
}
