SQL資料庫訪問類

using System;using System.Data;
using System.Data.SqlClient;
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;

/// <summary>
/// Execommd 的摘要說明
/// </summary>
public class Execommd
{
  private string _connectionString;
  SqlConnection SqlConnectionsifang = new SqlConnection();
  SqlCommand SqlCommandsifang = new SqlCommand();
  public Execommd()
  {
  //
  // TODO: 在此處添加建構函式邏輯
  //
  _connectionString = ConfigurationManager.ConnectionStrings["testConnectionString"].ConnectionString;//定義資料連線字串
  SqlConnectionsifang.ConnectionString = _connectionString;//初始化資料連線
  SqlCommandsifang.Connection = SqlConnectionsifang;//初始化SqlCommand
  }
  //
  //Execommdnone用於執行所有無傳回型別的SQL語句
  public void Execommdnone(string sqlstr)
  {
  try
  {
  if (SqlConnectionsifang.State.ToString() != "Open")
  {
  SqlConnectionsifang.Open();
  SqlCommandsifang.CommandText = sqlstr;
  SqlCommandsifang.ExecuteNonQuery();
  }
  }
  catch (SqlException e)
  {
  throw e;
  }
  finally
  {
  if (SqlConnectionsifang.State.ToString() != "Close")
  {
  SqlConnectionsifang.Close();
  }
  }
  }
  //Execommdint用於執行所有SQL語句,返回影響行數
  public int Execommdint(string sqlstr)
  {
  int num = 0;
  try
  {
  if (SqlConnectionsifang.State.ToString() != "Open")
  {
  SqlConnectionsifang.Open();
  SqlCommandsifang.CommandText = sqlstr;
  num = SqlCommandsifang.ExecuteNonQuery();
  }
  }
  catch (SqlException e)
  {
  throw e;
  }
  finally
  {
  if (SqlConnectionsifang.State.ToString() != "Close")
  {
  SqlConnectionsifang.Close();
  }
  }
  return num;

  }

 //

 //Execommdcount用於執行所有SQL語句,返回int類型首行首列

  public int Execommdcount(string sqlstr)
  {
  int num = 0;
  try
  {
  if (SqlConnectionsifang.State.ToString() != "Open")
  {
  SqlConnectionsifang.Open();
  SqlCommandsifang.CommandText = sqlstr;
  num = (int)SqlCommandsifang.ExecuteScalar();
  }
  }
  catch (SqlException e)
  {
  throw e;
  }
  finally
  {
  if (SqlConnectionsifang.State.ToString() != "Close")
  {
  SqlConnectionsifang.Close();
  }
  }
  return num;
  }
  //Execommdstring用於執行所有返回string類型首行首列的SQL語句
  public string Execommdstring(string sqlstr)
  {
  string result = "";
  try
  {
  if (SqlConnectionsifang.State.ToString() != "Open")
  {
  SqlConnectionsifang.Open();
  SqlCommandsifang.CommandText = sqlstr;
  result = SqlCommandsifang.ExecuteScalar().ToString();
  }
  }
  catch (Exception e)
  {
  throw e;
  }
  finally
  {
  if (SqlConnectionsifang.State.ToString() != "Close")
  {
  SqlConnectionsifang.Close();
  }
  }
  return result;
  }
 
  //Execommddatatable用於根據參數返回datatable
  public DataTable Execommddatatable(string sqlstr)
  {
  SqlDataAdapter da = new SqlDataAdapter(sqlstr, SqlConnectionsifang);
  DataSet ds = new DataSet();
  try
  {
  SqlConnectionsifang.Open();
  da.Fill(ds, "tbl");
  }
  catch
  {
  }
  finally
  {
  SqlConnectionsifang.Close();
  }
  return ds.Tables["tbl"];

  }

 //

 //Execommddataset用於根據參數返回dataset
  public DataSet Execommddataset(string sqlstr)
  {
  SqlDataAdapter da = new SqlDataAdapter(sqlstr, SqlConnectionsifang);
  DataSet ds = new DataSet();
  try
  {
  SqlConnectionsifang.Open();
  da.Fill(ds);
  }
  catch
  {
  }
  finally
  {
  SqlConnectionsifang.Close();
  }
  return ds;
  }
 //ExecommdReader用於根據參數返回ExecommdReader

  public SqlDataReader ExecommdReader(string sqlstr)
  {
  SqlCommand cm = new SqlCommand(sqlstr, SqlConnectionsifang);
  if (cm.Connection.State.ToString() != "Open")
  cm.Connection.Open();
  SqlDataReader sdr = cm.ExecuteReader();
  return sdr;

  }
 
  /// <summary>
  /// 帶代參數的sql語句，用於插入和更新 
  /// </summary>
  /// <param name="count">values個數</param>
  /// <param name="values">3個分別為"sql語句",參數名稱連結串,參數類型(資料庫中)連結串,參數的內容</param>
  /// <returns></returns>
  public bool ExecommdParams(int count,params string[] values)
  {
  try
  {
  string sqlstr = "";
  string ParamsStr = "";
  string ParamsType = "";
  int valueLenght = count - 3;
  string[] count_str_len = new string[valueLenght];
  for (int i = 0, j = 0; i < count; i++)
  {
  if (i == 0) //sql語句
  {
  sqlstr=values[i];
  }
  else if (i == 1) //參數名稱串
  {
  ParamsStr = values[i];
  }
  else if (i == 2) //參數類型串
  {
  ParamsType = values[i];
  }
  else
  {
  count_str_len[j]=values[i];//參數內容串
  }
   
  }

  string[] pamstr=ParamsStr.Split(','); //儲存參數名稱
  string[] pamtype = ParamsType.Split(',');//儲存參數資料庫類型
   
  SqlCommandsifang.CommandText = sqlstr;

  int pamstrlen = pamstr.Length; //參數個數
  int pamtypelen = pamtype.Length; //參數類型個數
  if ((pamstrlen == valueLenght) && (pamstrlen == valueLenght) && (valueLenght == pamtypelen)) //參數個數，參數類型個數，內容數相同
  {
  for (int i = 0; i < valueLenght; i++)
  {
  SqlCommandsifang.Parameters.AddWithValue(pamstr[i], pamtype[i]).Value = count_str_len[i];
  }

  SqlCommandsifang.Connection.Open();
  int rowsAffected = SqlCommandsifang.ExecuteNonQuery();
  SqlConnectionsifang.Close();
  SqlCommandsifang.Dispose();

  if (rowsAffected > 0)
  return true;
  else return false;

  }
  else
  {
  return false;
  }

  }
  catch (Exception ex)
  {
  throw ex;
  }
   
  }
   
  /// <summary>
  /// 執行無參預存程序
  /// </summary>
  /// <param name="ProName">預存程序名稱</param>
  /// <returns></returns>
  public DataSet ExecommdProcedure(string ProName)
  {
  SqlCommandsifang.CommandType = CommandType.StoredProcedure; //指定執行預存程序操作 
  SqlCommandsifang.CommandText = ProName; //預存程序名稱 
  SqlDataAdapter adapter = new SqlDataAdapter(SqlCommandsifang);
  DataSet ds;
  adapter.Fill(ds);
  return ds;
  }

   
  //執行有參預存程序
  public SqlDataAdapter ExecommdProcedureParameter(int count,string ProName, params string[] values)
  {
  //values格式
  // "@id,@name,@sex" "Char,DateTime,Float" "10,5,20"
   
   
  SqlCommandsifang.CommandType = CommandType.StoredProcedure; //指定執行預存程序操作 
  SqlCommandsifang.CommandText = ProName; //預存程序名稱 

   
  string ParamsStr = "";
  string ParamsType = "";
  string ParamsValue="";
  int valueLenght = count - 3;
  string[] count_str_len = new string[valueLenght];
  for (int i = 0, j = 0; i < count; i++)
  {
  if (i == 0) //參數串
  {
  ParamsStr = values[i];
  }
  else if (i == 1) //參數類型串
  {
  ParamsType = values[i];
  }
  else if (i == 2) //參數值串
  {
  ParamsValue = values[i];
  }
  else
  {
  count_str_len[j] = values[i];//參數內容
  }

  }

  string[] pamstr = ParamsStr.Split(','); //儲存每個參數
  string[] pamtype = ParamsType.Split(',');//儲存每個參數資料庫類型
  string[] pamValue = ParamsValue.Split(',');//儲存每個參數資料庫類型

  int pamstrlen = pamstr.Length; //參數個數
  int pamtypelen = pamtype.Length; //參數類型個數
  int pamValuelen = pamValue.Length;

   
   
  if ((pamstrlen == valueLenght && pamtypelen == valueLenght && pamValuelen == valueLenght) && (pamstrlen == pamtypelen && pamtypelen == pamValuelen && pamstrlen == pamValuelen))
  {
  for (int i = 0; i < valueLenght; i++)
  {
  //SqlParameter parid = new SqlParameter(pamstr[0], pamtype[0].ToString(),Convert.ToInt32(pamValue[0]));
  SqlParameter parid = new SqlParameter();
  parid.ParameterName = pamstr[i];
  {
  if (pamtype[i] == "Bit")
  parid.SqlDbType = SqlDbType.Bit;
  else if (pamtype[i] == "Char")
  parid.SqlDbType = SqlDbType.Char;
  else if (pamtype[i] == "DateTime")
  parid.SqlDbType = SqlDbType.DateTime;
  else if (pamtype[i] == "Float")
  parid.SqlDbType = SqlDbType.Float;
  else if (pamtype[i] == "Int")
  parid.SqlDbType = SqlDbType.Int;
  else if (pamtype[i] == "NVarChar")
  parid.SqlDbType = SqlDbType.NVarChar;
  else if (pamtype[i] == "Text")
  parid.SqlDbType = SqlDbType.Text;
  else if (pamtype[i] == "VarChar")
  parid.SqlDbType = SqlDbType.VarChar;
  else if (pamtype[i] == "NText")
  parid.SqlDbType = SqlDbType.NText;
  }
  if (pamValue[i] != "")
  parid.Size = Convert.ToInt32(pamValue[i]);
  parid.Value = count_str_len[i];
  SqlCommandsifang.Parameters.Add(parid);
  }
   
  }
  SqlDataAdapter adapter = new SqlDataAdapter(SqlCommandsifang);
  return adapter;
   
  }

}