.NET：一个非常好用的SQL数据库处理类

来源：互联网发布：修理图片的软件编辑：程序博客网时间：2024/05/29 03:24
using System;using System.Collections.Generic;using System.Collections;using System.Collections.Specialized;using System.Runtime.Remoting.Messaging;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Text;public abstract class DBUtility {  //数据库连接字符串  protected static string connectionstring =  ConfigurationSettings.AppSettings["conStr"];  public static string conStr  {      get      {          return connectionstring;      }  }  public DBUtility()  {   //   // todo: 在此处添加构造函数逻辑   //  }  /// <summary>  /// 执行sql语句，返回影响的记录数  /// </summary>  /// <param name="sqlstring"></param>  /// <returns></returns>  public static int ExecuteSql(string sqlString)  {   using ( SqlConnection connection = new SqlConnection(connectionstring))   {        using (SqlCommand cmd = new SqlCommand(sqlString, connection))    {     try     {          connection.Open();        int rows=cmd.ExecuteNonQuery();        return rows;     }     catch(System.Data.SqlClient.SqlException e)     {            throw new Exception(e.Message);     }    }       }  }  /// <summary>  /// 执行两条sql语句，实现数据库事务。  /// </summary>  /// <param name="sqlstring1"></param>  /// <param name="sqlstring2"></param>  public static void ExecuteSqlTran(string sqlstring1,string sqlstring2)  {   using (SqlConnection connection = new SqlConnection(connectionstring))   {    connection.Open();    SqlCommand cmd = new SqlCommand();    cmd.Connection=connection;        SqlTransaction tx=connection.BeginTransaction();       cmd.Transaction=tx;        try    {          cmd.CommandText=sqlstring1;     cmd.ExecuteNonQuery();     cmd.CommandText=sqlstring2;     cmd.ExecuteNonQuery();          tx.Commit();         }    catch(System.Data.SqlClient.SqlException e)    {       tx.Rollback();     throw new Exception(e.Message);    }    finally    {     cmd.Dispose();     connection.Close();    }    }  }   /// <summary>  /// 执行多条sql语句，实现数据库事务，每条语句以“;”分割。  /// </summary>  /// <param name="sqlstringlist"></param>  public static void ExecuteSqlTran(string sqlstringlist)  {   using (SqlConnection conn = new SqlConnection(connectionstring))   {    conn.Open();    SqlCommand cmd = new SqlCommand();    cmd.Connection=conn;        SqlTransaction tr =conn.BeginTransaction();       cmd.Transaction=tr;        try    {        string [] split= sqlstringlist.Split(new char [] {';'});     foreach (string strsql in split)      {      if (strsql.Trim()!="")      {       cmd.CommandText=strsql;       cmd.ExecuteNonQuery();      }     }            tr.Commit();         }    catch(System.Data.SqlClient.SqlException e)    {       tr.Rollback();     throw new Exception(e.Message);    }   }  }   /// <summary>  /// 向数据库里插入图像格式的字段  /// </summary>  /// <param name="strsql"></param>  /// <param name="fs"></param>  /// <returns></returns>  public static int ExecuteSqlInsertImg(string strsql,byte[] fs)  {     using (SqlConnection connection = new SqlConnection(connectionstring))   {    SqlCommand cmd = new SqlCommand(strsql,connection);     System.Data.SqlClient.SqlParameter  myparameter = new System.Data.SqlClient.SqlParameter ( "@fs", SqlDbType.Image);    myparameter.Value = fs ;    cmd.Parameters.Add(myparameter);    try    {     connection.Open();     int rows=cmd.ExecuteNonQuery();     return rows;    }    catch(System.Data.SqlClient.SqlException e)    {         throw new Exception(e.Message);    }    finally    {     cmd.Dispose();     connection.Close();    }        }  }  /// <summary>  /// 执行一条计算查询结果语句，返回查询结果（整数）。  /// </summary>  /// <param name="strsql"></param>  /// <returns></returns>  public static int GetCount(string strsql)  {   using (SqlConnection connection = new SqlConnection(connectionstring))   {    SqlCommand cmd = new SqlCommand(strsql,connection);        try    {     connection.Open();     SqlDataReader result = cmd.ExecuteReader();     int i=0;     while(result.Read())     {      i=result.GetInt32(0);     }     result.Close();         return i;    }    catch(System.Data.SqlClient.SqlException e)    {             throw new Exception(e.Message);    }     finally    {     cmd.Dispose();     connection.Close();    }   }  }   /// <summary>  /// 执行一条计算查询结果语句，返回查询结果（object）。  /// </summary>  /// <param name="sqlstring"></param>  /// <returns></returns>  public static object GetSingleObj(string sqlstring)  {   using (SqlConnection connection = new SqlConnection(connectionstring))   {    SqlCommand cmd = new SqlCommand(sqlstring,connection);      try    {     connection.Open();     object obj = cmd.ExecuteScalar();     if((object.Equals(obj,null))||(object.Equals(obj,System.DBNull.Value)))     {           return null;     }     else     {      return obj;     }        }    catch(System.Data.SqlClient.SqlException e)    {         throw new Exception(e.Message);    }    finally    {     cmd.Dispose();     connection.Close();    }   }  }  /// <summary>  /// 执行查询语句，返回SqlDataReader  /// </summary>  /// <param name="strsql"></param>  /// <returns></returns>  public static SqlDataReader ExecuteReader(string strsql)  {      SqlConnection connection = new SqlConnection(connectionstring);   {    SqlCommand cmd = new SqlCommand(strsql,connection);     SqlDataReader myreader;       try    {     connection.Open();      myreader = cmd.ExecuteReader();     return myreader;    }    catch(System.Data.SqlClient.SqlException e)    {             throw new Exception(e.Message);    }      //finally    //{    // cmd.Dispose();    // connection.Close();    //}    }  }  public static SqlDataReader ExecuteReader(string strsql, IDataParameter[] parameters)  {      SqlConnection connection = new SqlConnection(connectionstring);      {          SqlCommand cmd = new SqlCommand(strsql, connection);          cmd.Parameters.AddRange(parameters);          SqlDataReader myreader;          try          {              connection.Open();              myreader = cmd.ExecuteReader();              return myreader;          }          catch (System.Data.SqlClient.SqlException e)          {              throw new Exception(e.Message);          }          //finally          //{          //    cmd.Dispose();          //    connection.Close();          //}      }  }    /// <summary>  /// 执行查询语句，返回dataset  /// </summary>  /// <param name="sqlstring"></param>  /// <returns></returns>  ///   public static DataSet Query(string sqlstring)  {   using (SqlConnection connection = new SqlConnection(connectionstring))   {    DataSet ds = new DataSet();    try    {     connection.Open();     SqlDataAdapter command = new SqlDataAdapter(sqlstring,connection);         command.Fill(ds,"ds");    }    catch(System.Data.SqlClient.SqlException ex)    {         throw new Exception(ex.Message);    }       return ds;   }     }      #region 存储过程操作  /// <summary>  /// 运行存储过程  /// </summary>  /// <param name="storedprocname"></param>  /// <param name="parameters"></param>  /// <returns></returns>  public static SqlDataReader RunProcedure(string storedprocname, IDataParameter[] parameters )  {   using (SqlConnection connection = new SqlConnection(connectionstring))   {    SqlDataReader returnreader;    connection.Open();    SqlCommand command = BuildQueryCommand( connection,storedprocname, parameters );    command.CommandType = CommandType.StoredProcedure;    returnreader = command.ExecuteReader();    //connection.Close();    return returnreader;   }  }  public static SqlDataReader RunProcedure(string storedprocname)  {      using (SqlConnection connection = new SqlConnection(connectionstring))      {          SqlDataReader returnreader;          connection.Open();          SqlCommand command = new SqlCommand(storedprocname, connection);          command.CommandType = CommandType.StoredProcedure;          returnreader = command.ExecuteReader();          //connection.Close();          return returnreader;      }  }  public static DataSet RunProcedure(string storedprocname, IDataParameter[] parameters, string tablename)  {   using (SqlConnection connection = new SqlConnection(connectionstring))   {    DataSet dataset = new DataSet();    connection.Open();    SqlDataAdapter sqlda = new SqlDataAdapter();    sqlda.SelectCommand = BuildQueryCommand(connection, storedprocname, parameters );    sqlda.Fill( dataset, tablename );    connection.Close();    return dataset;   }  }  public static int GetProcedureReturnValue(string storedprocname, IDataParameter[] parameters)  {      using (SqlConnection connection = new SqlConnection(connectionstring))      {          connection.Open();          SqlCommand command = BuildQueryCommand(connection, storedprocname, parameters);          SqlParameter rtnval = command.Parameters.Add("rval", SqlDbType.Int);          rtnval.Direction = ParameterDirection.ReturnValue;          command.ExecuteNonQuery();          return (int)rtnval.Value;      }  }    public static string GetProcedureOutputValue(string storedprocname, IDataParameter[] parameters, string outputParaName)    {        using (SqlConnection connection = new SqlConnection(connectionstring))        {            connection.Open();            SqlCommand command = BuildQueryCommand(connection, storedprocname, parameters);            SqlParameter rtnval = command.Parameters.Add(outputParaName, SqlDbType.VarChar, 500);            rtnval.Direction = ParameterDirection.Output;            command.ExecuteNonQuery();            return rtnval.Value.ToString();        }    }   private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedprocname, IDataParameter[] parameters)  {      SqlCommand command = new SqlCommand(storedprocname, connection);      command.CommandType = CommandType.StoredProcedure;      foreach (SqlParameter parameter in parameters)      {          command.Parameters.Add(parameter);      }      return command;  }    #endregion      }
0 5