<rt id="bn8ez"></rt>
<label id="bn8ez"></label>

  • <span id="bn8ez"></span>

    <label id="bn8ez"><meter id="bn8ez"></meter></label>

    c# SQLServer 數據庫連接

    using System;
    using System.Data;
    using System.Configuration;
    using System.Web;
    using System.Data.SqlClient;
    using System.Text;
    using System.Collections;
    namespace BaseProject.Data
    {

        public class DbAccess
        {
            private string _EditString = "if exists({0}) begin {1} end else begin {2} end";
            public string EditString
            {
                set { this._EditString = value; }
                get { return this._EditString; }
            }
            SqlConnection conn = null;
            SqlCommand cmd = null;
            public DbAccess()
            {
                //
                // TODO: 在此處添加構造函數邏輯
                //
                conn = new SqlConnection();
                conn.ConnectionString = Convert.ToString(ConfigurationManager.AppSettings["datasource"]); //Convert.ToString(System.Configuration.ConfigurationSettings.AppSettings["datasource"]);
                //conn.ConnectionString = "initial catalog=idyan_new;data source=.;user id=bt;password=btbtbtbt;Connect Timeout=5000";
                cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandTimeout = 0;

            }
            public DbAccess(string constr)
            {

                //
                // TODO: 在此處添加構造函數邏輯
                //
                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 = constr;// "initial catalog=idyan_new;data source=.;user id=bt;password=btbtbtbt";
                cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandTimeout = 0;

            }
            /// <summary>
            /// 獲取數據根據sql語句
            /// </summary>
            /// <param name="sql"></param>
            /// <returns></returns>
            public DataTable GetTable(string sql)
            {
                DataSet ds = new DataSet();

                try
                {
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    da.Fill(ds);
                }
                catch (Exception ex)
                {


                    return null;

                }
                return ds.Tables[0] ?? new DataTable();
            }
            /// <summary>
            /// 獲取數據根據sql語句
            /// </summary>
            /// <param name="sql"></param>
            /// <returns></returns>
            public DataSet GetDataSet(string sql)
            {
                DataSet ds = new DataSet();

                try
                {
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    da.Fill(ds);
                }
                catch (Exception ex)
                {


                    return null;

                }
                return ds;
            }
            /// <summary>
            /// 獲取數據根據sql語句
            /// </summary>
            /// <param name="sql"></param>
            /// <returns></returns>
            public DataSet GetDataSet(string sql, SqlParameter[] pas)
            {
                DataSet ds = new DataSet();

                try
                {
                    cmd.Parameters.Clear();
                    cmd.CommandText = sql;
                    foreach (SqlParameter pa in pas)
                    {
                        cmd.Parameters.Add(pa);
                    }
                    cmd.CommandType = CommandType.Text;
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    da.Fill(ds);
                }
                catch (Exception ex)
                {


                    return null;

                }
                return ds;
            }
            /// <summary>
            /// 獲取數據根據sql語句 帶參數 的
            /// </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;
                    cmd.CommandType = CommandType.Text;
                    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)
                {


                    return null;
                }
                return ds.Tables[0] ?? new DataTable();
            }
            /// <summary>
            /// 獲取數據根據sql語句 帶參數 的
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="pas"></param>
            /// <returns></returns>
            public DataTable GetProcTable(string procname, params SqlParameter[] pas)
            {
                DataSet ds = new DataSet();
                try
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = procname;
                    //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)
                {


                    return null;
                }
                return ds.Tables[0] ?? new DataTable();
            }
            /// <summary>
            /// 獲取數據根據sql語句 帶參數 的
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="pas"></param>
            /// <returns></returns>
            public DataTable GetProcCursorTable(string procname, params SqlParameter[] pas)
            {
                DataSet ds = new DataSet();
                try
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = procname;
                    //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)
                {


                    return null;
                }
                return ds.Tables[1] ?? new DataTable();
            }
            /// <summary>
            /// 獲取數據根據sql語句 帶參數 的
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="pas"></param>
            /// <returns></returns>
            public int GetProcState(string procname, params SqlParameter[] pas)
            {
                int state = 0;
                try
                {
                    OpenConn();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = procname;
                    cmd.Parameters.Clear();
                    foreach (SqlParameter temppa in pas)
                    {
                        cmd.Parameters.Add(temppa);
                    }
                    cmd.ExecuteNonQuery();
                    CloseConn();
                    state = Convert.ToInt32(pas[pas.Length - 1].Value);

                }
                catch
                {
                    return 0;
                }
                return state;
            }
            /// <summary>
            /// 獲取數據根據sql語句 帶參數 的
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="pas"></param>
            /// <returns></returns>
            public int GetProcStateNo(string procname, params SqlParameter[] pas)
            {
                int state = 0;
                try
                {
                    OpenConn();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = procname;
                    cmd.Parameters.Clear();
                    foreach (SqlParameter temppa in pas)
                    {
                        cmd.Parameters.Add(temppa);
                    }
                    cmd.ExecuteNonQuery();
                    CloseConn();
                    state = 1;

                    // state = Convert.ToInt32(pas[pas.Length - 1].Value);

                }
                catch
                {

                    return 0;
                }
                return state;
            }
            /// <summary>
            /// 存儲過程返回值的
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="pas"></param>
            /// <returns></returns>
            public string GetProcStateReturnValue(string procname, params SqlParameter[] pas)
            {
                string state = "";
                try
                {
                    OpenConn();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = procname;
                    cmd.Parameters.Clear();
                    foreach (SqlParameter temppa in pas)
                    {
                        cmd.Parameters.Add(temppa);
                    }
                    cmd.Parameters.AddWithValue("@Return_Value", "").Direction = ParameterDirection.ReturnValue;
                    cmd.ExecuteNonQuery();
                    state = Convert.ToString(cmd.Parameters["@Return_Value"].Value);
                    state = state == null ? ("") : (state);
                    CloseConn();


                    // state = Convert.ToInt32(pas[pas.Length - 1].Value);

                }
                catch
                {

                    return "";
                }
                return state;
            }
            /// <summary>
            /// 根據sql語句返回跟新狀態
            /// </summary>
            /// <param name="sql"></param>
            /// <returns></returns>
            public bool GetState(string sql)
            {
                bool succ = false;
                try
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;
                    OpenConn();
                    succ = cmd.ExecuteNonQuery() > 0 ? (true) : (false);
                    CloseConn();
                }
                catch (Exception ex)
                {


                    return false;
                }
                return succ;

            }
            /// <summary>
            /// 根據sql語句返回跟新狀態帶參數的
            /// </summary>
            /// <param name="sql">sql語句</param>
            /// <param name="pas">參數的集合</param>
            /// <returns></returns>
            public bool GetState(string sql, params SqlParameter[] pas)
            {
                bool succ = false;
                try
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;
                    cmd.Parameters.Clear();

                    foreach (SqlParameter temppa in pas)
                    {
                        cmd.Parameters.Add(temppa);
                    }
                    OpenConn();
                    succ = cmd.ExecuteNonQuery() > 0 ? (true) : (false);
                    CloseConn();
                }
                catch
                {

                    // this.ShowError(ex.Message);
                    //using (System.IO.StreamWriter sw = new System.IO.StreamWriter("D:\\error2008.txt"))
                    //{
                    //    sw.Write(ex.Message);
                    //    sw.Flush();
                    //}
                    return false;
                }
                return succ;

            }
            /// <summary>
            /// 根據sql語句返回第一個單元格的數據
            /// </summary>
            /// <param name="sql"></param>
            /// <returns></returns>
            public string GetOne(string sql)
            {
                string res = "";
                try
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;
                    OpenConn();
                    res = cmd.ExecuteScalar() == null ? ("") : (Convert.ToString(cmd.ExecuteScalar()));
                    CloseConn();
                }
                catch (Exception ex)
                {


                    return null;
                }
                return res;
            }
            /// <summary>
            ///  根據sql語句返回第一個單元格的數據帶參數的
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="pas"></param>
            /// <returns></returns>
            public string GetOne(string sql, params SqlParameter[] pas)
            {
                string res = "";
                try
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;
                    cmd.Parameters.Clear();

                    foreach (SqlParameter temppa in pas)
                    {
                        cmd.Parameters.Add(temppa);
                    }
                    OpenConn();
                    res = cmd.ExecuteScalar() == null ? ("") : (Convert.ToString(cmd.ExecuteScalar()));
                    CloseConn();
                }
                catch (Exception ex)
                {
                    CloseConn();

                    return null;
                }
                return res;
            }
            /// <summary>
            /// 返回數據的DataReader
            /// </summary>
            /// <param name="sql"></param>
            /// <returns></returns>
            public SqlDataReader GetDataReader(string sql)
            {
                SqlDataReader dr = null;
                try
                {
                    cmd.CommandType = CommandType.Text;
                    conn.Open();
                    cmd.CommandText = sql;
                    dr = cmd.ExecuteReader();
                }
                catch (Exception ex)
                {


                    return null;
                }
                return dr;
            }
            /// <summary>
            /// 返回數據的DataReader帶參數的
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="pas"></param>
            /// <returns></returns>
            public SqlDataReader GetDataReader(string sql, params SqlParameter[] pas)
            {
                SqlDataReader dr = null;
                try
                {
                    cmd.CommandType = CommandType.Text;
                    conn.Open();
                    cmd.Parameters.Clear();

                    foreach (SqlParameter temppa in pas)
                    {
                        cmd.Parameters.Add(temppa);
                    }

                    cmd.CommandText = sql;
                    dr = cmd.ExecuteReader();
                }
                catch (Exception ex)
                {


                    return null;
                }
                return dr;
            }
            /// <summary>
            /// 事務處理函數
            /// </summary>
            /// <param name="al"></param>
            /// <returns></returns>
            public bool GetTranState(ArrayList al)
            {
                cmd.CommandType = CommandType.Text;
                OpenConn();
                SqlTransaction tran = conn.BeginTransaction();
                cmd.Transaction = tran;
                try
                {
                    for (int i = 0; i < al.Count; i++)
                    {
                        cmd.CommandText = Convert.ToString(al[i]);
                        cmd.ExecuteNonQuery();
                    }

                    tran.Commit();
                    CloseConn();
                }
                catch
                {

                    tran.Rollback();
                    CloseConn();
                    return false;
                }
                return true;

            }
            /// <summary>
            /// 事務處理函數
            /// </summary>
            /// <param name="al"></param>
            /// <returns></returns>
            public bool GetTranStateParameter(ArrayList al)
            {
                cmd.CommandType = CommandType.Text;
                OpenConn();
                SqlTransaction tran = conn.BeginTransaction();
                cmd.Transaction = tran;
                try
                {
                    for (int i = 0; i < al.Count; i++)
                    {
                        SqlTranModel stm = (SqlTranModel)al[i];
                        cmd.CommandText = Convert.ToString(stm.Sql);
                        SqlParameter[] pas = stm.Pas;
                        cmd.Parameters.Clear();
                        foreach (SqlParameter temp in pas)
                        {
                            cmd.Parameters.Add(temp);
                        }
                        cmd.ExecuteNonQuery();
                    }

                    tran.Commit();
                    CloseConn();
                }
                catch
                {

                    tran.Rollback();
                    CloseConn();
                    return false;
                }
                return true;

            }
            /// <summary>
            /// 分頁函數
            /// </summary>
            /// <param name="pagesize"></param>
            /// <param name="columns"></param>
            /// <param name="tablename"></param>
            /// <param name="pid"></param>
            /// <param name="order"></param>
            /// <param name="current"></param>
            /// <returns></returns>
            public DataTable GetPageData(int current, int pagesize, string columns, string tablename, string pid, string where, string order)
            {
                current = current - 1 >= 0 ? (current - 1) : (0);
                string sql = string.Format("select top {0} {1} from {2} where 1=1 and {3} not in(select top {4}{3} from {2} where 1=1{5}  order by {6}){5} order by {6}", pagesize, columns, tablename, pid, current * pagesize, where, order);
                return GetTable(sql);
            }
            /// <summary>
            /// 分頁存儲過程的調用
            /// </summary>
            /// <param name="current"></param>
            /// <param name="pagesize"></param>
            /// <param name="columns"></param>
            /// <param name="tablename"></param>
            /// <param name="pid"></param>
            /// <param name="where"></param>
            /// <param name="order"></param>
            /// <returns></returns>
            public DataTable GetProcPageData(int current, int pagesize, string columns, string tablename, string pid, string where, string order, string ordertype)
            {
                SqlParameter[] pas = { new SqlParameter("@PageIndex", current), new SqlParameter("@PageSize", pagesize), new SqlParameter("@Columns", columns), new SqlParameter("@Tablename", tablename), new SqlParameter("@Where", where), new SqlParameter("@Order", order), new SqlParameter("@OrderType", ordertype), new SqlParameter("@Pid", pid) };
                return GetProcTable("Pages", pas);
                //current = current - 1 >= 0 ? (current - 1) : (0);
                //string sql = string.Format("select top {0} {1} from {2} where 1=1 and {3} not in(select top {4}{3} from {2} where 1=1{5}  order by {6}){5} order by {6}", pagesize, columns, tablename, pid, current * pagesize, where, order);
                //return GetTable(sql);
            }
            /// <summary>
            /// 分頁存儲過程的調用
            /// </summary>
            /// <param name="current"></param>
            /// <param name="pagesize"></param>
            /// <param name="columns"></param>
            /// <param name="tablename"></param>
            /// <param name="pid"></param>
            /// <param name="where"></param>
            /// <param name="order"></param>
            /// <returns></returns>
            public DataTable GetProcData(int current, int pagesize, string columns, string tablename, string pid, string where, string order, string resultCount, string distinct)
            {
                SqlParameter[] pas = { new SqlParameter("@TableNames", SqlDbType.NVarChar, 200), new SqlParameter("@PrimaryKey", SqlDbType.NVarChar, 100), new SqlParameter("@Order", SqlDbType.NVarChar, 200), new SqlParameter("@CurrentPage", SqlDbType.Int), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@Fields", SqlDbType.NVarChar, 800), new SqlParameter("@Filter", SqlDbType.NVarChar, 1000), new SqlParameter("@ResultCount", SqlDbType.NVarChar, 12), new SqlParameter("@distinct", SqlDbType.NVarChar, 12) };
                pas[0].Value = tablename;
                pas[1].Value = pid;
                pas[2].Value = order;
                pas[3].Value = current;
                pas[4].Value = pagesize;
                pas[5].Value = columns;
                pas[6].Value = where;
                pas[7].Value = resultCount;
                pas[8].Value = distinct;
                return this.GetProcTable("Pages", pas);
                //SqlParameter[] parameters = {
                //                                new SqlParameter("@TableNames",SqlDbType.VarChar,8000),
                //                                new SqlParameter("@PrimaryKey", SqlDbType.VarChar,8000),
                //                                new SqlParameter("@Order", SqlDbType.VarChar,8000),
                //                                new SqlParameter("@CurrentPage",SqlDbType.Int),
                //                                new SqlParameter("@PageSize", SqlDbType.Int),
                //                                new SqlParameter("@Fields",SqlDbType.VarChar,8000),
                //                                new SqlParameter("@Filter", SqlDbType.VarChar,8000),
                //                                new SqlParameter("@Group", SqlDbType.VarChar,8000)
                //                            };
                //parameters[0].Value = tablename;
                //parameters[1].Value = pid;
                //parameters[2].Value = order;
                //parameters[3].Value = current;
                //parameters[4].Value = pagesize;
                //parameters[5].Value = columns;
                //parameters[6].Value = where;
                //parameters[7].Value = "";
                //DataSet ds = DbHelperSQL.RunProcedure("Pages", parameters, "data");

                //return ds.Tables["data"];//
                //return GetProcTable("Pages", pas);
                //current = current - 1 >= 0 ? (current - 1) : (0);
                //string sql = string.Format("select top {0} {1} from {2} where 1=1 and {3} not in(select top {4}{3} from {2} where 1=1{5}  order by {6}){5} order by {6}", pagesize, columns, tablename, pid, current * pagesize, where, order);
                //return GetTable(sql);
            }
            /// <summary>
            /// 分頁存儲過程的調用
            /// </summary>
            /// <param name="current"></param>
            /// <param name="pagesize"></param>
            /// <param name="columns"></param>
            /// <param name="tablename"></param>
            /// <param name="pid"></param>
            /// <param name="where"></param>
            /// <param name="order"></param>
            /// <returns></returns>
            public DataTable GetProcAdminData(int current, int pagesize, string columns, string tablename, string pid, string where, string order, string resultCount, string distinct)
            {
                SqlParameter[] pas = { new SqlParameter("@TableNames", SqlDbType.NVarChar, 200), new SqlParameter("@PrimaryKey", SqlDbType.NVarChar, 100), new SqlParameter("@Order", SqlDbType.NVarChar, 200), new SqlParameter("@CurrentPage", SqlDbType.Int), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@Fields", SqlDbType.NVarChar, 800), new SqlParameter("@Filter", SqlDbType.NVarChar, 200), new SqlParameter("@ResultCount", SqlDbType.NVarChar, 24), new SqlParameter("@Distinct", SqlDbType.NVarChar, 12) };
                pas[0].Value = tablename;
                pas[1].Value = pid;
                pas[2].Value = order;
                pas[3].Value = current;
                pas[4].Value = pagesize;
                pas[5].Value = columns;
                pas[6].Value = where;
                pas[7].Value = resultCount;
                pas[8].Value = distinct;
                return this.GetProcTable("Pages", pas);

            }
            /// <summary>
            /// 打開連接
            /// </summary>
            public void OpenConn()
            {
                if (conn.State != ConnectionState.Open)
                {
                    try
                    {
                        conn.Open();
                    }
                    catch (Exception ex)
                    {


                        return;
                    }
                }
            }
            /// <summary>
            /// 關閉連接
            /// </summary>
            public void CloseConn()
            {
                if (conn.State != ConnectionState.Closed)
                {
                    try
                    {
                        conn.Close();

                    }
                    catch (Exception ex)
                    {


                        return;
                    }
                }
            }
            public void DisposeConn()
            {
                try
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        try
                        {
                            conn.Close();

                        }
                        catch (Exception ex)
                        {


                            return;
                        }
                    }
                }
                catch
                {

                }
            }

     

        }
        public class SqlTranModel
        {
            private string _sql;
            public string Sql
            {
                set { this._sql = value; }
                get { return this._sql; }
            }
            private SqlParameter[] _pas;
            public SqlParameter[] Pas
            {
                set { this._pas = value; }
                get { return this._pas; }
            }
        }
    }

    謝謝大家的支持,幫我點下需要的廣告謝謝我會更加努力整理我的百科服務大家
    posted on 2009-04-02 18:33 sanmao 閱讀(162) 評論(0)  編輯  收藏

    只有注冊用戶登錄后才能發表評論。


    網站導航:
     

    常用鏈接

    留言簿(5)

    隨筆分類

    隨筆檔案

    搜索

    •  

    最新評論

    閱讀排行榜

    評論排行榜

    主站蜘蛛池模板: 亚洲日韩中文字幕一区| 久久久久av无码免费网| 亚洲丁香婷婷综合久久| 亚洲午夜免费视频| 亚洲午夜久久久久妓女影院| 色吊丝永久在线观看最新免费| h视频在线免费看| 香蕉视频在线免费看| jzzijzzij在线观看亚洲熟妇| 亚洲成人福利网站| 91亚洲精品视频| 亚洲va无码va在线va天堂| 亚洲国产电影av在线网址| 国产成人无码免费视频97 | 永久黄网站色视频免费直播| 精品熟女少妇av免费久久| 中文字幕乱码一区二区免费| 国产精品免费视频观看拍拍 | 国产传媒在线观看视频免费观看| 日韩精品成人无码专区免费| h片在线免费观看| 久久精品国产免费观看三人同眠 | 亚洲精品综合久久中文字幕| 亚洲五月六月丁香激情| 亚洲AV乱码一区二区三区林ゆな| 亚洲国产精品一区二区成人片国内| 最新精品亚洲成a人在线观看| 亚洲AV无码一区二区三区在线观看| 国产美女a做受大片免费| 青青青国产免费一夜七次郎| 欧洲精品免费一区二区三区| 免费理论片51人人看电影| 日本无吗免费一二区| 国产嫩草影院精品免费网址| 免费一级e一片在线播放| 吃奶摸下高潮60分钟免费视频| 免费午夜爽爽爽WWW视频十八禁| 免费国产人做人视频在线观看| 免费大黄网站在线观| 亚洲精品无码99在线观看| 亚洲综合熟女久久久30p|