using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Reflection;
using System.Collections;
namespace DataHelp
{
#region ADO.NET 访问数据库辅助类 +SqlHelp
//Author:兵兵 +SqlHelp
public class SqlHelp
{
/// <summary>
/// DB连接字符串
/// </summary>
public static readonly string DB= ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
#region ExecuteDataReader +ExecuteDataReader(string cmdText, List<SqlParameter> parameters,string connString)
/// <summary>
/// ExecuteDataReader(执行有参存储过程)
/// </summary>
/// <param name="cmdText">存储过程名称</param>
/// <param name="parameters">参数列表</param>
/// <param name="connString">连接字符串</param>
/// <returns>SqlDataReader对象</returns>
public static SqlDataReader ExecuteDataReader(string cmdText, List<SqlParameter> parameters, string connString)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand();
CommandBuilder(cmdText, cmd, conn, parameters);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return reader;
}
#endregion
#region ExecuteDataReader +ExecuteDataReader(string cmdText,string connString)
/// <summary>
/// ExecuteDataReader(执行无参存储过程)
/// </summary>
/// <param name="cmdText">存储过程</param>
/// <param name="connString">连接字符串</param>
/// <returns>SqlDataReader对象</returns>
public static SqlDataReader ExecuteDataReader(string cmdText, string connString)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand();
CommandBuilder(cmdText, cmd, conn);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return reader;
}
#endregion
#region ExecuteNonQuery +ExecuteNonQuery(string cmdText, List<SqlParameter> parameters, string connString)
/// <summary>
/// ExecuteNonQuery(执行有参存储过程)
/// </summary>
/// <param name="cmdText">存储过程名称</param>
/// <param name="parameters">参数列表</param>
/// <param name="connString">连接字符串</param>
/// <returns>数据库受影响的行数</returns>
public static int ExecuteNonQuery(string cmdText, List<SqlParameter> parameters, string connString)
{
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand();
CommandBuilder(cmdText, cmd, conn, parameters);
int result = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return result;
}
}
#endregion
#region ExecuteNonQuery +ExecuteNonQuery(string cmdText, string connString)
/// <summary>
/// ExecuteNonQuery(执行无参存储过程)
/// </summary>
/// <param name="cmdText">存储过程名称</param>
/// <param name="connString">连接字符串</param>
/// <returns>数据库受影响的行数</returns>
public static int ExecuteNonQuery(string cmdText, string connString)
{
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand();
CommandBuilder(cmdText, cmd, conn);
int result = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return result;
}
}
#endregion
#region ExecuteScalar +ExecuteScalar(string cmdText, List<SqlParameter> parameters, string connString)
/// <summary>
/// ExecuteScalar(执行有参存储过程)
/// </summary>
/// <param name="cmdText">存储过程名称</param>
/// <param name="parameters">参数列表</param>
/// <param name="connString">连接字符串</param>
/// <returns>object</returns>
public static object ExecuteScalar(string cmdText, List<SqlParameter> parameters, string connString)
{
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand();
CommandBuilder(cmdText, cmd, conn, parameters);
object o = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return o;
}
}
#endregion
#region ExecuteScalar +ExecuteScalar(string cmdText, string connString)
/// <summary>
/// ExecuteScalar(执行无参存储过程)
/// </summary>
/// <param name="cmdText">存储过程名称</param>
/// <param name="connString">连接字符串</param>
/// <returns>object</returns>
public static object ExecuteScalar(string cmdText, string connString)
{
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand();
CommandBuilder(cmdText, cmd, conn);
object o = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return o;
}
}
#endregion
#region ExecuteDataTable +ExecuteDataTable(string cmdText, List<SqlParameter> parameters, string connString)
/// <summary>
/// ExecuteDataTable(用适配器执行有参存储过程)
/// </summary>
/// <param name="cmdText">存储过程名称</param>
/// <param name="parameters">参数列表</param>
/// <param name="connString">连接字符串</param>
/// <returns>DataTable</returns>
public static DataTable ExecuteDataTable(string cmdText, List<SqlParameter> parameters, string connString)
{
using (SqlConnection conn = new SqlConnection(connString))
{
SqlDataAdapter da = new SqlDataAdapter(cmdText, conn);
//命令类型为存储过程
da.DeleteCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.AddRange(parameters.ToArray());
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
}
#endregion
#region ExecuteDataTable +ExecuteDataTable(string cmdText, string connString)
/// <summary>
/// ExecuteDataTable(用适配器执行无参存储过程)
/// </summary>
/// <param name="cmdText">存储过程名称</param>
/// <param name="connString">连接字符串</param>
/// <returns>DataTable</returns>
public static DataTable ExecuteDataTable(string cmdText, string connString)
{
using (SqlConnection conn = new SqlConnection(connString))
{
SqlDataAdapter da = new SqlDataAdapter(cmdText, conn);
//命令类型为存储过程
da.DeleteCommand.CommandType = CommandType.StoredProcedure;
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
}
#endregion
#region ExecuteDataTableProc(命令+适配器) +ExecuteDataTableProc(string cmdText, List<SqlParameter> parameters, string connString)
/// <summary>
/// ExecuteDataTableProc(执行有参存储过程)
/// </summary>
/// <param name="cmdText">存储过程名称</param>
/// <param name="parameters">参数列表</param>
/// <param name="connString">连接字符串</param>
/// <returns>DataTable</returns>
public static DataTable ExecuteDataTableProc(string cmdText, List<SqlParameter> parameters, string connString)
/// <summary>
{
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand();
CommandBuilder(cmdText, cmd, conn, parameters);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
cmd.Parameters.Clear();
return dt;
}
}
#endregion
#region ExecuteDataTableProc(命令+适配器) +ExecuteDataTableProc(string cmdText, string connString)
/// <summary>
/// ExecuteDataTableProc(执行无参存储过程)
/// </summary>
/// <param name="parameters">参数列表</param>
/// <param name="connString">连接字符串</param>
/// <returns>DataTable</returns>
public static DataTable ExecuteDataTableProc(string cmdText, string connString)
{
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand();
CommandBuilder(cmdText, cmd, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
cmd.Parameters.Clear();
return dt;
}
}
#endregion
#region 准备命令对象 -CommandBuilder(string cmdText, SqlCommand cmd, SqlConnection conn, List<SqlParameter> parameters)
/// <summary>
/// 准备命令对象(执行有参存储过程)
/// </summary>
/// <param name="cmdText">存储过程名称</param>
/// <param name="cmd">命令对象</param>
/// <param name="conn">连接对象</param>
/// <param name="parameters">参数列表</param>
private static void CommandBuilder(string cmdText, SqlCommand cmd, SqlConnection conn, List<SqlParameter> parameters)
{
if (conn.State == System.Data.ConnectionState.Closed)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if (parameters.Count > )
cmd.Parameters.AddRange(parameters.ToArray());
}
#endregion
#region 准备命令对象 -CommandBuilder(string cmdText, SqlCommand cmd, SqlConnection conn)
/// <summary>
/// 准备命令对象(执行无参存储过程)
/// </summary>
/// <param name="cmdText">存储过程名称</param>
/// <param name="cmd">命令对象</param>
/// <param name="conn">连接对象</param>
private static void CommandBuilder(string cmdText, SqlCommand cmd, SqlConnection conn)
{
if (conn.State == System.Data.ConnectionState.Closed)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
}
#endregion
#region 批插入 void BulkInsert( DataTable dt, string tableName, string connStr)
/// <summary>
/// 批插入 void BulkInsert( DataTable dt, string tableName, string connStr)
/// </summary>
/// <param name="dt">所有数据的表格</param>
/// <param name="tableName">表名</param>
public static int BulkInsert(DataTable dt, string tableName, string connStr)
{
int result = -;
if (string.IsNullOrEmpty(tableName))
throw new Exception("请指定你要插入的表名");
var count = dt.Rows.Count;
if (count == )
return result;
SqlTransaction sqlBulkTran = null;
try
{
using (SqlConnection conn = new SqlConnection(connStr))
{
if (conn.State == System.Data.ConnectionState.Closed)
conn.Open();
sqlBulkTran = conn.BeginTransaction();
using (SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlBulkTran))
{
copy.DestinationTableName = tableName;//指定目标表
copy.WriteToServer(dt);//将dt中的所有行复制到SqlBulkCopy对象的DestinationTableName指定的目标表中
if (sqlBulkTran != null)
{
sqlBulkTran.Commit();
}
result = ;
}
}
}
catch (Exception)
{
if (sqlBulkTran != null)
{
sqlBulkTran.Rollback();
}
}
finally
{
sqlBulkTran = null;
}
return result;
}
#endregion
}
#endregion
}
#region list 扩展方法 Author:高兵兵
public static class IListUtil
{
/// <summary>
/// 将集合类转换成DataTable
/// </summary>
/// <param name="list">集合</param>
/// <returns></returns>
public static DataTable AsDataTable<T>(this IList<T> list)
{
DataTable result = new DataTable();
if (list.Count > )
{
PropertyInfo[] propertys = typeof(T).GetProperties();
foreach (PropertyInfo pi in propertys)
{
result.Columns.Add(pi.Name, pi.PropertyType);
}
for (int i = ; i < list.Count; i++)
{
ArrayList tempList = new ArrayList();
foreach (var item in propertys)
{
object obj = item.GetValue(list[i], null);
tempList.Add(obj);
}
object[] array = tempList.ToArray();
result.LoadDataRow(array, true);
}
}
return result;
}
}
#endregion
