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(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则是

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);
 }
 }
 }
}