sqldbhelper.cs

guan yu pai ke xi tong de ruan jian

using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using System.Configuration;

namespace Y2T03.CourseScheduler.CourseDAL
{
    
    /// <summary>
    /// SQL Server 助手类
    /// </summary>
    public class SQLDBHelper
    {
        //private static readonly string connectString = @"Data Source=.\sqlexpress;Initial Catalog=CourseDB;Integrated Security=True";
        private static readonly string connectString = ConfigurationManager.ConnectionStrings["SQL"].ConnectionString;


        #region 返回带参数的受影响行数 ( INSERT, UPDATE, DELETE)
        /// <summary>
        /// 返回带参数的受影响行数
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="paramlist">参数列表</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string sql, SqlParameter[] paramlist)
        {
            int rows = 0;

            using (SqlConnection conn = new SqlConnection(connectString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                SqlCommand command = new SqlCommand(sql, conn);

                if (paramlist != null)
                {
                    command.Parameters.AddRange(paramlist);
                }

                rows = command.ExecuteNonQuery();

                command.Parameters.Clear();
            }

            return rows;
        }
        #endregion

        #region 返回不带参数的受影响行数 ( INSERT, UPDATE, DELETE)
        /// <summary>
        /// 返回不带参数的受影响行数
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }
        #endregion

        #region 读取带参数的结果集
        /// <summary>
        /// 读取带参数的结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="paramlist">参数列表</param>
        /// <returns>结果集</returns>
        public static SqlDataReader ExecuteReader(string sql, SqlParameter[] paramlist)
        {
            SqlConnection conn = new SqlConnection(connectString);
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            SqlCommand command = new SqlCommand(sql, conn);

            if (paramlist != null)
            {
                command.Parameters.AddRange(paramlist);
            }

            SqlDataReader reader = command.ExecuteReader();

            command.Parameters.Clear();

            return reader;
        }
        #endregion

        #region 读取不带参数的结果集
        /// <summary>
        /// 读取不带参数的结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>结果集</returns>
        public static SqlDataReader ExecuteReader(string sql)
        {
            return ExecuteReader(sql, null);
        }
        #endregion

        #region 返回操作的最新记录
        /// <summary>
        /// 返回操作的最新记录
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="paramlist">参数列表</param>
        /// <returns>object对象</returns>
        public static object ExecuteScalar(string sql, SqlParameter[] paramlist)
        {
            object obj = null;

            using (SqlConnection conn = new SqlConnection(connectString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                SqlCommand command = new SqlCommand(sql, conn);

                if (paramlist != null)
                {
                    command.Parameters.AddRange(paramlist);
                }

                obj = command.ExecuteScalar();

                command.Parameters.Clear();
            }

            return obj;
        }
        #endregion

        #region 数据填充,返回 DataTable
        /// <summary>
        /// 数据填充
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="paramlist">参数列表</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTable(string sql, SqlParameter[] paramlist)
        {
            DataTable dataTable = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                SqlCommand command = new SqlCommand(sql, conn);
                if (paramlist != null)
                {
                    command.Parameters.AddRange(paramlist);
                }

                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(command);
                sqlDataAdapter.Fill(dataTable);
                command.Parameters.Clear();
            }

            return dataTable;
        }
        #endregion
    }
}