这几天在学习反射的应用,就有想法在封装数据库类的时候用上,下面就是这几天封装的类
用的泛型封装的,可以查询不同的表,不用每个表写不同的一长串的Sql语句了.感觉省事多了,
只是在多条件查询时Where后面的要自己手动输入传进去就OK了.
#define OLEDB_
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Reflection;
namespace WindowsFormsApp_反射的应用
{
#if OLEDB_
using MyCommand = OleDbCommand;
using MyConnection = OleDbConnection;
using MyDataAdapter = OleDbDataAdapter;
using MyDataReader = OleDbDataReader;
using myParameter = OleDbParameter;
#elif SQL_
using MyCommand = SqlCommand;
using MyConnection =SqlConnection;
using MyDataAdapter =SqlDataAdapter;
using MyDataReader =SqlDataReader;
using myParameter = SqlParameter;
#endif
/// <summary>
/// 泛型数据库操作类
/// <para>泛型类要和操作的数据库中表的名称,字段名称都要一样</para>
///
/// 作者: greenleaf1976
/// 创建时间: 2017-07-17
/// </summary>
public class MYADO_T
{
private static readonly string myDataFileName = @"D:\MyAccessFile\myTonxilu.accdb";
private static readonly string CONN_STRING = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={myDataFileName};Persist Security Info=False";
public static MyConnection conn = null;
public MYADO_T() { }
~MYADO_T() { conn.Dispose(); }
/// <summary>
/// 初始化conn对象(保证打开连接状态)
/// </summary>
private static void InitConnection()
{
if (conn == null)
conn = new MyConnection(CONN_STRING);
if (conn.State == ConnectionState.Closed)
conn.Open();
if (conn.State == ConnectionState.Broken)
{
conn.Close();
conn.Open();
}
}
/// <summary>
/// 获得表中的数据
/// </summary>
/// <typeparam name="T">和表中相对应的类或结构体</typeparam>
/// <param name="conditionsSql">多种查询条件,格式(mName='张三')</param>
/// <returns>返回查询结果表</returns>
public static DataTable GetDataTableAll<T>(string conditionsSql = null)
{
DataTable dt = new DataTable();
MyCommand cmd = null;
MyDataAdapter da = null;
Type ty = typeof(T);//获得泛型类型
//拼接Sql字符串
string sql = $"SELECT * FROM [{ty.Name}]";
if (conditionsSql != null)
sql += $" WHERE {conditionsSql}";
InitConnection();
try
{
cmd = new MyCommand(sql, conn);
da = new MyDataAdapter(cmd);
da.Fill(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
da.Dispose();
cmd.Dispose();
conn.Close();
}
return dt;
}
/// <summary>
/// 更新指定条件的数据
/// </summary>
/// <typeparam name="T">和表中相对应的类或结构体</typeparam>
/// <param name="conditionsSql">多种查询条件,格式(mName='张三' and ...)</param>
/// <param name="t">用于更新数据库中的新数据</param>
/// <returns>返回受影响的行数</returns>
public static int UpdateDataTable<T>(string conditionsSql, T t)
{
int nRet = 0;
string intoSql = null;
string fileName = null;
MyCommand cmd = null;
List<myParameter> parList = new List<myParameter>();
Type ty = t.GetType(); //获得泛型的类型
//获得Sql查询语句和添加参数列表
foreach (FieldInfo v in ty.GetFields())
{
fileName += v.Name + $"=@{v.Name},"; //拼接字段名和值的查询字符串
myParameter par = new myParameter($"@{v.Name}", v.GetValue(t)); //添加参数列表到parList中
parList.Add(par);
}
fileName = fileName.Substring(0, fileName.Length - 1); //去掉最后一个,号
//拼接Sql查询语句字符串
intoSql = $"UPDATE [{ty.Name}] SET {fileName} WHERE {conditionsSql}";
InitConnection();
try
{
cmd = new MyCommand(intoSql, conn);
cmd.Parameters.Clear();
cmd.Parameters.AddRange(parList.ToArray());//添加参数列表
nRet = cmd.ExecuteNonQuery();
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
conn.Close();
}
return nRet;
}
/// <summary>
/// 删除指定条件的数据
/// </summary>
/// <typeparam name="T">和表中相对应的类或结构体</typeparam>
/// <param name="conditionsSql">多种查询条件,格式(mName='张三' and ...)</param>
/// <returns>返回受影响的行数</returns>
public static int DeleatDataTable<T>(string conditionsSql)
{
int nRet = 0;
MyCommand cmd = null;
Type typ = typeof(T);
string sql = $"DELETE FROM {typ.Name} WHERE {conditionsSql}";
try
{
InitConnection();
cmd = new MyCommand(sql, conn);
nRet = cmd.ExecuteNonQuery();
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
conn.Close();
}
return nRet;
}
/// <summary>
/// 插入新的数据
/// </summary>
/// <typeparam name="T">和表中相对应的类或结构体</typeparam>
/// <param name="t">要插入的新数据对象</param>
/// <returns>成功返回True,失败False</returns>
public static bool InsertDataTable<T>(T t)
{
int nRet = 0;
string intoSql = null;
string fileName = null;
string fileValue = null;
MyCommand cmd = null;
List<myParameter> parList = new List<myParameter>();
Type ty = t.GetType();
foreach (FieldInfo v in ty.GetFields())
{
fileName += v.Name + ","; //拼接列名字符串
fileValue += $"@{v.Name},"; //拼接参数化值的字符串
//创建新的参数化查询,并添加到列表中
myParameter par = new myParameter($"@{v.Name}", v.GetValue(t));
parList.Add(par);
}
fileName = fileName.Substring(0, fileName.Length - 1); //去除最后的,号
fileValue = fileValue.Substring(0, fileValue.Length - 1); //去除最后的,号
//拼接添加数据的Sql语句
intoSql = $"INSERT INTO [{ty.Name}] ({fileName}) VALUES ({fileValue})";
try
{
InitConnection();
cmd = new MyCommand(intoSql, conn);
cmd.Parameters.Clear();
cmd.Parameters.AddRange(parList.ToArray());//添加参数列表
nRet = cmd.ExecuteNonQuery();
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
conn.Close();
}
return nRet == 1;
}
/// <summary>
/// 不断开连接的读取数据,多用于递归查询的调用
/// </summary>
/// <typeparam name="T">和表中相对应的类或结构体</typeparam>
/// <param name="conditionsSql">多种查询条件,格式(mName='张三' and ...)</param>
/// <returns>返回DataReader对象</returns>
public static MyDataReader GetDataReaddr<T>(string conditionsSql)
{
MyDataReader drRead = null;
MyCommand cmd = null;
Type ty = typeof(T);
string sql = $"SELECT * FROM [{ty.Name}]";
if (conditionsSql != null)
sql += $" WHERE {conditionsSql}";
InitConnection();
try
{
cmd = new MyCommand(sql, conn);
drRead = cmd.ExecuteReader();
}
catch (Exception ex)
{
throw ex;
}
return drRead;
}
/// <summary>
/// 查找指定条件,并返回单个对象
/// </summary>
/// <typeparam name="T">和表中相对应的类或结构体</typeparam>
/// <param name="conditionsSql">多种查询条件,格式(mName='张三' and ...)</param>
/// <returns>返回单个对象的数据</returns>
public static T FindDataTable<T>(string conditionsSql)
{
MyDataReader drRead = null;
Type ty = typeof(T); //获得泛型的类型
object obj =Activator.CreateInstance(ty);//根据类型创建新的object实例对象(不要实例成具体的类型)
try
{
drRead = GetDataReaddr<T>(conditionsSql); //读取数据库中的数据
if(drRead.Read())
{
foreach (FieldInfo v in ty.GetFields())
{
//根据字段类型给字段赋值
switch(v.FieldType.FullName)
{
case "System.Int32"://int
v.SetValue(obj, int.Parse(drRead[v.Name].ToString()));
break;
case "System.Double"://double
v.SetValue(obj, double.Parse(drRead[v.Name].ToString()));
break;
case "System.String"://string
v.SetValue(obj, drRead[v.Name].ToString());
break;
case "System.Object"://object
v.SetValue(obj, drRead[v.Name]);
break;
case "System.Boolean"://bool
v.SetValue(obj, bool.Parse(drRead[v.Name].ToString()));
break;
case "System.Char"://char
v.SetValue(obj, char.Parse(drRead[v.Name].ToString()));
break;
default: //其他类型,以后用的时候再加上
throw new Exception("没有指定转换的类型!!!");
}
}
}
else
return default(T); //没有找到返回默认的泛型对象,不是null
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
drRead.Close();
conn.Close();
}
//返回时转换成对应的泛型类型
return (T)obj;
}
}
}
在程序中调用:
1.显示全部数据
DataTable dt = MYADO_T.GetDataTableAll<mTable>();
foreach (DataRow dr in dt.Rows)
{
foreach (object v in dr.ItemArray)
{Console.Write(v.ToString() + "\t");
}
Console.WriteLine();
}
2.查询指定条件
返回一个对象
Form2 frm = new Form2();
if(frm.ShowDialog()==DialogResult.OK)
{
mTable tl = MYADO_T.FindDataTable<mTable>("mName='张三2');
if (tl.mName == null)
MessageBox.Show("没有找到数据");
else
MessageBox.Show(tl.ToString());
}
返回多个对象就返回DataTable
DataTable dt = MYADO_T.GetDataTableAll<mTable>("mAge>30");
foreach (DataRow dr in dt.Rows)
{
foreach (object v in dr.ItemArray)
{Console.Write(v.ToString() + "\t");
}
Console.WriteLine();
}
3.删除数据,这可以删除多个数据,只要符合条件的都会删除
int nRet = MYADO_T.DeleatDataTable<mTable>("mName='张三'");
Console.WriteLine(nRet);
4.插入数据
mTable tl = new mTable()
{
mName = "李雪琴",
mSex = "女",
mAge = 20,
mFenlei = "明星",
mTel = "132009833"
};
//这名没有写泛型类型是因为编译器提示我简化了的.本来我是写了泛型类型的.
bool bol = MYADO_T.InsertDataTable(tl);
if (bol)
MessageBox.Show("添加数据成功!");