C# SQL數(shù)據(jù)庫操作類

1、using system;using system.configuration;using system.data;using system.data.sqlclient;using system.collections; namespace mycorporation.department.database/ / 通用數(shù)據(jù)庫類/ public class databaseprivate string connstr = null;public database() connstr = configurationsettings.appsettingsconnstr;public databa

2、se(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(); /

3、/ 運(yùn)行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; / / 運(yùn)行sql語句返回datareader/ / / sqldatareader對象.public sqldatar

4、eader 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對象/ / / /

5、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);

6、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;/ / 運(yùn)行sql語句,返回dataset對象/ / sql語句/ dataset對象public dataset runproc(string sql ,dataset ds)

7、 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; / / 運(yùn)行sql語句,返回dataset對象/ / sql語句/ dataset對象/ 表名public dataset runproc(strin

8、g 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;/ / 運(yùn)行sql語句,返回dataset對象/ / sql語句/ dataset對象/ 表名public dataset runproc(strin

9、g 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;/ / 檢驗(yàn)是否存在數(shù)據(jù)/ / pu

10、blic 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語句執(zhí)行結(jié)果的第一行第一列/ / 字符串public string returnvalue(string s

11、ql) 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;/ / 返回sq

12、l語句第一列,第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();

13、else result = ; dr.close(); dispose(conn); return result;/ / 生成一個(gè)存儲(chǔ)過程使用的sqlcommand./ / 存儲(chǔ)過程名./ 存儲(chǔ)過程入?yún)?shù)組./ sqlcommand對象.public sqlcommand createcmd(string procname, sqlparameter prams) sqlconnection conn; conn = new sqlconnection(connstr); conn.open(); sqlcommand cmd = new sqlcommand(procname, conn);

14、 cmd.commandtype = commandtype.storedprocedure; if (prams != null) foreach (sqlparameter parameter in prams) if(parameter != null) cmd.parameters.add(parameter); return cmd; / / 為存儲(chǔ)過程生成一個(gè)sqlcommand對象/ / 存儲(chǔ)過程名/ 存儲(chǔ)過程參數(shù)/ sqlcommand對象private sqlcommand createcmd(string procname, sqlparameter prams,sqlda

15、tareader 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 sqlpara

16、meter(returnvalue, sqldbtype.int, 4, parameterdirection.returnvalue, false, 0, 0, string.empty, datarowversion.default, null); return cmd;/ / 運(yùn)行存儲(chǔ)過程,返回./ / 存儲(chǔ)過程名/ 存儲(chǔ)過程參數(shù)/ sqldatareader對象public void runproc(string procname, sqlparameter prams, sqldatareader dr) sqlcommand cmd = createcmd(procname, pr

17、ams, dr); dr = cmd.executereader(system.data.commandbehavior.closeconnection); return;/ / 運(yùn)行存儲(chǔ)過程,返回./ / 存儲(chǔ)過程名/ 存儲(chǔ)過程參數(shù)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 ;