ado.net mysql类_C#ADO.NET帮助类

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, FillValuesfill)

{

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)

{

Listlststr = 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)

{

Listlstbool = 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);

}

}

}

}

本文原创发布php中文网，转载请注明出处，感谢您的尊重！