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 閱讀(165) 評論(0)  編輯  收藏

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


          網站導航:
           

          常用鏈接

          留言簿(5)

          隨筆分類

          隨筆檔案

          搜索

          •  

          最新評論

          閱讀排行榜

          評論排行榜

          主站蜘蛛池模板: 乌拉特中旗| 伊川县| 蒙阴县| 锡林郭勒盟| 辽中县| 盱眙县| 尚义县| 桦南县| 南丰县| 南宫市| 大足县| 宁安市| 安仁县| 武山县| 崇州市| 灵川县| 新源县| 台南市| 达尔| 兴文县| 思茅市| 霍城县| 临夏市| 瓮安县| 日土县| 庄河市| 攀枝花市| 永善县| 嘉鱼县| 新建县| 静安区| 棋牌| 雅江县| 郯城县| 旬邑县| 扬州市| 广元市| 西安市| 繁峙县| 都江堰市| 临沂市|