using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
namespace FerexTestSystem
{
public class DBHelper
{
//private static string server;
//private static string uid;
//private static string pwd;
//private static string database;
//private static string connStr;
//public DBHelper(string server1, string uid1, string pwd1, string database1)
//{
// server = server1;
// pwd = pwd1;
// database = database1;
// uid = uid1;
// connStr = @"server=" + server + ";uid=" + uid + ";pwd=" + pwd + ";database=" + database;
//}
//public static string connStr = @"server=PE01\PE01;uid=sa;pwd=adminsystem;database=QQDB";
///执行添加、删除、修改通用方法
///</summary>
///<param name="sql"></param>
///<param name="paras"></param>
///<returns></returns>
public static int ExecuteNonQuery(string connStr,string sql, params SqlParameter[] paras)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
//打开数据库连接
conn.Open();
//创建执行脚本的对象
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paras);
int result = command.ExecuteNonQuery();
return result;
}
}
///
/// 使用存储过程
///
public static Boolean Stored_Procedure(string connStr, string sql, params SqlParameter[] paras)
{
Boolean Flag = false;
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlCommand command = new SqlCommand(sql,conn);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter ps in paras)
command.Parameters.Add(ps);
command.Parameters.Add("@rs", SqlDbType.Int).Direction = ParameterDirection.Output;
command.ExecuteScalar();
if ((int)command.Parameters["@rs"].Value == 0)
return true;
}
return Flag;
}
///<summary>
///执行SQL并返回第一行第一列
///</summary>>
///<param name="sql""></param>
///<returns></returns>returns>
///
public static object ExecuteScalar(string connStr,String sql, params SqlParameter[] paras)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paras);
object obj = command.ExecuteScalar();
return obj;
}
}
///<summary>
///根据SQL和泛型方法返回泛型【集合】
///</summary>>
///<typeparam name="T"></typeparam>
///<param name="sql"></param>
///<param name="paras"></param>
///<returns></returns>
public static List<T> GetListByColumns<T>(string connStr, string sql, params SqlParameter[] paras)
{
List<T> list = new List<T>();
//获取select和form中间的字段
string columnsStr = sql.ToLower().Substring(sql.IndexOf("select ") + 7, sql.IndexOf("from") - 7)
.Replace(" ", "")
.Replace("\r\n", "")
.Replace("[", "")
.Replace("]", "");
//保存字段
ArrayList columns = new ArrayList(columnsStr.Split(','));
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paras);
using (SqlDataReader reader = command.ExecuteReader())
{
//typeof()检测类型
Type type = typeof(T);//类型的声明(可声明一个不确定的类型 )
if (columnsStr == "*")//查询所有(里面不用判断)
{
while (reader.Read())
{
T t = (T)Activator.CreateInstance(type);
//通过反射去遍历属性
foreach (PropertyInfo info in type.GetProperties())
{
info.SetValue(t, reader[info.Name] is DBNull ? null : reader[info.Name]);
}
list.Add(t);
}
}
else//根据查询的列遍历
{
while (reader.Read())
{
T t = (T)Activator.CreateInstance(type);
//通过反射去遍历属性
foreach (PropertyInfo info in type.GetProperties())
{
if (columns.Contains(info.Name.ToLower()))//判断是否存在
{
info.SetValue(t, reader[info.Name] is DBNull ? null : reader[info.Name]);
}
}
list.Add(t);
}
}
}
}
return list;//命令行为
}
public static List<T> GetList<T>(string connStr, String sql, params SqlParameter[] paras)
{
List<T> list = new List<T>();
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlCommand command = new SqlCommand(sql,conn);
command.Parameters.AddRange(paras);
using (SqlDataReader reader = command.ExecuteReader())
{
//typeof()检测类型
Type type = typeof(T);//类型的声明(可声明一个不确定的类型)
while (reader.Read())
{
T t = (T)Activator.CreateInstance(type);
//通过反射去遍历属性
foreach (PropertyInfo info in type.GetProperties())
{
info.SetValue(t, reader[info.Name] is DBNull ? null : reader[info.Name]);
}
list.Add(t);
}
}
}
return list;//命令行为
}
///<summary>
///根据SQL和泛型方法返回泛型[对象]
///</summary>>
///<typeparam name="T"></typeparam>
///<param name="sql"></param>
///<param name="="paras"></param>
///<returns></returns>
public static T GetModel<T>(string connStr, String sql, params SqlParameter[] paras)
{
Type type = typeof(T);//赋默认值null,可能是值类型
T t = default(T);//赋默认值null,可能是值类型
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paras);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
t = (T)Activator.CreateInstance(type);
//通过反射去遍历属性
foreach (PropertyInfo info in type.GetProperties())
{
info.SetValue(t, reader[info.Name] is DBNull ?
null : reader[info.Name]);
}
}
}
}
return t;//命令行为
}
/// <summary>
/// 查询返回临时表
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static DataTable GetDataTable(string connStr, string sql, params SqlParameter[] paras)
{
DataTable dt = null;
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paras);
SqlDataAdapter adapter = new SqlDataAdapter(command);
dt = new DataTable();
adapter.Fill(dt);
}
return dt;
}
/// <summary>
/// 执行SQL返回SqlDataReader对象(游标)
/// </summary>>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string connStr, string sql, params SqlParameter[] paras)
{
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
SqlCommand command = new SqlCommand(sql,conn);
command.Parameters.AddRange(paras);
return command.ExecuteReader(CommandBehavior.CloseConnection);//命令行为
}
///<summary>
///根据SQL执行返回数据集(多临时表)
///<param name="sql"></param>
///<param name="paras"></param>
///<returns></returns>
public static DataSet GetDataSet(string connStr, string sql, params SqlParameter[] paras)
{
DataSet ds = null;
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paras);
SqlDataAdapter adapter = new SqlDataAdapter(command);
ds = new DataSet();
adapter.Fill(ds);
}
return ds;
}
///执行事务的通用方法
public static bool ExecutTransaction(string connStr, string[] sql, params SqlParameter[] paras)
{
bool result = false;
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlCommand command = new SqlCommand();
command.Parameters.AddRange(paras);
command.Connection = conn;//关联联接对象
command.Transaction = conn.BeginTransaction();//开始事务
try
{
for (int i = 0; i < sql.Length; i++)
{
command.CommandText = sql[1];
command.ExecuteNonQuery();//执行
}
command.Transaction.Commit();//提交
result = true;
}
catch (Exception ex)
{
command.Transaction.Rollback();//回滚
result = false;
}
}
return result;
}
//事务批量添加
public static bool ExecuteTransaction(string connStr, string[] sql, List<SqlParameter[]> list)
{
bool result = false;
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlCommand command = new SqlCommand();
foreach (SqlParameter[] item in list)
{
command.Parameters.AddRange(item);
}
command.Connection = conn;//关联联接对象
command.Transaction = conn.BeginTransaction();//开始事务
try
{
for (int i = 0; i < sql.Length; i++)
{
command.CommandText = sql[i];
command.ExecuteNonQuery();//执行
}
command.Transaction.Commit();//提交
result= true;
}
catch (Exception ex)
{
command.Transaction.Rollback();//回滚
result = false;
}
}
return result;
}
}
}