using System;
using System.Collections.Generic;
using System.Configuration;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
namespace website
{
public class db
{
public static string ConnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "App_Data\\BaseDB.mdb;";
// public static string ConnStr = db.Properties.Settings.Default.my_soft_oledbConn;
//打开数据库链接
public static OleDbConnection Open_Conn(string ConnStr)
{
OleDbConnection Conn = new OleDbConnection(ConnStr);
Conn.Open();
return Conn;
}
//关闭数据库链接
public static void Close_Conn(OleDbConnection Conn)
{
if (Conn != null)
{
Conn.Close();
Conn.Dispose();
}
GC.Collect();
}
//运行OleDb语句
public static int Run_SQL(string sql)
{
return Run_SQL(sql, ConnStr);
}
public static int Run_SQL(string SQL, string ConnStr)
{
OleDbConnection Conn = Open_Conn(ConnStr);
OleDbCommand Cmd = Create_Cmd(SQL, Conn);
try
{
int result_count = Cmd.ExecuteNonQuery();
Close_Conn(Conn);
return result_count;
}
catch
{
Close_Conn(Conn);
return 0;
}
}
// 生成Command对象
public static OleDbCommand Create_Cmd(string SQL, OleDbConnection Conn)
{
OleDbCommand Cmd = new OleDbCommand(SQL, Conn);
return Cmd;
}
// 运行OleDb语句返回 DataTable
public static DataTable Get_DataTable(string SQL, string ConnStr, string Table_name)
{
OleDbDataAdapter Da = Get_Adapter(SQL, ConnStr);
DataTable dt = new DataTable(Table_name);
Da.Fill(dt);
return dt;
}
// 运行OleDb语句返回 OleDbDataReader对象
public static OleDbDataReader Get_Reader(string SQL, string ConnStr)
{
OleDbConnection Conn = Open_Conn(ConnStr);
OleDbCommand Cmd = Create_Cmd(SQL, Conn);
OleDbDataReader Dr;
try
{
Dr = Cmd.ExecuteReader(CommandBehavior.Default);
}
catch
{
throw new Exception(SQL);
}
Close_Conn(Conn);
return Dr;
}
// 运行OleDb语句返回 OleDbDataAdapter对象
public static OleDbDataAdapter Get_Adapter(string SQL, string ConnStr)
{
OleDbConnection Conn = Open_Conn(ConnStr);
OleDbDataAdapter Da = new OleDbDataAdapter(SQL, Conn);
return Da;
}
// 运行OleDb语句,返回DataSet对象
public static DataSet getds(string sql)
{
DataSet ds = new DataSet();
Get_DataSet(sql, ConnStr, ds);
return ds;
}
public static DataSet Get_DataSet(string SQL, string ConnStr, DataSet Ds)
{
OleDbDataAdapter Da = Get_Adapter(SQL, ConnStr);
try
{
Da.Fill(Ds);
}
catch (Exception Err)
{
throw Err;
}
return Ds;
}
// 运行OleDb语句,返回DataSet对象
public static DataSet Get_DataSet(string SQL, string ConnStr, DataSet Ds, string tablename)
{
OleDbDataAdapter Da = Get_Adapter(SQL, ConnStr);
try
{
Da.Fill(Ds, tablename);
}
catch (Exception Ex)
{
throw Ex;
}
return Ds;
}
// 运行OleDb语句,返回DataSet对象，将数据进行了分页
public static DataSet Get_DataSet(string SQL, string ConnStr, DataSet Ds, int StartIndex, int PageSize, string tablename)
{
OleDbConnection Conn = Open_Conn(ConnStr);
OleDbDataAdapter Da = Get_Adapter(SQL, ConnStr);
try
{
Da.Fill(Ds, StartIndex, PageSize, tablename);
}
catch (Exception Ex)
{
throw Ex;
}
Close_Conn(Conn);
return Ds;
}
// 返回OleDb语句执行结果的第一行第一列
public static string Get_Row1_Col1_Value(string SQL, string ConnStr)
{
OleDbConnection Conn = Open_Conn(ConnStr);
string result;
OleDbDataReader Dr;
try
{
Dr = Create_Cmd(SQL, Conn).ExecuteReader();
if (Dr.Read())
{
result = Dr[0].ToString();
Dr.Close();
}
else
{
result = "";
Dr.Close();
}
}
catch
{
throw new Exception(SQL);
}
Close_Conn(Conn);
return result;
}
/// <summary>
/// List转换成DataSet
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="list">将要转换的List</param>
/// <returns></returns>
public DataSet ConvertToDataSet<T>(IList<T> list)
{
if (list == null || list.Count <= 0)
{
return null;
}
DataSet ds = new DataSet();
DataTable dt = new DataTable(typeof(T).Name);
DataColumn column;
DataRow row;
System.Reflection.PropertyInfo[] myPropertyInfo = typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
foreach (T t in list)
{
if (t == null)
{
continue;
}
row = dt.NewRow();
for (int i = 0, j = myPropertyInfo.Length; i < j; i++)
{
System.Reflection.PropertyInfo pi = myPropertyInfo[i];
string name = pi.Name;
if (dt.Columns[name] == null)
{
column = new DataColumn(name, pi.PropertyType);
dt.Columns.Add(column);
}
row[name] = pi.GetValue(t, null);
}
dt.Rows.Add(row);
}
ds.Tables.Add(dt);
return ds;
}
#region DataSetToObjList
/// <summary>
/// DataSet数据转成Entity
/// </summary>
/// <typeparam name="T">Entity</typeparam>
/// <param name="ds">数据集</param>
/// <param name="tbName">表名</param>
/// <returns></returns>
public static List<T> DataSetToObjList<T>(DataSet ds, String tbName)
where T : new()
{
List<T> list = new List<T>();
PropertyInfo[] propinfos = null;
foreach (DataTable dt in ds.Tables)
{
if (dt.TableName.Equals(tbName))
{
foreach (DataRow dr in dt.Rows)
{
T entity = new T();
//初始化propertyinfo
if (propinfos == null)
{
Type objtype = entity.GetType();
propinfos = objtype.GetProperties();
}
//填充entity类的属性
foreach (PropertyInfo propinfo in propinfos)
{
foreach (DataColumn dc in dt.Columns)
{
if (dc.ColumnName.Equals(propinfo.Name))
{
string v = null;
v = dr[dc.ColumnName].ToString();
if (v != null)
{
propinfo.SetValue(entity, Convert.ChangeType(v, propinfo.PropertyType), null);
break;
}
}
}
}
list.Add(entity);
}
}
}
return list;
}
#endregion
#region DataSetToObjList
/// <summary>
/// DataSet数据转成Entity
/// </summary>
/// <typeparam name="T">Entity</typeparam>
/// <param name="ds">数据集</param>
/// <param name="tbName">表名</param>
/// <returns></returns>
public static T DataSetToObj<T>(DataSet ds, String tbName)
where T : new()
{
PropertyInfo[] propinfos = null;
T entity = new T();
if (ds.Tables[0].Rows.Count > 0)
{
DataTable dt = ds.Tables[0];
//初始化propertyinfo
if (propinfos == null)
{
Type objtype = entity.GetType();
propinfos = objtype.GetProperties();
}
//填充entity类的属性
DataRow dr = dt.Rows[0];
foreach (PropertyInfo propinfo in propinfos)
{
foreach (DataColumn dc in dt.Columns)
{
if (dc.ColumnName.Equals(propinfo.Name))
{
string v = null;
v = dr[dc.ColumnName].ToString();
if (v != null)
{
propinfo.SetValue(entity, Convert.ChangeType(v, propinfo.PropertyType), null);
break;
}
}
}
}
}
return entity;
}
#endregion
}
}
浙公网安备 33010602011771号