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

转载 2006年06月09日 19:10:00

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

相关文章推荐

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

using System;using System.Collections;using System.Collections.Specialized;using System.Runtime.Remo...

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

Posted on 2005-12-03 00:18 李天平 阅读(7467) 评论(11) 编辑 收藏 using System; using System.Coll...

封装的ADO.NET对数据库操作经典类 代码

using System;using System.Collections;using System.Collections.Specialized;using System.Runtime.Remo...
  • zz_mm
  • zz_mm
  • 2011-07-11 10:52
  • 565

C# ADO.NET_数据库操作的封装

1.App.config                             providerName="Syst...

ADO.NET通用数据库操作类

最近经常看到一些人在秀其多年来积累经验而成的数据库操作类,在此我也秀一把。 本人对ORM相关的东西是极不喜欢的,可能与平常接触到的项目所操作的数据量都较大、业务较复杂吧。另对那种通过自己编写实体类,...

ADO.NET数据库操作

  • 2008-09-23 15:36
  • 82KB
  • 下载

ADO.NET数据库操作

  • 2013-01-07 15:18
  • 1.63MB
  • 下载

关于ADO.NET数据库操作中的增删改查讲解

我们要连接数据库之后就要对它进行增删改查,这里我们就详细的分析一下吧。对于ADO.NET数据库操作Insert,Update,Delete等单向操作,对于插入、删除、修改等操作,由于是客户端应用程序向...
  • letIgo
  • letIgo
  • 2011-10-17 15:59
  • 1013
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)