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; publi
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<T>(SqlDataReader reader); public static T[] QuerySomes<T>(string sql, FillValues<T> 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<string> lststr = new List<string>(); while (reader.Read()) { lststr.Add(reader.GetString(0)); } return lststr.ToArray(); } public static string[] QueryStrs(string sql) { return QuerySomes(sql, new FillValues<string>(FillStrsValue)); } private static bool[] FillBooleansValue(SqlDataReader reader) { List<bool> lstbool = new List<bool>(); while (reader.Read()) { lstbool.Add(reader.GetBoolean(0)); } return lstbool.ToArray(); } public static bool[] QueryBooleans(string sql) { return QuerySomes(sql, new FillValues<bool>(FillBooleansValue)); } public static void ExecCmd(string sql) { using (SqlConnection conn = DBConn.GetConn()) { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; cmd.ExecuteNonQuery(); } } /// <summary> /// 获取存储过程的参数列表 /// </summary> /// <param name="proc_Name">存储过程名称</param> /// <returns>DataTable</returns> 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; } /// <summary> /// 为 SqlCommand 添加参数及赋值 /// </summary> /// <param name="comm">SqlCommand</param> /// <param name="paraValues">参数数组(必须遵循存储过程参数列表的顺序)</param> 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); } } } }