本人在操作数据库的时候,采用将对象的形式操作传入数据库,经过大量百度和朋友帮助,总结出如下两个接口。用于插入数据,提取数据。
要这样操作的前提条件,就是类名和表名一致,类字段和表字段一致就可以了
/// <summary>
/// 插入一个数据
/// </summary>
/// <param name="pole">需要插入的数据</param>
/// <returns></returns>
public int Insert<T>(T pole)
{
string strconn = DBHelp.GetDBConnection();
string strSQL = "INSERT INTO " + typeof(T).Name + " VALUES (";
FieldInfo[] entityFilds = typeof(T).GetFields();
for (int i = 0; i < entityFilds.Length; i++)
{
FieldInfo field = entityFilds[i];
if (field.FieldType.Name.ToLower() == "string")
{
strSQL += "'" + field.GetValue(pole) + "',";
}
else
{
strSQL += field.GetValue(pole) + ",";
}
}
strSQL = strSQL.TrimEnd(',') + ")";
int intRtn = DBHelp.ExeSqlCommand(strSQL, strconn);
return intRtn;
}
/// <summary>
/// 获取当前数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public IList<T> getPoleLst<T>()
{
IList<T> _lst = new List<T>();
string strconn = DBHelp.GetDBConnection();
string strSQL = "select * from " + typeof(T).Name;
DataSet ds = DBHelp.GetDataSet(strSQL, strconn);
if (ds.Tables != null && ds.Tables.Count > 0)
{
DataTable dt = ds.Tables[0];
FieldInfo[] entityFilds = typeof(T).GetFields();
T t = Activator.CreateInstance<T>();//创建一个T对象
foreach (DataRow dr in dt.Rows)
{
//通过属性集合迭代出每一个属性对象
foreach (FieldInfo item in entityFilds)
{
//循环迭代出DataTable的每一列
for (int j = 0; j < dt.Columns.Count; j++)
{
//判断当前迭代出的属性名称是否和迭代出的DataTable的列名称一致
if (item.Name.ToLower().Equals(dt.Columns[j].ColumnName.ToLower()))
{
//判断当前DataTable的单列值是否为null
if (dr[j] != DBNull.Value)
{
//根据实际对象的字段类型转换
string itemTypeName = item.FieldType.Name.ToLower();
object value = new object();
switch (itemTypeName)
{
case "uint16": value = Convert.ToUInt16(dr[j]); break; case "uint32": value = Convert.ToUInt32(dr[j]); break; case "uint64": value = Convert.ToUInt64(dr[j]); break; default: value = dr[j]; break;
}
item.SetValue(t, value);//将当前DataTable的单列值赋予相匹配的属性,否则赋予一个null值.
}
else
item.SetValue(t, null);
break;
}
}
}
_lst.Add(t);
}
}
ds.Dispose();
return _lst;
}