using system;using system.collections.generic;using system.linq;using system.text;using system.data;using system.data.sqlclient;namespace dbcomm{ static class dbcommand { public class dbparameters { private sqlcommand m_owner = null; public dbparameters(sqlcommand owner) { m_owner = owner; } public sqlparametercollection p() { return m_owner.parameters; } }; public static bool bulktodb(string tabname, datatable dt, params string[] destcolumnnames) { bool bret = false; do { if (dt == null) break; if (dt.rows.count == 0) break; using (sqlconnection conn = dbconn.getconn()) { if (conn == null) break; sqlbulkcopy bulkcopy = new sqlbulkcopy(conn); if (bulkcopy == null) break; bulkcopy.destinationtablename = tabname; bulkcopy.bulkcopytimeout = 30; if (destcolumnnames.length == 0) { foreach (datacolumn col in dt.columns) bulkcopy.columnmappings.add(col.columnname, col.columnname); } else { if (destcolumnnames.length == dt.columns.count) { for (int i = 0; i < destcolumnnames.length; ++i) { bulkcopy.columnmappings.add(dt.columns[i].columnname, destcolumnnames[i]); } } } bulkcopy.batchsize = dt.rows.count; try { bulkcopy.writetoserver(dt); } catch (system.exception e) { string err = e.message; break; } finally { bulkcopy.close(); } } bret = true; } while (false); return bret; } public static dbparameters execprocnonquery(string proc_name, object[] paravalues) { using (sqlconnection conn = dbconn.getconn()) { sqlcommand cmd = new sqlcommand(); cmd.connection = conn; cmd.commandtype = commandtype.storedprocedure; cmd.commandtext = proc_name; addinparavalues(cmd, paravalues); cmd.executenonquery(); return new dbparameters(cmd); } } public delegate t[] fillvalues(sqldatareader reader); public static t[] querysomes(string sql, fillvalues fill) { using (sqlconnection conn = dbconn.getconn()) { t[] result = null; sqlcommand cmd = new sqlcommand(); cmd.connection = conn; cmd.commandtext = sql; sqldatareader reader = null; lock (reader = cmd.executereader()) { try { result = fill(reader); } catch (exception e) { throw new exception(e.stacktrace); } finally { reader.close(); } } return result; } } public delegate object fillvalue(sqldatareader reader); public static object querysome(string sql, fillvalue fill) { using (sqlconnection conn = dbconn.getconn()) { object result = null; sqlcommand cmd = new sqlcommand(); cmd.connection = conn; cmd.commandtext = sql; sqldatareader reader = null; lock (reader = cmd.executereader()) { try { result = fill(reader); } catch (exception e) { throw new exception(e.stacktrace); } finally { reader.close(); } } return result; } } public static object fillresultvalue(sqldatareader reader) { object o = null; if (reader.read()) { o = reader.getvalue(0); } return o; } public static bool queryboolean(string sql) { return convert.toboolean(querysome(sql, new fillvalue(fillresultvalue))); } public static byte[] querybytes(string sql) { return (byte[])(querysome(sql, new fillvalue(fillresultvalue))); } public static int queryinteger(string sql) { return convert.toint32(querysome(sql, new fillvalue(fillresultvalue))); } public static string querystr(string sql) { return querysome(sql, new fillvalue(fillresultvalue)) as string; } private static string[] fillstrsvalue(sqldatareader reader) { list lststr = new list(); while (reader.read()) { lststr.add(reader.getstring(0)); } return lststr.toarray(); } public static string[] querystrs(string sql) { return querysomes(sql, new fillvalues(fillstrsvalue)); } private static bool[] fillbooleansvalue(sqldatareader reader) { list lstbool = new list(); while (reader.read()) { lstbool.add(reader.getboolean(0)); } return lstbool.toarray(); } public static bool[] querybooleans(string sql) { return querysomes(sql, new fillvalues(fillbooleansvalue)); } public static void execcmd(string sql) { using (sqlconnection conn = dbconn.getconn()) { sqlcommand cmd = new sqlcommand(); cmd.connection = conn; cmd.commandtext = sql; cmd.executenonquery(); } } /// /// 获取存储过程的参数列表 /// /// 存储过程名称 /// datatable private static datatable getparameters(sqlconnection conn, string proc_name) { sqlcommand comm = new sqlcommand(dbo.sp_sproc_columns, conn); comm.commandtype = commandtype.storedprocedure; comm.parameters.addwithvalue(@procedure_name, (object)proc_name); sqldataadapter sda = new sqldataadapter(comm); datatable dt = new datatable(); sda.fill(dt); return dt; } /// /// 为 sqlcommand 添加参数及赋值 /// /// sqlcommand /// 参数数组(必须遵循存储过程参数列表的顺序) private static void addinparavalues(sqlcommand comm, params object[] paravalues) { using (sqlconnection conn = dbconn.getconn()) { comm.parameters.add(new sqlparameter(@return_value, sqldbtype.int)); comm.parameters[@return_value].direction = parameterdirection.returnvalue; if (paravalues != null) { datatable dt = getparameters(conn, comm.commandtext); int i = 0; foreach (datarow row in dt.rows) { string key = row[3].tostring(); if (key != @return_value) { int value = int.parse(row[4].tostring()); if (value == 1) { comm.parameters.addwithvalue(key, paravalues[i]); } else if (value == 2)//value为2则是输出参数 { comm.parameters.addwithvalue(key, paravalues[i]).direction = parameterdirection.output; //comm.parameters[key].direction = parameterdirection.output; } comm.parameters[key].size = convert.toint32(row[7].tostring()); i++; } } } } } }}
using system;using system.collections.generic;using system.linq;using system.text;using system.data.sqlclient;namespace dbcomm{ class dbconn { private static string m_connstr; public static string connstring { get { return m_connstr; } private set { m_connstr = value; } } static dbconn() { sqlconnectionstringbuilder connstr = new sqlconnectionstringbuilder(); connstr.datasource = .; connstr.initialcatalog = test; connstr.integratedsecurity = true; connstr.pooling = true; //开启连接池 connstr.minpoolsize = 0; //设置最小连接数为0 connstr.maxpoolsize = 100; //设置最大连接数为100 connstr.connecttimeout = 10; //设置超时时间为10秒 connstring = connstr.connectionstring; //connectdb(connstring); } public static sqlconnection getconn() { sqlconnection conn = new sqlconnection(connstring); conn.open(); return conn; } }}
using system;using system.collections.generic;using system.linq;using system.text;using system.data.sqlclient;using system.data;namespace dbcomm{ static class dbtablesource { public static datatable getsource(sqlconnection conn, string strsql) { datatable dt = null; sqlcommand cmd = null; sqldataadapter ad = null; try { lock (dt = new datatable()) { if (conn is sqlconnection) { cmd = new sqlcommand(strsql, conn); ad = new sqldataadapter((sqlcommand)cmd); } dt.clear(); ad.fill(dt); } } catch (exception e) { throw e; } return dt; } public static datatable source(string strsql) { using (sqlconnection conn = dbconn.getconn()) { return getsource(conn, strsql); } } }}