ASP.NET封装的SQL数据库访问类

using system;
using system.configuration;
using system.data;
using system.data.sqlclient;
using system.collections;
namespace mycorporation.department.database
{
/// <summary>
/// 通用数据库类
/// </summary>
public class database
{
private string connstr = null;
public database()
{
connstr = configurationsettings.appsettings["connstr"];
}
public database(string str)
{
try
{
this.connstr = str;
}
catch (exception ex)
{
throw ex;
}
}
/// <summary>
/// 返回connection对象
/// </summary>
/// <returns></returns>
public sqlconnection returnconn()
{
sqlconnection conn = new sqlconnection(connstr);
conn.open();
return conn;
}
public void dispose(sqlconnection conn)
{
if (conn != null)
{
conn.close();
conn.dispose();
}
gc.collect();
}
/// <summary>
/// 运行sql语句
/// </summary>
/// <param name="sql"></param>
public void runproc(string sql)
{
sqlconnection conn;
conn = new sqlconnection(connstr);
conn.open();
sqlcommand cmd;
cmd = createcmd(sql, conn);
try
{
cmd.executenonquery();
}
catch
{
throw new exception(sql);
}
dispose(conn);
return;
}
/// <summary>
/// 运行sql语句返回datareader
/// </summary>
/// <param name="sql"></param>
/// <returns>sqldatareader对象.</returns>
public sqldatareader runprocgetreader(string sql)
{
sqlconnection conn;
conn = new sqlconnection(connstr);
conn.open();
sqlcommand cmd;
cmd = createcmd(sql, conn);
sqldatareader dr;
try
{
dr = cmd.executereader(commandbehavior.default);
}
catch
{
throw new exception(sql);
}
//dispose(conn);
return dr;
}
/// <summary>
/// 生成command对象
/// </summary>
/// <param name="sql"></param>
/// <param name="conn"></param>
/// <returns></returns>
public sqlcommand createcmd(string sql, sqlconnection conn)
{
sqlcommand cmd;
cmd = new sqlcommand(sql, conn);
return cmd;
}
/// <summary>
/// 生成command对象
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public sqlcommand createcmd(string sql)
{
sqlconnection conn;
conn = new sqlconnection(connstr);
conn.open();
sqlcommand cmd;
cmd = new sqlcommand(sql, conn);
return cmd;
}
/// <summary>
/// 返回adapter对象
/// </summary>
/// <param name="sql"></param>
/// <param name="conn"></param>
/// <returns></returns>
public sqldataadapter createda(string sql)
{
sqlconnection conn;
conn = new sqlconnection(connstr);
conn.open();
sqldataadapter da;
da = new sqldataadapter(sql, conn);
return da;
}
/// <summary>
/// 运行sql语句,返回dataset对象
/// </summary>
/// <param name="procname">sql语句</param>
/// <param name="prams">dataset对象</param>
public dataset runproc(string sql, dataset ds)
{
sqlconnection conn;
conn = new sqlconnection(connstr);
conn.open();
sqldataadapter da;
//da = createda(sql, conn);
da = new sqldataadapter(sql, conn);
try
{
da.fill(ds);
}
catch (exception err)
{
throw err;
}
dispose(conn);
return ds;
}
/// <summary>
/// 运行sql语句,返回dataset对象
/// </summary>
/// <param name="procname">sql语句</param>
/// <param name="prams">dataset对象</param>
/// <param name="datareader">表名</param>
public dataset runproc(string sql, dataset ds, string tablename)
{
sqlconnection conn;
conn = new sqlconnection(connstr);
conn.open();
sqldataadapter da;
da = createda(sql);
try
{
da.fill(ds, tablename);
}
catch (exception ex)
{
throw ex;
}
dispose(conn);
return ds;
}
/// <summary>
/// 运行sql语句,返回dataset对象
/// </summary>
/// <param name="procname">sql语句</param>
/// <param name="prams">dataset对象</param>
/// <param name="datareader">表名</param>
public dataset runproc(string sql, dataset ds, int startindex, int pagesize, string tablename)
{
sqlconnection conn;
conn = new sqlconnection(connstr);
conn.open();
sqldataadapter da;
da = createda(sql);
try
{
da.fill(ds, startindex, pagesize, tablename);
}
catch (exception ex)
{
throw ex;
}
dispose(conn);
return ds;
}
/// <summary>
/// 检验是否存在数据
/// </summary>
/// <returns></returns>
public bool existdate(string sql)
{
sqlconnection conn;
conn = new sqlconnection(connstr);
conn.open();
sqldatareader dr;
dr = createcmd(sql, conn).executereader();
if (dr.read())
{
dispose(conn);
return true;
}
else
{
dispose(conn);
return false;
}
}
/// <summary>
/// 返回sql语句执行结果的第一行第一列
/// </summary>
/// <returns>字符串</returns>
public string returnvalue(string sql)
{
sqlconnection conn;
conn = new sqlconnection(connstr);
conn.open();
string result;
sqldatareader dr;
try
{
dr = createcmd(sql, conn).executereader();
if (dr.read())
{
result = dr[0].tostring();
dr.close();
}
else
{
result = "";
dr.close();
}
}
catch
{
throw new exception(sql);
}
dispose(conn);
return result;
}
/// <summary>
/// 返回sql语句第一列,第columni列,
/// </summary>
/// <returns>字符串</returns>
public string returnvalue(string sql, int columni)
{
sqlconnection conn;
conn = new sqlconnection(connstr);
conn.open();
string result;
sqldatareader dr;
try
{
dr = createcmd(sql, conn).executereader();
}
catch
{
throw new exception(sql);
}
if (dr.read())
{
result = dr[columni].tostring();
}
else
{
result = "";
}
dr.close();
dispose(conn);
return result;
}
/// <summary>
/// 生成一个存储过程使用的sqlcommand.
/// </summary>
/// <param name="procname">存储过程名.</param>
/// <param name="prams">存储过程入参数组.</param>
/// <returns>sqlcommand对象.</returns>
public sqlcommand createcmd(string procname, sqlparameter[] prams)
{
sqlconnection conn;
conn = new sqlconnection(connstr);
conn.open();
sqlcommand cmd = new sqlcommand(procname, conn);
cmd.commandtype = commandtype.storedprocedure;
if (prams != null)
{
foreach (sqlparameter parameter in prams)
{
if (parameter != null)
{
cmd.parameters.add(parameter);
}
}
}
return cmd;
}
/// <summary>
/// 为存储过程生成一个sqlcommand对象
/// </summary>
/// <param name="procname">存储过程名</param>
/// <param name="prams">存储过程参数</param>
/// <returns>sqlcommand对象</returns>
private sqlcommand createcmd(string procname, sqlparameter[] prams, sqldatareader dr)
{
sqlconnection conn;
conn = new sqlconnection(connstr);
conn.open();
sqlcommand cmd = new sqlcommand(procname, conn);
cmd.commandtype = commandtype.storedprocedure;
if (prams != null)
{
foreach (sqlparameter parameter in prams)
cmd.parameters.add(parameter);
}
cmd.parameters.add(
new sqlparameter("returnvalue", sqldbtype.int, 4,
parameterdirection.returnvalue, false, 0, 0,
string.empty, datarowversion.default, null));
return cmd;
}
/// <summary>
/// 运行存储过程,返回.
/// </summary>
/// <param name="procname">存储过程名</param>
/// <param name="prams">存储过程参数</param>
/// <param name="datareader">sqldatareader对象</param>
public void runproc(string procname, sqlparameter[] prams, sqldatareader dr)
{
sqlcommand cmd = createcmd(procname, prams, dr);
dr = cmd.executereader(system.data.commandbehavior.closeconnection);
return;
}
/// <summary>
/// 运行存储过程,返回.
/// </summary>
/// <param name="procname">存储过程名</param>
/// <param name="prams">存储过程参数</param>
public string runproc(string procname, sqlparameter[] prams)
{
sqldatareader dr;
sqlcommand cmd = createcmd(procname, prams);
dr = cmd.executereader(system.data.commandbehavior.closeconnection);
if (dr.read())
{
return dr.getvalue(0).tostring();
}
else
{
return "";
}
}
/// <summary>
/// 运行存储过程,返回dataset.
/// </summary>
/// <param name="procname">存储过程名.</param>
/// <param name="prams">存储过程入参数组.</param>
/// <returns>dataset对象.</returns>
public dataset runproc(string procname, sqlparameter[] prams, dataset ds)
{
sqlcommand cmd = createcmd(procname, prams);
sqldataadapter da = new sqldataadapter(cmd);
try
{
da.fill(ds);
}
catch (exception ex)
{
throw ex;
}
return ds;
}
}
}