/// <summary>
/// Executes dynamic SQL statement and fills DataRow with data
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="sqlQuery">Sql statement</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="objDataRow">DataRow</param>
/// <param name="commandTimeout">Command timeout</param>
/// <returns>DataRow</returns>
public static DataRow GetDataRow(ConnectionStructure connectionStructure, string sqlQuery, SqlParameter[] commandParameters, DataRow objDataRow, int commandTimeout)
{
objDataRow = GetDataRow(GetConnectionString(connectionStructure), sqlQuery, commandParameters, objDataRow, commandTimeout);
return objDataRow;
}
/// <summary>
/// Executes dynamic SQL statement and fills ArrayList with data
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="sqlQuery">Sql statement</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="objArrayList">ArrayList</param>
/// <param name="commandTimeout">Command timeout</param>
/// <returns>ArrayList</returns>
public static ArrayList GetArrayList(ConnectionStructure connectionStructure, string sqlQuery, SqlParameter[] commandParameters, ArrayList objArrayList, int commandTimeout)
{
objArrayList = GetArrayList(GetConnectionString(connectionStructure), sqlQuery, commandParameters, objArrayList, commandTimeout);
return objArrayList;
}
/// <summary>
/// Executes non query
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="sqlQuery">SQL statement to execute</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <returns>Number of Records affected</returns>
public static int ExecuteNonQuery(ConnectionStructure connectionStructure, string sqlQuery, SqlParameter[] commandParameters)
{
int Return;
Return = ExecuteNonQuery(GetConnectionString(connectionStructure), sqlQuery, commandParameters);
return Return;
}
/// <summary>
/// Run Stored Procedure
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="storedProcName">Name of stored procedure</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="commandTimeout">Command timeout</param>
/// <param name="rowsAffected">Ouput parameters for affected rows</param>
/// <returns>Integer</returns>
public static int RunProcedure(ConnectionStructure connectionStructure, string storedProcName, SqlParameter[] commandParameters, int commandTimeout, out int rowsAffected)
{
int Return = RunProcedure(GetConnectionString(connectionStructure), storedProcName, commandParameters, commandTimeout, out rowsAffected);
return Return;
}
/// <summary>
/// Executes Scalar on the aggregate function. Uses stored procedure
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="storedProcName">Stored procedure name</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="commandTimeout">Command timeout</param>
/// <returns>object that contains the result of the aggregate calculation</returns>
public static object ExecuteScalarStoredProc(ConnectionStructure connectionStructure, string storedProcName, SqlParameter[] commandParameters, int commandTimeout)
{
object Return = ExecuteScalarStoredProc(GetConnectionString(connectionStructure), storedProcName, commandParameters, commandTimeout);
return Return;
}
/// <summary>
/// Performs bulk copy for multiple inserts
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="objDataReader">DataReader with results</param>
/// <param name="destinationTableName">Name of the destination table</param>
/// <param name="batchSize">batch size of the bulk copy (number of rows)</param>
public static void InsertBulkCopy(ConnectionStructure connectionStructure, SqlDataReader objDataReader, string destinationTableName, int batchSize)
{
InsertBulkCopy(GetConnectionString(connectionStructure), objDataReader, destinationTableName, batchSize);
}
/// <summary>
/// Inserts record and returns the identity of the inserted record. Executes stored procedure.
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="storedProcName">Name of the stored procedure</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="commandTimeout">Command timeout</param>
/// <returns>Identity of the inserted record</returns>
public static int InsertIdentityStoredProc(ConnectionStructure connectionStructure, string storedProcName, SqlParameter[] commandParameters, int commandTimeout)
{
int Return;
Return = InsertIdentityStoredProc(GetConnectionString(connectionStructure), storedProcName, commandParameters, commandTimeout);
return Return;
}
/// <summary>
/// Executes dynamic SQL statement and fills Generic List with int data.
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="sqlQuery">Sql statement</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="objList">Generic list to fill with data</param>
/// <param name="commandTimeout">Command timeout</param>
/// <returns>Generic List (string)</returns>
public static List<string> GetGenericList(ConnectionStructure connectionStructure, string sqlQuery, SqlParameter[] commandParameters, List<string> objList, int commandTimeout)
{
objList = GetGenericList(GetConnectionString(connectionStructure), sqlQuery, commandParameters, objList, commandTimeout);
return objList;
}
/// <summary>
/// Executes stored procedure (non query)
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="storedProcName">Stored procedure name</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="commandTimeout">Command timeout</param>
/// <returns>Number of records affected</returns>
public static int ExecuteNonQueryStoredProc(ConnectionStructure connectionStructure, string storedProcName, SqlParameter[] commandParameters, int commandTimeout)
{
int Return;
Return = ExecuteNonQueryStoredProc(GetConnectionString(connectionStructure), storedProcName, commandParameters, commandTimeout);
return Return;
}
/// <summary>
/// Executes dynamic SQL statement and fills DataTable with data
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="sqlQuery">Sql statement</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="objDataTable">DataTable object</param>
/// <returns>DataTable</returns>
public static DataTable GetDataTable(ConnectionStructure connectionStructure, string sqlQuery, SqlParameter[] commandParameters, DataTable objDataTable)
{
objDataTable = GetDataTable(GetConnectionString(connectionStructure), sqlQuery, commandParameters, objDataTable);
return objDataTable;
}
/// <summary>
/// Executes stored procedure and fills DataTable with data
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="storedProcName">Stored procedure name</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="objDataTable"></param>
/// <returns>DataTable</returns>
public static DataTable GetDataTableStoredProc(ConnectionStructure connectionStructure, string storedProcName, SqlParameter[] commandParameters, DataTable objDataTable)
{
objDataTable = GetDataTableStoredProc(GetConnectionString(connectionStructure), storedProcName, commandParameters, objDataTable);
return objDataTable;
}
/// <summary>
/// Executes stored procedure and fills DataSet with data
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="storedProcName">Stored procedure name</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="objDataSet">DataSet to fill with data</param>
/// <param name="commandTimeout">Command timeout</param>
/// <returns>DataSet</returns>
public static DataSet GetDataSetStoredProc(ConnectionStructure connectionStructure, string storedProcName, SqlParameter[] commandParameters, DataSet objDataSet, int commandTimeout)
{
objDataSet = GetDataSetStoredProc(GetConnectionString(connectionStructure), storedProcName, commandParameters, objDataSet, commandTimeout);
return objDataSet;
}
/// <summary>
/// Executes dynamic SQL statement and fills DataSet with data
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="sqlQuery">Sql statement</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="objDataSet">DataSet to fill with data</param>
/// <param name="commandTimeout">Command timeout</param>
/// <returns>DataSet</returns>
public static DataSet GetDataSet(ConnectionStructure connectionStructure, string sqlQuery, SqlParameter[] commandParameters, DataSet objDataSet, int commandTimeout)
{
objDataSet = GetDataSet(GetConnectionString(connectionStructure), sqlQuery, commandParameters, objDataSet, commandTimeout);
return objDataSet;
}
/// <summary>
/// Executes SQL stored procedure and fills DataRow with data.
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="storedProcName">Name of stored procedure to execute</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="objDataRow">DataRow</param>
/// <returns>DataRow</returns>
public static DataRow GetDataRowStoredProc(ConnectionStructure connectionStructure, string storedProcName, SqlParameter[] commandParameters, DataRow objDataRow)
{
objDataRow = GetDataRowStoredProc(GetConnectionString(connectionStructure), storedProcName, commandParameters, objDataRow);
return objDataRow;
}
/// <summary>
/// Executes stored procedure and fills string array with data
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="storedProcName">storedProcName</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="commandTimeout">Command timeout</param>
/// <returns>string[]</returns>
public static string[] GetStringValuesStoredProc(ConnectionStructure connectionStructure, string storedProcName, SqlParameter[] commandParameters, int commandTimeout)
{
string[] ReturnResults = GetStringValuesStoredProc(GetConnectionString(connectionStructure), storedProcName, commandParameters, commandTimeout);
return ReturnResults;
}
/// <summary>
/// Executes dynamic SQL statement and fills Generic List with int data.
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="storedProcName">storedProcName</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="objList">Generic list to fill with data</param>
/// <param name="commandTimeout">Command timeout</param>
/// <returns>Generic List (string)</returns>
public static List<string> GetGenericListStoredProc(ConnectionStructure connectionStructure, string storedProcName, SqlParameter[] commandParameters, List<string> objList, int commandTimeout)
{
objList = GetGenericListStoredProc(GetConnectionString(connectionStructure), storedProcName, commandParameters, objList, commandTimeout);
return objList;
}
/// <summary>
/// Performs bulk copy for multiple inserts
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="objDataTable">DataTable with values to insert</param>
/// <param name="destinationTableName">Name of the destination table</param>
/// <param name="batchSize">batch size of the bulk copy (number of rows)</param>
/// <param name="timeOut">Bulk Copy timeout</param>
public static void InsertBulkCopy(ConnectionStructure connectionStructure, DataTable objDataTable, string destinationTableName, int batchSize, int timeOut)
{
InsertBulkCopy(GetConnectionString(connectionStructure), objDataTable, destinationTableName, batchSize, timeOut);
}
/// <summary>
/// Gets hashtable object. Executes dynamic query
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="sqlQuery">Sql statement to execute</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="objHashtable">Instantiated Hashtable object</param>
/// <param name="commandTimeout">Command Timeout</param>
/// <returns>Hashtable</returns>
public static Hashtable GetHashtable(ConnectionStructure connectionStructure, string sqlQuery, SqlParameter[] commandParameters, Hashtable objHashtable, int commandTimeout)
{
objHashtable = GetHashtable(GetConnectionString(connectionStructure), sqlQuery, commandParameters, objHashtable, commandTimeout);
return objHashtable;
}
/// <summary>
/// Inserts record and returns the identity of the inserted record. Executes dynamic query.
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="sqlQuery">Sql statement to execute</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="commandTimeout">SQL parameters array</param>
/// <returns>Identity of the inserted record</returns>
public static int InsertIdentity(ConnectionStructure connectionStructure, string sqlQuery, SqlParameter[] commandParameters, int commandTimeout)
{
int Return;
Return = InsertIdentity(GetConnectionString(connectionStructure), sqlQuery, commandParameters, commandTimeout);
return Return;
}
/// <summary>
/// Executes stored procedure and fills hashtable object with data
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="storedProcName">Name of the stored procedure</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="objHashtable">Hashtable object</param>
/// <param name="commandTimeout">Command timeout</param>
/// <returns>Hashtable</returns>
public static Hashtable GetHashtableStoredProc(ConnectionStructure connectionStructure, string storedProcName, SqlParameter[] commandParameters, Hashtable objHashtable, int commandTimeout)
{
objHashtable = GetHashtableStoredProc(GetConnectionString(connectionStructure), storedProcName, commandParameters, objHashtable, commandTimeout);
return objHashtable;
}
/// <summary>
/// Creates a DataSet by running the stored procedure and placing the results
/// of the query/proc into the given tablename.
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="storedProcName">Name of stored procedure</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="tableName">Name of the table to return</param>
/// <param name="commandTimeout">Command timeout</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(ConnectionStructure connectionStructure, string storedProcName, SqlParameter[] commandParameters, string tableName, int commandTimeout)
{
DataSet objDataSet = RunProcedure(GetConnectionString(connectionStructure), storedProcName, commandParameters, tableName, commandTimeout);
return objDataSet;
}
/// <summary>
/// Executes dynamic SQL statement and fills integer32 array with data.
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="sqlQuery">Sql statement</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <returns>int[]</returns>
public static int[] GetIntegerValues(ConnectionStructure connectionStructure, string sqlQuery, SqlParameter[] commandParameters)
{
int[] ReturnResults = GetIntegerValues(GetConnectionString(connectionStructure), sqlQuery, commandParameters);
return ReturnResults;
}
//TATIANA TO DO: start here
/// <summary>
/// Takes an -existing- dataset and fills the given table name with the results
/// of the stored procedure
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="storedProcName">Stored procedure name</param>
/// <param name="commandParameters"></param>
/// <param name="objDataSet"></param>
/// <param name="tableName"></param>
/// <returns>DataSet</returns>
public static void RunProcedure(ConnectionStructure connectionStructure, string storedProcName, SqlParameter[] commandParameters, DataSet objDataSet, string tableName)
{
SqlCommand objCommand = new SqlCommand();
objCommand.CommandText = storedProcName;
objCommand.CommandType = CommandType.StoredProcedure;
SqlConnection objConnection = new SqlConnection();
string ConnectionString = GetConnectionString(connectionStructure);
objConnection = OpenConnection(objConnection, ConnectionString);
objCommand.Connection = objConnection;
if (commandParameters != null)
{
AttachParametersStoredProc(objCommand, commandParameters);
objCommand.Parameters.Add(new SqlParameter("ReturnValue",
SqlDbType.Int,
4, /* Size */
ParameterDirection.ReturnValue,
false, /* is nullable */
0, /* byte precision */
0, /* byte scale */
string.Empty,
DataRowVersion.Default,
null));
objCommand.Prepare();
}
if (objDataSet == null) throw new ArgumentNullException("objDataSet");
SqlDataAdapter objDataAdapter = new SqlDataAdapter(objCommand);
try
{
objDataAdapter.Fill(objDataSet, tableName);
}
catch (SqlException se)
{
//Generic SqlException
throw new Exception(se.ToString());
}
catch (FormatException fe)
{
//wrong parameters Data Type
throw new FormatException(fe.ToString());
}
catch (Exception ex)
{
//any other exception
throw new Exception(ex.ToString());
}
finally
{
objDataAdapter.Dispose();
CleanParameters(objCommand);
KillConnection(objConnection);
}
}
/// <summary>
/// Executes stored procedure and fills integer32 array with data
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="storedProcName"></param>
/// <param name="commandParameters"></param>
/// <returns>int[]</returns>
public static int[] GetIntegerValuesStoredProc(ConnectionStructure connectionStructure, string storedProcName, SqlParameter[] commandParameters)
{
int[] ReturnResults = GetIntegerValuesStoredProc(GetConnectionString(connectionStructure), storedProcName, commandParameters);
return ReturnResults;
}
/// <summary>
/// Creates a connection string from the connection structure
/// </summary>
/// <param name="objConn">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <returns>Connection String</returns>
private static string GetConnectionString(ConnectionStructure objConn)
{
DbConnectionStringBuilder objConnectionBuilder = new DbConnectionStringBuilder();
string ConnectionString;
try
{
objConnectionBuilder.Add("Data Source", objConn.DataSource);
objConnectionBuilder.Add("Initial Catalog", objConn.InitialCatalog);
objConnectionBuilder.Add("Trusted_Connection", objConn.TrustedConnection);
objConnectionBuilder.Add("User ID", objConn.UserID);
objConnectionBuilder.Add("Password", objConn.Password);
objConnectionBuilder.Add("Persist Security Info", objConn.PersistSecurityInfo);
objConnectionBuilder.Add("Min Pool Size", objConn.MinPoolSize);
objConnectionBuilder.Add("Max Pool Size", objConn.MaxPoolSize);
objConnectionBuilder.Add("Enlist", objConn.Enlist);
objConnectionBuilder.Add("Async", objConn.Async);
objConnectionBuilder.Add("MultipleActiveResultSets", objConn.MultipleActiveResultSets);
ConnectionString = objConnectionBuilder.ConnectionString;
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
//finally
//{
// objConnectionBuilder = null;
//}
return ConnectionString;
}
/// <summary>
/// Executes Scalar on the aggregate function
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="sqlQuery">Sql Statement</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="commandTimeout">Command timeout</param>
/// <returns>object that contains the result of the aggregate calculation</returns>
public static object ExecuteScalar(ConnectionStructure connectionStructure, string sqlQuery, SqlParameter[] commandParameters, int commandTimeout)
{
object Return = ExecuteScalar(GetConnectionString(connectionStructure), sqlQuery, commandParameters, commandTimeout);
return Return;
}
/// <summary>
/// Executes stored procedure and fills ArrayList with data
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="storedProcName">storedProcName</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="objArrayList">ArrayList</param>
/// <param name="commandTimeout">Command timeout</param>
/// <returns>ArrayList</returns>
public static ArrayList GetArrayListStoredProc(ConnectionStructure connectionStructure, string storedProcName, SqlParameter[] commandParameters, ArrayList objArrayList, int commandTimeout)
{
objArrayList = GetArrayListStoredProc(GetConnectionString(connectionStructure), storedProcName, commandParameters, objArrayList, commandTimeout);
return objArrayList;
}
/// <summary>
/// Executes dynamic SQL statement and fills string array with data
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="sqlQuery">Sql statement</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="commandTimeout">Command timeout</param>
/// <returns>string[]</returns>
public static string[] GetStringValues(ConnectionStructure connectionStructure, string sqlQuery, SqlParameter[] commandParameters, int commandTimeout)
{
string[] ReturnResults = GetStringValues(GetConnectionString(connectionStructure), sqlQuery, commandParameters, commandTimeout);
return ReturnResults;
}
/// <summary>
/// Executes sql statement, puts results into DataReader
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="sqlQuery">SQL Statement to execute</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="objDataReader">Instance of SqlDataReader object</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader GetDataReader(ConnectionStructure connectionStructure, string sqlQuery, SqlParameter[] commandParameters, SqlDataReader objDataReader)
{
objDataReader = GetDataReader(GetConnectionString(connectionStructure), sqlQuery, commandParameters, objDataReader);
return objDataReader;
}
/// <summary>
/// Executes stored procedure, puts results into DataReader
/// </summary>
/// <param name="connectionStructure">Instance of ConnectionStructure structure that holds a connection string to a database</param>
/// <param name="storedProcName">Stored procedure name</param>
/// <param name="commandParameters">SQL parameters array</param>
/// <param name="objDataReader"></param>
/// <param name="commandTimeout">Command timeout</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader GetDataReaderStoredProc(ConnectionStructure connectionStructure, string storedProcName, SqlParameter[] commandParameters, SqlDataReader objDataReader, int commandTimeout)
{
objDataReader = GetDataReaderStoredProc(GetConnectionString(connectionStructure), storedProcName, commandParameters, objDataReader, commandTimeout);
return objDataReader;
}
