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" 将调试符号插入
已编译的页面中。但由于这会
影响性能,因此只在开发过程中将此值
设置为 true。
-->
<compilation debug="true" />
<!--
通过 <authentication> 节可以配置 ASP.NET 使用的
安全身份验证模式,
以标识传入的用户。
-->
<authentication mode="Windows" />
<!--
如果在执行请求的过程中出现未处理的错误,
则通过 <customErrors> 节可以配置相应的处理步骤。具体说来,
开发人员通过该节可以配置
要显示的 html 错误页
以代替错误堆栈跟踪。
<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
<error statusCode="403" redirect="NoAccess.htm" />
<error statusCode="404" redirect="FileNotFound.htm" />
</customErrors>
-->
</system.web>
</configuration>