/// <summary>
/// 根據(jù)指定類型，從數(shù)據(jù)庫中獲取數(shù)據(jù)，存放到List<T>集合中
/// </summary>
/// <typeparam name="T">泛型對象</typeparam>
/// <param name="_classType">類類型，即數(shù)據(jù)庫中的表名</param>
/// <param name="_sql">查詢表的SQL</param>
/// <param name="_cmdParams">SQL參數(shù)</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("打包數(shù)據(jù)出錯-MakeTablePackage");
}
}
調(diào)用方代碼
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;
}
==============類型轉(zhuǎn)換通用類===============
主要是把
public int? IsMustExport
{
set{ _ismustexport=value;}
get{return _ismustexport;}
}
這樣的可空類型進行轉(zhuǎn)換，讓它在反射中可以通過屬性賦值
_property.SetValue(_item,
_value.ChangeType(_property.PropertyType)
public static class DBConvert
{
public static object ChangeType(this object value, Type conversionType) //第一個參數(shù)加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端插入數(shù)據(jù)庫的代碼============
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需要轉(zhuǎn)換為DBNull,否則不能成功插入數(shù)據(jù)庫
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();
}
|
