数据库操作的一般类：DB.Manager

数据库操作的一般类：DB.Manager 

 

/* ----------------------------------------------------------------  

        // 文件功能描述：数据库操作类。
        // 首先你要修改 配置数据库连接字符串：ConnectionString，即是以下这个语句：
 private static string ConnectionString = "Data Source=localhost;Initial Catalog=DataBaseName;User Id=sa;Password=;";
        改成你自己的ConnectionString
 
        // 对本DB.Manager类的应用如下：
 * 
 *           1.如果你要进行Insert操作，则只要在你要Insert的地方写 ：
              string insertString="Insert into tableName ...";//写你的关于Insert的SQL语句；
              DB.Manager.ExecuteSQL(insertString);
 
             2.如果你想知道Insert操作是否成功，则类似以上：
              string insertString="Insert into tableName ...";//写你的关于Insert的SQL语句；
              bool ok=DB.Manager.ExecuteSQL(insertString);
              if(ok) {// 这里写Insert成功的语句}
 
             3.Select操作，并用GridView显示：
               string selectString="Select * from ...";//写你的关于Select的SQL语句；                
               GridView1.DataSource=DB.Manager.GetDataTable(selectString);
               GridView1.DataBind();
            
             其他Update、Delete操作类似Insert。
//---------------------------------------------------------------- */

using  System;
using  System.Data.SqlClient;
using  System.Data;
using  System.Configuration;
using  System.Xml;

namespace  DB
{
     ///   <summary>
     ///  数据库操作类(数据层)。
     ///  如果是做ASP.NET,数据库连接配置在Web.config 中ConnectionString配置节。
     ///   </summary>
     public   class  Manager
    {

         #region  配置数据库连接字符串
         ///   <summary>
         ///  配置数据库连接字符串
         ///   </summary>
         private   static   string  ConnectionString  =   " Data Source=localhost;Initial Catalog=DataBaseName;User Id=sa;Password=; " ;
         #endregion

         #region   执行SQL语句，返回Bool值
         ///   <summary>
         ///  执行SQL语句，返回Bool值
         ///   </summary>
         ///   <param name="sql"> 要执行的SQL语句 </param>
         ///   <returns> 返回BOOL值，True为执行成功 </returns>         
         public   static   bool  ExecuteSQL( string  sql)
        {
            SqlConnection con  =   new  SqlConnection(ConnectionString);
            SqlCommand cmd  =   new  SqlCommand(sql, con);
             try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
                 return   true ;
            }
             catch
            {
                 return   false ;
            }
             finally
            {
                 if  (con.State  !=  ConnectionState.Closed)
                    con.Close();
                con.Dispose();
                cmd.Dispose();
            }
        }
         #endregion


         #region   执行SQL语句，返回DataTable
         ///   <summary>
         ///  执行SQL语句，返回DataTable
         ///   </summary>
         ///   <param name="sql"> 要执行的SQL语句 </param>
         ///   <returns> 返回DataTable类型的执行结果 </returns>
         public   static  DataTable GetDataTable( string  sql)
        {
            SqlConnection con  =   new  SqlConnection(ConnectionString);
            con.Open();
            DataSet ds  =   new  DataSet();
            SqlDataAdapter da  =   new  SqlDataAdapter(sql, con);
             try
            {
                da.Fill(ds,  " tb " );
            }
             catch  (Exception ex)
            {
                 throw   new  Exception(ex.ToString());
            }
             finally
            {
                con.Close();
                con.Dispose();
                da.Dispose();
            }
            DataTable result  =  ds.Tables[ " tb " ];
             return  result;
        }
         #endregion

         #region   执行SQL语句，返回DataSet
         ///   <summary>
         ///  执行SQL语句，返回DataSet
         ///   </summary>
         ///   <param name="sql"> 要执行的SQL语句 </param>
         ///   <param name="tablename"> DataSet中要填充的表名 </param>
         ///   <returns> 返回dataSet类型的执行结果 </returns>
         public   static  DataSet GetDataSet( string  sql,  string  tablename)
        {
            SqlConnection con  =   new  SqlConnection(ConnectionString);
            con.Open();
            DataSet ds  =   new  DataSet();
            SqlDataAdapter da  =   new  SqlDataAdapter(sql, con);
             try
            {
                da.Fill(ds, tablename);
            }
             catch  (Exception ex)
            {
                 throw   new  Exception(ex.ToString());
            }
             finally
            {
                con.Close();
                con.Dispose();
                da.Dispose();
            }
             return  ds;
        }
         #endregion

         #region  执行SQL语句，返回SqlDataReader
         ///   <summary>
         ///  执行SQL语句，返回SqlDataReader
         ///   </summary>
         ///   <param name="sql"> 要执行的SQL语句 </param>
         ///   <returns> 返回SqlDataReader，需手工关闭连接 </returns>
         public   static  SqlDataReader GetReader( string  sql)
        {
            SqlConnection con  =   new  SqlConnection(ConnectionString);
            SqlCommand cmd  =   new  SqlCommand(sql, con);
            SqlDataReader dr  =   null ;
             try
            {
                con.Open();
                dr  =  cmd.ExecuteReader(CommandBehavior.CloseConnection);
                con.Close();
            }
             catch  (Exception ex)
            {
                dr.Close();
                con.Dispose();
                cmd.Dispose();
                 throw   new  Exception(ex.ToString());
            }
             return  dr;
        }
         #endregion        


         #region   执行SQL语句并返回受影响的行数

         ///   <summary>
         ///  执行SQL语句并返回受影响的行数
         ///   </summary>
         ///   <param name="sql"> 要执行的SQL语句 </param>
         ///   <returns> 返回Int类型的受影响的行数 </returns>
         public   static   int  GetCount( string  sql)
        {
            SqlConnection con  =   new  SqlConnection(ConnectionString);
            SqlCommand cmd  =   new  SqlCommand(sql, con);
             try
            {
                con.Open();
                 int  count  =  ( int )cmd.ExecuteScalar();
                 return  count;
            }
             catch
            {
                 return   0 ;
            }
             finally
            {
                con.Close();
                con.Dispose();
                cmd.Dispose();
            }
        }

         #endregion

        
        
    }
}