C# 執行（存儲過程帶數組）和執行sql 帶 數組參數

   //數據庫連接字符串(web.config來配置)，可以動態更改connectionString支持多數據庫.        

        public string connectionString = PubConstant.ConnectionString;              

        public DbHelperSQLP()

        {           

        }

        public DbHelperSQLP(string ConnectionString)

        {

            connectionString = ConnectionString;   

        }

  //sql帶參數的方法

 

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)

        {

            if (conn.State != ConnectionState.Open)

                conn.Open();

            cmd.Connection = conn;

            cmd.CommandText = cmdText;

            if (trans != null)

                cmd.Transaction = trans;

            cmd.CommandType = CommandType.Text;//cmdType;

            if (cmdParms != null)

            {

 

 

                foreach (SqlParameter parameter in cmdParms)

                {

                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&

                        (parameter.Value == null))

                    {

                        parameter.Value = DBNull.Value;

                    }

                    cmd.Parameters.Add(parameter);

                }

            }

        }

    /// <summary>

        /// 執行SQL語句，返回影響的記錄數

        /// </summary>

        /// <param name="SQLString">SQL語句</param>

        /// <returns>影響的記錄數</returns>

        public int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)

        {

            using (SqlConnection connection = new SqlConnection(connectionString))

            {

                using (SqlCommand cmd = new SqlCommand())

                {

                    try

                    {

                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);

                        int rows = cmd.ExecuteNonQuery();

                        cmd.Parameters.Clear();

                        return rows;

                    }

                    catch (System.Data.SqlClient.SqlException e)

                    {

                        throw e;

                    }

                }

            }

        }

 

DAL层方法

DbHelperSQLP DBHelp = new DbHelperSQLP(PubConstant.GetConnectionString("HelpdeskConnectionString"));

        public  int AddArea(Area area)

        {

            string sql =

                "INSERT Area (AreaName, CountryCode)" +

                "VALUES (@AreaName, @CountryCode)";

 

            sql += " ; SELECT @@IDENTITY";

 

            try

            {

                SqlParameter[] para = new SqlParameter[]

                     {

                           new SqlParameter("@AreaName", area.AreaName),

                           new SqlParameter("@CountryCode", area.CountryCode)

                     };

 

                int newId = DBHelp.ExecuteSql(sql, para);

                return newId;

            }

            catch (Exception e)

            {

                Console.WriteLine(e.Message);

                throw e;

            }

        }

********************************************************************

  /// <summary>

        /// 構建 SqlCommand 對像(用來返回一個結果集，而不是一個整數值)

        /// </summary>

        /// <param name="connection">數據庫連接</param>

        /// <param name="storedProcName">存儲過程名</param>

        /// <param name="parameters">存儲過程參數</param>

        /// <returns>SqlCommand</returns>

        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)

        {

            SqlCommand command = new SqlCommand(storedProcName, connection);

            command.CommandType = CommandType.StoredProcedure;

            foreach (SqlParameter parameter in parameters)

            {

                if (parameter != null)

                {

                    // 檢查未分配值的輸出參數,將其分配以DBNull.Value.

                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&

                        (parameter.Value == null))

                    {

                        parameter.Value = DBNull.Value;

                    }

                    command.Parameters.Add(parameter);

                }

            }

 

            return command;

        }

 

 

-------------------------------------------------------------------------------

 /// <summary>

        /// 執行存儲過程

        /// </summary>

        /// <param name="storedProcName">存儲過程名</param>

        /// <param name="parameters">存儲過程參數</param>

        /// <param name="tableName">DataSet結果中的表名</param>

        /// <returns>DataSet</returns>

        public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)

        {

            using (SqlConnection connection = new SqlConnection(connectionString))

            {

                DataSet dataSet = new DataSet();

                connection.Open();

                SqlDataAdapter sqlDA = new SqlDataAdapter();

                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);

                sqlDA.Fill(dataSet, tableName);

                connection.Close();

                return dataSet;

            }

        }