DataBase类-CSDN博客

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient
using System.Configuration;
using System.Collections;

namespace Utility
{
     // 数据库连接类
     public class DataBase
    {
         protected SqlConnection Connection;
         protected string ConnectionString;
         // 构造函数
         public DataBase()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings[ " ConnectionString " ].ConnectionString;
        }

         // 打开数据库连接
         protected void Open()
        {
             if (Connection == null )
            {
                Connection = new SqlConnection(ConnectionString);
                Connection.Open();
            }
             else
            {
                 if (Connection.State.Equals(ConnectionState.Closed))
                    Connection.Open();
            }
        }

         // 关闭数据库连接
         public void Close()
        {
             if (Connection.State.Equals(ConnectionState.Open))
                Connection.Close();
        }

         // 析构函数,释放非托管资源
         ~ DataBase()
        {
             try
            {
                 if (Connection != null )
                    Connection.Close();
            }
             catch {}
             try {
                Dispose();
            }
             catch {}
        }
         // 释放资源
         public void Dispose()
        {
             if (Connection != null )
            {
                Connection.Dispose();
                Connection = null ;
            }
        }

         // 是否查询到记录
         public bool GetRecord( string strSQL)
        {
            Open();
            SqlDataAdapter adapter = new SqlDataAdapter(strSQL,Connection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            Close();

             if (ds.Tables[ 0 ].Rows.Count > 0 )
                 return true ;
             else
                 return false ;
        }

         // 返回获得的数据值
         // strSQL格式: select count(*) from XXX where ...
         public int GetRecordCount( string strSQL)
        {
             string count;
            Open();
            SqlCommand cmd = new SqlCommand(strSQL,Connection);
            count = cmd.ExecuteScalar().ToString().Trim();
             if (count == "" )
                count = " 0 " ;
            Close();
             return Convert.ToInt32(count);
        }

         // 实现高级查询
         // tableName:表名,ht键:字段名,值:字段值
         public DataSet AdvancedSearch( string tableName, Hashtable ht)
        {
             int count = 0 ;
             string field = "" ;
             foreach (DictionaryEntry item in ht)
            {
                 if (count != 0 )
                {
                    field += " and " ;
                }
                field += item.Key.ToString();
                field += " like '% " ;
                field += item.Value.ToString();
                field += " %' " ;
                count ++ ;
            }
            field += " " ;
             string strSQL = " select * from " + tableName + " where " + field;
            Open();
            SqlDataAdapter adapter = new SqlDataAdapter(strSQL,Connection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            Close();
             return ds;
        }

         // 创建带有存储过程的SqlCommand
         private SqlCommand CreateCommand( string procName,SqlParameter[] Params)
        {
            Open();
            SqlCommand cmd = new SqlCommand(procName,Connection);
            cmd.CommandType = CommandType.StoredProcedure;

             if (Params != null )
            {
                 foreach (SqlParameter Parameter in Params)
                {
                     cmd.Parameters.Add(Parameter);
                }
            }
             return cmd;
        }

         // 创建存储过程参数
         // 参数名称,参数类型,参数大小,传递方向.
         public SqlParameter MakeParam( string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object value)
        {
            SqlParameter Param;
             if (Size > 0 )
                Param = new SqlParameter(ParamName, DbType, Size);
             else
                Param = new SqlParameter(ParamName,DbType);

            Param.Direction = Direction;

             if (value != null )
                Param.Value = value;

             return Param;
        }

         public SqlParameter MakeInParam( string ParamName, SqlParameter DbType, Int32 Size, object value)
        {
            MakeParam(ParamName,DbType,Size,ParameterDirection.Input,value);
        }

         // 调用存储过程返回影响行数(update insert delete)
         public int RunProc( string ProcName)
        {
             int count = - 1 ;
            SqlCommand cmd = CreateCommand(ProcName, null );
            count = cmd.ExecuteNonQuery().ToString().Trim();
            Close();
             return count;
        }
         // 以SqlDataReader返回
         public SqlDataReader RunProcGetReader( string ProcName)
        {
            SqlCommand cmd = CreateCommand(ProcName);
             return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

         public SqlDataReader RunProcGetReader( string ProcName, SqlParameter[] Params)
        {
            SqlCommand cmd = CreateCommand(ProcName,Params);
             return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

         public int RunProcGetCount( string ProcName, SqlParameter[] Params)
        {
            SqlCommand cmd = CreateCommand(ProcName,Params);
             string count = cmd.ExecuteScalar().ToString().Trim();
             if (count == "" )
                count = " 0 " ;
             return Convert.ToInt32(count);
        }

         // 执行存储过程
         public DataSet GetDataSet( string ProcName)
        {
            Open();
            SqlDataAdapter adapter = new SqlDataAdapter(ProcName,Connection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            Close();
             return ds;
        }
         // 执行带参数的存储过程
         public DataSet GetDataSet( string ProcName, SqlParameter[] Params)
        {
            Open();
            SqlCommand cmd = CreateCommand(ProcName,Params);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            Close();
             return ds;
        }

         // 执行SQL
         public DataSet GetDataSetSql( string strSQL)
        {
            Open();
            SqlDataAdapter adapter = new SqlDataAdapter(strSQL,Connection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            Close();
             return ds;
        }

         // 插入数据
         public int Insert( string strSQL)
        {
             int count = - 1 ;
            Open();
            SqlCommand cmd = new SqlCommand(strSQL,Connection);
            count = cmd.ExecuteNonQuery();
            Close();
             return count;
        }

         public int GetIDInsert( string strSQL)
        {
              int count = - 1 ;
             Open();
             SqlCommand cmd = new SqlCommand(strSQL,Connection);
             count = Convert.ToInt32(cmd.ExecuteScalar().ToString().Trim());
             Close();
              return count;
        }

         // 返回DataRow
         public DataRow GetDataRow( string strSQL)
        {
            DataSet ds = GetDataSet(strSQL);
            ds.CaseSensitive = false ;
             if (ds.Tables[ 0 ].Rows.Count > 0 )
            {
                 return ds.Tables[ 0 ].Rows[ 0 ];
            }
             else
            {
                 return null ;
            }
        }

         // 更新数据表
         public bool Update( string tableName, Hashtable ht, string where )
        {
             int count = 0 ;
             if (ht.Count <= 0 )
                 return true ;
             string field = "" ;
             foreach (DictionaryEntry item in ht)
            {
                 if (count != 0 )
                    field += " , " ;
                field += " [ " + item.Key.ToString() + " ] " ;
                field += " = " ;
                field += item.Value.ToString();
                count ++ ;
            }
            field += " " ;

             string strSQL = " Update " + tableName + " Set " + field + where ;
             string [] sqls = {strSQL};
             return ExecuteSQL(sqls);
        }

         public bool ExecuteSQL( string [] strSQL)
        {
             bool success = true ;
            Open();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction trans = Connection.BeginTransaction();
            cmd.Connection = Connection;
            cmd.Transaction = trans;
             int i = 0 ;
             try
            {
                 foreach ( string str in strSQL)
                {
                    cmd.CommandText = str;
                    cmd.ExecuteNonQuery();
                    i ++ ;
                }
                trans.Commit();
            }
             catch {
                success = false ;
                Close();
                trans.Rollback();
            }
             finally
            {
                Close();
            }
             return success;
        }

         public DataTable GetDataTable( string strSQL)
        {
            DataSet ds = GetDataSet(strSQL);
            ds.CaseSensitive = false ;
             return ds.Tables[ 0 ];
        }

    }
}

<?xml version="1.0" encoding="utf-8"?>

<configuration>

    <appSettings/>
<connectionStrings>
    <add name="ConnectionString" connectionString="Data Source=(local); Initial Catalog=数据库名; User ID=sa;Password=" providerName="System.Data.SqlClient"/>
</connectionStrings>

    <system.web>
        
        <compilation debug="true" />
        
        <authentication mode="Windows" />
        <!--
            如果在执行请求的过程中出现未处理的错误，
            则通过 <customErrors> 节可以配置相应的处理步骤。具体说来，
            开发人员通过该节可以配置
            要显示的 html 错误页
            以代替错误堆栈跟踪。