中國(guó)最大的web開發(fā)資源網(wǎng)站及技術(shù)社區(qū)，
using system;
using system.configuration;
using system.data;
using system.data.sqlclient;
using system.collections;
namespace mycorporation.department.database
{
/// <summary>
/// 通用數(shù)據(jù)庫類
/// </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對(duì)象
/// </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>
/// 運(yùn)行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>
/// 運(yùn)行sql語句返回datareader
/// </summary>
/// <param name="sql"></param>
/// <returns>sqldatareader對(duì)象.</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對(duì)象
/// </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對(duì)象
/// </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對(duì)象
/// </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>
/// 運(yùn)行sql語句,返回dataset對(duì)象
/// </summary>
/// <param name="procname">sql語句</param>
/// <param name="prams">dataset對(duì)象</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>
/// 運(yùn)行sql語句,返回dataset對(duì)象
/// </summary>
/// <param name="procname">sql語句</param>
/// <param name="prams">dataset對(duì)象</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>
/// 運(yùn)行sql語句,返回dataset對(duì)象
/// </summary>
/// <param name="procname">sql語句</param>
/// <param name="prams">dataset對(duì)象</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>
/// 檢驗(yàn)是否存在數(shù)據(jù)
/// </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語句執(zhí)行結(jié)果的第一行第一列
/// </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>
/// 生成一個(gè)存儲(chǔ)過程使用的sqlcommand.
/// </summary>
/// <param name="procname">存儲(chǔ)過程名.</param>
/// <param name="prams">存儲(chǔ)過程入?yún)?shù)組.</param>
/// <returns>sqlcommand對(duì)象.</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>
/// 為存儲(chǔ)過程生成一個(gè)sqlcommand對(duì)象
/// </summary>
/// <param name="procname">存儲(chǔ)過程名</param>
/// <param name="prams">存儲(chǔ)過程參數(shù)</param>
/// <returns>sqlcommand對(duì)象</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>
/// 運(yùn)行存儲(chǔ)過程,返回.
/// </summary>
/// <param name="procname">存儲(chǔ)過程名</param>
/// <param name="prams">存儲(chǔ)過程參數(shù)</param>
/// <param name="datareader">sqldatareader對(duì)象</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>
/// 運(yùn)行存儲(chǔ)過程,返回.
/// </summary>
/// <param name="procname">存儲(chǔ)過程名</param>
/// <param name="prams">存儲(chǔ)過程參數(shù)</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>
/// 運(yùn)行存儲(chǔ)過程,返回dataset.
/// </summary>
/// <param name="procname">存儲(chǔ)過程名.</param>
/// <param name="prams">存儲(chǔ)過程入?yún)?shù)組.</param>
/// <returns>dataset對(duì)象.</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;
}
}
}
