c#中DBHelp封装方法
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
using System.Net;
using System.Net.Sockets;
namespace DAL
{
public class DBHelper
{
private SqlConnection conn = null;
/// <summary>
/// 构造函数
/// </summary>
public DBHelper()
{
if (conn == null)
{
conn = new SqlConnection("Data Source = ip,端口号; Database = 数据库名;"
+ "User Id = xx; Password = xxxxx");
}
}
/// <summary>
/// 返回DataTable查询结果
/// </summary>
/// <param name="sql"></param>
/// <param name="par"></param>
/// <returns></returns>
public DataTable GetTable(string sql, SqlParameter[] par = null)
{
try
{
SqlCommand com = new SqlCommand(sql, conn);
if (par != null)
{
com.Parameters.AddRange(par);
}
SqlDataAdapter ada = new SqlDataAdapter(com);
DataTable dt = new DataTable();
ada.Fill(dt);
ada.Dispose();
if (conn.State == ConnectionState.Open)
{
this.Close();
}
return dt;
}
catch (Exception ex)
{
if (conn.State == ConnectionState.Open)
{
this.Close();
}
throw;
}
}
/// <summary>
/// 返回List查询结果
/// </summary>
/// <param name="sql"></param>
/// <param name="par"></param>
/// <returns></returns>
public List<T> GetToList<T>(string sql, SqlParameter[] par = null)
{
List<T> li = DataTableToList<T>(GetTable(sql));
return li;
}
/// <summary>
/// 返回查询结果首行首列
/// </summary>
/// <param name="sql"></param>
/// <param name="par"></param>
/// <returns></returns>
public object ExecuteScalar(string sql, SqlParameter[] par = null)
{
try
{
this.OpenConnect();
SqlCommand com = new SqlCommand(sql, conn);
if (par != null)
{
com.Parameters.AddRange(par);
}
this.Close();
return com.ExecuteScalar();
}
catch (Exception ex)
{
this.Close();
throw;
}
}
/// <summary>
/// 返回执行结果受影响行数
/// </summary>
/// <param name="sql"></param>
/// <param name="par"></param>
/// <returns></returns>
public int ExecuteNon(string sql, SqlParameter[] par = null)
{
try
{
OpenConnect();
SqlCommand com = new SqlCommand(sql,conn);
com.CommandTimeout = 180;
if (par != null)
{
com.Parameters.AddRange(par);
}
int i= com.ExecuteNonQuery();
conn.Close();
return i;
}
catch (Exception ex)
{
Close();
throw new Exception(ex.Message);
}
}
/// <summary>
/// Table转list集合
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
private List<T> DataTableToList<T>(DataTable dt)
{
//初始化值
List<T> result = new List<T>();
for (int i = 0; i < dt.Rows.Count; i++)
{
T _t = (T)Activator.CreateInstance(typeof(T));
PropertyInfo[] propertys = _t.GetType().GetProperties();
foreach (PropertyInfo pro in propertys)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (pro.Name.Equals(dt.Columns[j].ColumnName))
{
if (dt.Rows[i][j] != DBNull.Value)
{
pro.SetValue(_t, dt.Rows[i][j], null);
}
else
{
pro.SetValue(_t, null, null);
}
break;
}
}
}
result.Add(_t);
}
return result;
}
/// <summary>
/// 打开数据库链接
/// </summary>
private void OpenConnect()
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
}
/// <summary>
/// 关闭数据库链接
/// </summary>
private void Close()
{
if (conn.State != ConnectionState.Closed)
{
conn.Dispose();
}
}
}
}
如有哪里不符合逻辑,还请各位大神指点一二