// with parameters
public DataSet GetDataSet(string sql, SQL_PARAMETER[] parameters)
{
using (DbCommand command = DBProviderFactory.CreateCommand())
{
command.Connection = this.DBConnection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = sql;
command.CommandTimeout = 3000;
if (parameters != null)
{
foreach (SQL_PARAMETER parameter in parameters)
{
DbParameter dbParameter = DBProviderFactory.CreateParameter();
dbParameter.DbType = parameter.Type;
dbParameter.ParameterName = parameter.Name;
dbParameter.Value = parameter.Value;
command.Parameters.Add(dbParameter);
}
}
using (DbDataAdapter adapter = DBProviderFactory.CreateDataAdapter())
{
adapter.SelectCommand = command;
DataSet ds = new DataSet();
adapter.Fill(ds);
return(ds);
}
}
}
public object GetScalar(string sql, SQL_PARAMETER[] parameters)
{
using (DbCommand command = DBProviderFactory.CreateCommand())
{
command.Connection = this.DBConnection;
command.CommandText = sql;
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
foreach (SQL_PARAMETER parameter in parameters)
{
DbParameter dbParameter = DBProviderFactory.CreateParameter();
dbParameter.DbType = parameter.Type;
dbParameter.ParameterName = parameter.Name;
dbParameter.Value = parameter.Value;
command.Parameters.Add(dbParameter);
}
}
if (this.DBTransaction != null)
{
command.Transaction = this.DBTransaction;
}
return(command.ExecuteScalar());
}
}
/// <summary>
/// Executes Insert statements in the database. Optionally returns new identifier.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <param name="parameters">Sql parameter.</param>
/// <param name="getId">Value indicating whether newly generated identity is returned.</param>
/// <returns>Newly generated identity value (auto number value).</returns>
public int Insert(string sql, SQL_PARAMETER[] parameters, bool getId)
{
using (DbCommand command = DBProviderFactory.CreateCommand())
{
command.Connection = this.DBConnection;
if (this.DBTransaction != null)
{
command.Transaction = this.DBTransaction;
}
command.CommandText = sql;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Clear();
foreach (SQL_PARAMETER parameter in parameters)
{
DbParameter dbParameter = DBProviderFactory.CreateParameter();
dbParameter.DbType = parameter.Type;
dbParameter.ParameterName = parameter.Name;
dbParameter.Value = parameter.Value;
command.Parameters.Add(dbParameter);
}
command.ExecuteNonQuery();
int id = -1;
// Check if new identity is needed.
if (getId)
{
// Execute RpmServerDb specific auto number or identity retrieval code
// SELECT SCOPE_IDENTITY() -- for SQL Server
// SELECT @@IDENTITY -- for MS Access/Odbc
string identitySelect;
//switch (Consts.DataProvider)
//{
// Odbc
//case "System.Data.Odbc":
identitySelect = "SELECT @@IDENTITY";
//break;
// Sql Server
//case "System.Data.SqlClient":
// identitySelect = "SELECT SCOPE_IDENTITY()";
// break;
//default:
// identitySelect = "SELECT @@IDENTITY";
// break;
// }
command.CommandText = identitySelect;
//id = int.Parse(command.ExecuteScalar().ToString());
}
return(id);
}
}
public int Update(string sql, SQL_PARAMETER[] parameters)
{
using (DbCommand command = DBProviderFactory.CreateCommand())
{
command.Connection = this.DBConnection;
if (this.DBTransaction != null)
{
command.Transaction = this.DBTransaction;
}
command.CommandText = sql;
command.Parameters.Clear();
foreach (SQL_PARAMETER parameter in parameters)
{
DbParameter dbParameter = DBProviderFactory.CreateParameter();
dbParameter.DbType = parameter.Type;
dbParameter.ParameterName = parameter.Name;
dbParameter.Value = parameter.Value;
command.Parameters.Add(dbParameter);
}
int result = command.ExecuteNonQuery();
return(result);
}
}