DataBase类
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>