【轉(zhuǎn)】c# 數(shù)據(jù)庫的 sql 參數(shù)封裝類的編寫

球球圓圓豆豆 2015-12-16

展開全文

本文轉(zhuǎn)自：http://xyz610561646.web64./html/200712/85/13073.htm
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Text;
namespace ChinaSite.classes
{
    public class DbAccess
    {
        SqlConnection conn = null;
        SqlCommand cmd = null;
        public DbAccess()
        {
            //
            // TODO: 在此處添加構(gòu)造函數(shù)邏輯
            //
            conn = new SqlConnection();
            //conn.ConnectionString = "initial catalog=pubs;data source=.;user id=sa;password=";
            //conn.ConnectionString = Convert.ToString(System.Configuration.ConfigurationSettings.AppSettings["datasource"]);
            conn.ConnectionString = Convert.ToString(System.Configuration.ConfigurationManager.AppSettings["datasource"]);
            cmd = new SqlCommand();
            cmd.Connection = conn;
        }
        /// <summary>
        /// 獲取數(shù)據(jù)根據(jù)sql語句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetTable(string sql)
        {
            DataSet ds = new DataSet();
            try
            {
                cmd.CommandText = sql;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                this.ShowError(ex.Message);
                return null;
            }
            return ds.Tables[0] ?? new DataTable();
        }
        /// <summary>
        /// 獲取數(shù)據(jù)根據(jù)sql語句帶參數(shù) 的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public DataTable GetTable(string sql, params SqlParameter[] pas)
        {
            DataSet ds = new DataSet();
            try
            {
                cmd.CommandText = sql;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                cmd.Parameters.Clear();
                foreach (SqlParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }

                da.Fill(ds);
            }
            catch (Exception ex)
            {
                this.ShowError(ex.Message);
                return null;
            }
            return ds.Tables[0] ?? new DataTable();
        }
        /// <summary>
        /// 根據(jù)sql語句返回跟新狀態(tài)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool GetState(string sql)
        {
            bool succ = false;
            try
            {
                cmd.CommandText = sql;
                conn.Open();
                succ = cmd.ExecuteNonQuery() > 0 ? (true) : (false);
                conn.Close();
            }
            catch (Exception ex)
            {
                this.ShowError(ex.Message);
                return false;
            }
            return succ;
        }
        /// <summary>
        /// 根據(jù)sql語句返回跟新狀態(tài)帶參數(shù)的
        /// </summary>
        /// <param name="sql">sql語句</param>
        /// <param name="pas">參數(shù)的集合</param>
        /// <returns></returns>
        public bool GetState(string sql, params SqlParameter[] pas)
        {
            bool succ = false;
            try
            {
                cmd.CommandText = sql;
                cmd.Parameters.Clear();
                foreach (SqlParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }
                conn.Open();
                succ = cmd.ExecuteNonQuery() > 0 ? (true) : (false);
                conn.Close();
            }
            catch (Exception ex)
            {
                this.ShowError(ex.Message);
                return false;
            }
            return succ;
        }
        /// <summary>
        /// 根據(jù)sql語句返回第一個單元格的數(shù)據(jù)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string GetOne(string sql)
        {
            string res = "";
            try
            {
                cmd.CommandText = sql;
                conn.Open();
                res = cmd.ExecuteScalar() == null ? ("") : (Convert.ToString(cmd.ExecuteScalar()));
                conn.Close();
            }
            catch (Exception ex)
            {
                this.ShowError(ex.Message);
                return null;
            }
            return res;
        }
        /// <summary>
        /// 根據(jù)sql語句返回第一個單元格的數(shù)據(jù)帶參數(shù)的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public string GetOne(string sql, params SqlParameter[] pas)
        {
            string res = "";
            try
            {
                cmd.CommandText = sql;
                cmd.Parameters.Clear();
                foreach (SqlParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }
                conn.Open();
                res = cmd.ExecuteScalar() == null ? ("") : (Convert.ToString(cmd.ExecuteScalar()));
                conn.Close();
            }
            catch (Exception ex)
            {
                this.ShowError(ex.Message);
                return null;
            }
            return res;
        }
        /// <summary>
        /// 返回數(shù)據(jù)的DataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public SqlDataReader GetDataReader(string sql)
        {
            SqlDataReader dr = null;
            try
            {
                conn.Open();
                cmd.CommandText = sql;
                dr = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                this.ShowError(ex.Message);
                return null;
            }
            return dr;
        }
        /// <summary>
        /// 返回數(shù)據(jù)的DataReader帶參數(shù)的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public SqlDataReader GetDataReader(string sql, params SqlParameter[] pas)
        {
            SqlDataReader dr = null;
            try
            {
                conn.Open();
                cmd.Parameters.Clear();
                foreach (SqlParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }
                cmd.CommandText = sql;
                dr = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                this.ShowError(ex.Message);
                return null;
            }
            return dr;
        }
        /// <summary>
        /// 打開連接
        /// </summary>
        public void OpenConn()
        {
            if (conn.State != ConnectionState.Open)
            {
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {
                    this.ShowError(ex.Message);
                    return;
                }
            }
        }
        /// <summary>
        /// 關(guān)閉連接
        /// </summary>
        public void CloseConn()
        {
            if (conn.State != ConnectionState.Closed)
            {
                try
                {
                    conn.Close();
                    cmd = null;
                    conn = null;
                }
                catch (Exception ex)
                {
                    this.ShowError(ex.Message);
                    return;
                }
            }
        }
        /// <summary>
        /// 彈出錯誤的信息
        /// </summary>
        /// <param name="err"></param>
        public void ShowError(string err)
        {
            System.Web.HttpContext.Current.Response.Write(Script(err, ""));
        }
        /// <summary>
        /// 顯示信息
        /// </summary>
        /// <param name="err"></param>
        public void ShowMessage(string mes, string loc)
        {
            System.Web.HttpContext.Current.Response.Write(Script(mes, loc));
        }
        /// <summary>
        /// javascript腳本
        /// </summary>
        /// <param name="mess"></param>
        /// <param name="loc"></param>
        /// <returns></returns>
        public string Script(string mess, string loc)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<script language='javascript'>");
            sb.Append("alter('");
            sb.Append(mess);
            sb.Append("');");
            sb.Append(loc);
            sb.Append("</script>");
            return sb.ToString();
        }
    }
}