/// <summary>
/// Executes a SqlCommand and attaches a new connection if one hasn't been attached.
/// </summary>
/// <param name="cmd">SQLCommand - A valid command object with or without a Connection</param>
/// <param name="conn">SqlConn - A connection context object that will be used if a SQLConnection isnt already attached</param>
/// <param name="ConnectionStringName">ConnectionStringName - A valid connection string defined in the App.config file</param>
/// <returns>int - Represents the number of rows effected</returns>
public static int ExecuteNonQuery(SqlCommand cmd, SqlConn conn, String ConnectionStringName = null)
{
if (conn == null || String.IsNullOrEmpty(conn.ConnectionString))
{
if (!String.IsNullOrWhiteSpace(ConnectionStringName))
{
conn = new SqlConn(DBConnection.GetConnectionString(ConnectionStringName));
}
}
if (cmd.Connection == null)
{
cmd.Connection = DBConnection.GetOpenConnection(conn.ConnectionString);
}
return(ExecuteNonQuery(cmd, true));
}
public static List <string> ExecuteList(SqlCommand cmd, SqlConn conn)
{
#region Manage Connection
if (cmd == null)
{
throw new ArgumentNullException("cmd");
}
if (cmd.Connection == null)
{
cmd.Connection = DBConnection.GetOpenConnection(conn.ConnectionString);
}
else if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
#endregion
// Create the DataAdapter & DataSet
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
#region Execute
DataSet ds = new DataSet();
// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);
#endregion
#region Manage Connection
cmd.Connection.Close();
#endregion
#region Populate List
List <string> objList = new List <string>();
foreach (DataRow row in ds.Tables[0].Rows)
{
objList.Add(row[0].ToString());
}
#endregion
return(objList);
}
}
/// <summary>
/// Executes a SqlCommand and attaches a new connection if one hasn't been attached.
/// </summary>
/// <param name="cmd">SQLCommand - A valid command object with or without a Connection</param>
/// <param name="conn">SqlConn - A connection context object that will be used if a SQLConnection isnt already attached</param>
/// <param name="ConnectionStringName">ConnectionStringName - A valid connection string defined in the App.config file</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataset(SqlCommand cmd, SqlConn conn, String ConnectionStringName = null)
{
if (cmd == null)
{
throw new ArgumentNullException("cmd");
}
// Create the DataAdapter & DataSet
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
if (conn == null || String.IsNullOrEmpty(conn.ConnectionString))
{
if (!String.IsNullOrWhiteSpace(ConnectionStringName))
{
conn = new SqlConn(DBConnection.GetConnectionString(ConnectionStringName));
}
}
// Fill the DataSet
try
{
if (cmd.Connection == null)
{
cmd.Connection = DBConnection.GetOpenConnection(conn.ConnectionString);
}
da.Fill(ds);
cmd.Connection.Close();
}
catch (SqlException ex)
{
if ((int)ex.Class <= 16)
{
string strErr = "A SQL Server error occurred while trying to execute the command (" + cmd.CommandText + ").\n";
strErr += ex.Message + "\n";
strErr += GetQueryString(cmd);
throw new Exception(strErr, ex);
}
else if (ex.Message.ToLower().Contains("a transport-level error has occurred"))
{
throw new Exception(ex.Message + " : " + ConnectionStringName, ex);
}
else
{
throw new Exception(ex.Message + " : " + ConnectionStringName, ex);
}
}
catch (System.Exception ex)
{
string strErr = "An error occured while communicating with SQL Server (" + cmd.CommandText + ").\n";
strErr += ex.Message + "\n";
strErr += GetQueryString(cmd);
throw new Exception(strErr, ex);
}
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
// Return the dataset
return(ds);
}
}
