• 我封装的ADO.NET对数据库操作经典类


    using System;
    using System.Collections;
    using System.Collections.Specialized;
    using System.Runtime.Remoting.Messaging;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
    namespace LTP.SQLServerDAL
    {
     /// <summary>
     /// ADO.NET数据库操作基础类。
     /// </summary>
     public abstract class DbManagerSQL
     {
      //数据库连接字符串
      protected static string connectionString = ConfigurationSettings.AppSettings["ConnectionString"]; 
      public DbManagerSQL()
      {
       //
       // 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 (OdbcConnection conn = new OdbcConnection(connectionString))
       {
        conn.Open();
        OdbcCommand cmd = new OdbcCommand();
        cmd.Connection=conn;    
        OdbcTransaction tx=conn.BeginTransaction();   
        cmd.Transaction=tx;    
        try
        {  
         string [] split= SQLStringList.Split(new Char [] { ';'});
         foreach (string strsql in split)
         {
          if (strsql.Trim()!="")
          {
           cmd.CommandText=strsql;
           cmd.ExecuteNonQuery();
          }
         }       
         tx.Commit();     
        }
        catch(System.Data.Odbc.OdbcException E)
        {  
         tx.Rollback();
         throw new Exception(E.Message);
        }
       }
      }
      /// <summary>
      /// 执行带一个存储过程参数的的SQL语句。
      /// </summary>
      /// <param name="SQLString"></param>
      /// <param name="content"></param>
      /// <returns></returns>
      public static int ExecuteSql(string SQLString,string content)
      {    
       using (SqlConnection connection = new SqlConnection(connectionString))
       {
        SqlCommand cmd = new SqlCommand(SQLString,connection);  
        System.Data.SqlClient.SqlParameter  myParameter = new System.Data.SqlClient.SqlParameter ( "@content", SqlDbType.NText);
        myParameter.Value = content ;
        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>
      /// <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 GetSingle(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)
      {
       using (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();
        } 
       }
      }  
      /// <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;
       }
      }
      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;
       
      }  
      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;
       }
      }

      #endregion 
     }
    }

  • 相关阅读:
    mysql常用的语句示例
    node的模块机制
    mongodb的一些技术点
    php的权限设置流程
    linux常用命令大全
    通过centos7.2搭建个人博客--------------服务器篇
    详解vuex时光机
    详解位运算符的一些特点
    一位资深程序员大牛给予Java初学者的学习路线建议
    JDBC
  • 原文地址:https://www.cnblogs.com/ltp/p/289656.html
Copyright © 2020-2023  润新知