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;
   
  }

}