/// 根据指定类型,从数据库中获取数据,存放到List<T>集合中
/// </summary>
/// <typeparam name="T">泛型对象</typeparam>
/// <param name="_classType">类类型,即数据库中的表名</param>
/// <param name="_sql">查询表的SQL</param>
/// <param name="_cmdParams">SQL参数</param>
/// <returns></returns>
public List<T> MakeTablePackage<T>(string _classType,string _sql, //where T:class,new() 表示类型T必须是类,并且可以实例化
params SqlParameter[] _cmdParams) where T:class,new()
{
try
{
List<T> _lstReturn = new List<T>();
DataTable _dtGet = SqlHelper.ExcueteDataTable(_sql, _cmdParams);
//获得属性集合
T _tmpObj = new T();
Type _type = _tmpObj.GetType();
PropertyInfo[] _properties = _type.GetProperties();
for (int i = 0; i < _dtGet.Rows.Count; i++)
{
T _item = new T();
foreach (PropertyInfo _property in _properties)
{
object _value = _dtGet.Rows[i][_property.Name].ToString();
_property.SetValue(_item,
_value. ChangeType(_property.PropertyType)
, null);
}
_lstReturn.Add(_item);
}
return _lstReturn;
}
catch(Exception ex)
{
throw new Exception("打包数据出错-MakeTablePackage");
}
}
调用方代码
public TransDictionary PrepareDataToWH(string _batchID)
{
string _sql = "Select * From M_Batch01 Where BatchID=@BatchID";
List<M_Batch01> _lstM_Batch01 = MakeTablePackage<M_Batch01>
("M_Batch01", _sql, new SqlParameter("@BatchID", _batchID));
_sql = "Select * From Index_Flag Where BatchID=@BatchID";
List<Index_Flag> _lstIndex_Flag = MakeTablePackage<Index_Flag>
("Index_Flag", _sql, new SqlParameter("@BatchID", _batchID));
TransDictionary _transDic = new TransDictionary();
_transDic.M_Batch01 = _lstM_Batch01;
_transDic.Index_Flag = _lstIndex_Flag;
return _transDic;
}
==============类型转换通用类===============
主要是把
public int? IsMustExport
{
set{ _ismustexport=value;}
get{return _ismustexport;}
}
这样的可空类型进行转换,让它在反射中可以通过属性赋值
_property.SetValue(_item,
_value.ChangeType(_property.PropertyType)
public static class DBConvert
{
public static object ChangeType(this object value, Type conversionType) //第一个参数加this,表示该ChangeType方法将为object的扩展方法
{
if (conversionType.IsGenericType &&
conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
{
if (value != null)
{
NullableConverter nullableConverter = new NullableConverter(conversionType);
conversionType = nullableConverter.UnderlyingType;
}
else
{
return null;
}
}
//return Convert.ChangeType(value, conversionType);
if (conversionType.Equals(typeof(System.DateTime)) && (value.Equals("")))
{
return null;
}
else
{
return Convert.ChangeType(value, conversionType);
}
}
}
===========webservice端插入数据库的代码============
public string InsertDataToDB(TransDictionary _transDic)
{
using (SqlConnection con = new SqlConnection(SqlHelper._conStr))
{
SqlTransaction _trans = null;
SqlParameter[] _sqlparams = null;
string _sql = "";
try
{
con.Open();
_trans = con.BeginTransaction();
List<Index_Flag> _lstIndex_Flag = _transDic.Index_Flag;
for (int i = 0; i < _lstIndex_Flag.Count; i++)
{
_sql = BuildSqlByObject<Index_Flag>(_lstIndex_Flag[i],
"Index_Flag", out _sqlparams);
SqlHelper.ExecuteNonQuery(_trans, System.Data.CommandType.Text,
_sql, _sqlparams);
}
List<M_Batch01> _lstM_Batch01 = _transDic.M_Batch01;
for (int i = 0; i < _lstM_Batch01.Count; i++)
{
_sql = BuildSqlByObject<M_Batch01>(_lstM_Batch01[i],
"M_Batch01", out _sqlparams);
SqlHelper.ExecuteNonQuery(_trans, System.Data.CommandType.Text,
_sql, _sqlparams);
}
_trans.Commit();
}
catch (Exception ex)
{
_trans.Rollback();
return "InsertDataToDB-" + ex.ToString();
}
finally
{
}
}
return "成功";
}
private string BuildSqlByObject<T>(T _object,string _tableName,
out SqlParameter[] _sqlparams) where T : class
{
StringBuilder _sbSQL = new StringBuilder();
string _tmp = "Insert Into " + _tableName + "(";
_sbSQL.Append(_tmp);
List<SqlParameter> _lstParams = new List<SqlParameter>();
StringBuilder _sbFields = new StringBuilder();
StringBuilder _sbParams = new StringBuilder();
Type _type = _object.GetType();
PropertyInfo[] _properties = _type.GetProperties();
foreach (PropertyInfo item in _properties)
{
if (_sbFields.Length == 0)
{
_sbFields.Append(item.Name);
}
else
{
_sbFields.Append("," + item.Name);
}
if (_sbParams.Length == 0)
{
_sbParams.Append("@" + item.Name);
}
else
{
_sbParams.Append(",@" + item.Name);
}
//说明null需要转换为DBNull,否则不能成功插入数据库
object _paramValue = item.GetValue(_object , null) == null ?
DBNull.Value : item.GetValue(_object , null);
SqlParameter _param = new SqlParameter("@" + item.Name,
_paramValue);
_lstParams.Add(_param);
}
_sbSQL.Append(_sbFields.ToString() + ") Values(" + _sbParams.ToString() + ")");
_sqlparams = _lstParams.ToArray();
return _sbSQL.ToString();
}