<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无码精品色午夜| 久久久久亚洲AV无码专区桃色| 久久精品国产亚洲AV麻豆王友容 | 久久亚洲精精品中文字幕| 在线免费观看h片| 自拍偷自拍亚洲精品被多人伦好爽| 成人a毛片免费视频观看| 亚洲毛片网址在线观看中文字幕 | 日韩亚洲精品福利| 免费一级毛suv好看的国产网站| 国产大片91精品免费观看男同| 精品国产亚洲AV麻豆| 亚洲第一黄色网址| 中国性猛交xxxxx免费看| 亚洲AV无码一区二区二三区入口| 久久综合九色综合97免费下载| 亚洲福利在线视频| 无码av免费毛片一区二区| 亚洲综合一区无码精品| 免费在线观看中文字幕| 国产精品免费看久久久香蕉| 国产l精品国产亚洲区在线观看| 免费A级毛片无码A∨| 亚洲乱码在线观看| 亚洲成av人片不卡无码久久| 中国videos性高清免费| 亚洲人成免费电影| 亚洲国产成人久久笫一页 | 免费成人福利视频| 久久无码av亚洲精品色午夜| 亚洲偷自拍拍综合网| 久久99热精品免费观看牛牛| 久久国产亚洲精品| 国产亚洲精品AA片在线观看不加载 | 亚洲AV日韩综合一区| 亚洲精品少妇30p| 大地资源在线观看免费高清| 国产高清视频免费在线观看| 毛片A级毛片免费播放| 午夜男人一级毛片免费 | 久久精品国产亚洲精品|