/// <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); } }