C# SQL数据库操作类.doc

using system;using system.configuration;using system.data;using system.data.sqlclient;using system.collections; namespace mycorporation.department.database/ / 通用数据库类/ public class databaseprivate string connstr = null;public database() connstr = configurationsettings.appsettingsconnstr;public database(string str) try this.connstr = str; catch(exception ex) throw ex; / / 返回connection对象/ / 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(); / / 运行sql语句/ / 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; / / 运行sql语句返回datareader/ / / sqldatareader对象.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;/ / 生成command对象/ / / / public sqlcommand createcmd(string sql, sqlconnection conn) sqlcommand cmd ; cmd = new sqlcommand(sql, conn); return cmd;/ / 生成command对象/ / / public sqlcommand createcmd(string sql) sqlconnection conn; conn = new sqlconnection(connstr); conn.open(); sqlcommand cmd ; cmd = new sqlcommand(sql, conn); return cmd; / / 返回adapter对象/ / / / public sqldataadapter createda(string sql) sqlconnection conn; conn = new sqlconnection(connstr); conn.open(); sqldataadapter da; da = new sqldataadapter(sql, conn); return da;/ / 运行sql语句,返回dataset对象/ / sql语句/ dataset对象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; / / 运行sql语句,返回dataset对象/ / sql语句/ dataset对象/ 表名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;/ / 运行sql语句,返回dataset对象/ / sql语句/ dataset对象/ 表名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;/ / 检验是否存在数据/ / 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; / / 返回sql语句执行结果的第一行第一列/ / 字符串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 = dr0.tostring(); dr.close(); else result = ; dr.close(); catch throw new exception(sql); dispose(conn); return result;/ / 返回sql语句第一列,第columni列,/ / 字符串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 = drcolumni.tostring(); else result = ; dr.close(); dispose(conn); return result;/ / 生成一个存储过程使用的sqlcommand./ / 存储过程名./ 存储过程入参数组./ sqlcommand对象.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; / / 为存储过程生成一个sqlcommand对象/ / 存储过程名/ 存储过程参数/ sqlcommand对象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;/ / 运行存储过程,返回./ / 存储过程名/ 存储过程参数/ sqldatareader对象public void runproc(string procname, sqlparameter prams, sqldatareader dr) sqlcommand cmd = createcmd(procname, prams, dr); dr = cmd.executereader(system.data.commandbehavior.closeconnection); return;/ / 运行存储过程,返回./ / 存储过程名/ 存储过程参数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 ;