通过泛型数据,操作SQL数据库

时间:2023-03-09 00:36:53
通过泛型数据,操作SQL数据库

本人在操作数据库的时候,采用将对象的形式操作传入数据库,经过大量百度和朋友帮助,总结出如下两个接口。用于插入数据,提取数据。

要这样操作的前提条件,就是类名和表名一致,类字段和表字段一致就可以了

/// <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;

}