コード例 #1
0
ファイル: SQLHelper.cs プロジェクト: AgentTy/General
        /// <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));
        }
コード例 #2
0
ファイル: SQLHelper.cs プロジェクト: AgentTy/General
        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);
            }
        }
コード例 #3
0
ファイル: SQLHelper.cs プロジェクト: AgentTy/General
        /// <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);
            }
        }