using System;
using System.Collections.Generic;
using System.Linq;
//using System.Web;
//using TUV.LIMS.DLL;
using Oracle.DataAccess.Client;
using System.Data;
using System.IO;
using System.Reflection;
using System.Configuration;
using TUV.LIMS.DLL;
namespace TUV.LIMS.EDIAPI.Models
{
public
class GetDataForEDI
{
//private readonly
string dir_raw = @"C:\Log\WS_ZLabel\{0}\{1}";
private readonly
string dir_raw = @"C:\Log\EDIAPI\{0}\{1}";
public static Oracle.DataAccess.Client.OracleConnection CreateODPInstance()
{
return new Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.Connection
Strings["LIMS_ODP"].Connection
String);
}
public List<OutputRecord> GetReportData(InputRecord record)
{
OracleConnection conn = ConnectionHelper.CreateODPInstance();
Oracle.DataAccess.Client.OracleCommand _cmd = new Oracle.DataAccess.Client.OracleCommand("PKG_TEST_API.GET_DATA_FOR_EDI_NEW", conn);
_cmd.CommandType = CommandType.StoredProcedure;
//_cmd.BindByName = true;
#region parameters
_cmd.Parameters.Add("V_TEST_END_DATE_FROM", OracleDbType.Date).Value = record.TEST_END_DATE_FROM;
_cmd.Parameters.Add("V_TEST_END_DATE_TO", OracleDbType.Date).Value = record.TEST_END_DATE_TO;
OracleParameter p1 = new OracleParameter();
p1.UdtTypeName = "NUMERICARRAY";
p1.OracleDbType = OracleDbType.Array;
p1.ParameterName = "V_CUSTOMER_NUMBER";
if (record.CUSTOMER_NUMBER.Count == 0)
p1.Value = DBNull.Value;
else
p1.Value = record.CUSTOMER_NUMBER.Select(x => Convert.ToDecimal(x)).ToArray();
_cmd.Parameters.Add(p1);
_cmd.Parameters.Add("V_KC_TYPE", OracleDbType.Varchar2).Value = record.KEY_CLIENT;
_cmd.Parameters.Add("ITEMS_CURSOR", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
#endregion
Oracle.DataAccess.Client.
OracleDataAdapter da = new Oracle.DataAccess.Client.
OracleDataAdapter(_cmd);
Data
Table dt = new Data
Table();
da.Fill(dt);
return Covert
DataSetToEntity<OutputRecord>(dt);
}
public void logOutputToFile(InputRecord inputRecord, Output output)
{
DateTime dt = DateTime.Now;
//var dir = @"C:\Log\"; // folder location
string fileName = dt.To
String("yyyyMMdd HHmmss") + ".txt",
year = dt.To
String("yyyy"),
month = dt.To
String("MM"),
day = dt.To
String("dd");
string path =
string.Format(dir_raw, year, month);
if (!Directory.Exists(path)) // if it doesn't exist, create
Directory.CreateDirectory(path);
string fileStr1 = Path.Combine(path, fileName);
List<
string> msgs = new List<
string>() { "Input:",
"TEST_END_DATE_FROM: " + inputRecord.TEST_END_DATE_FROM,
"TEST_END_DATE_TO: " + inputRecord.TEST_END_DATE_TO,
"CUSTOMER_NUMBER: " +
string.Join(",", inputRecord.CUSTOMER_NUMBER),
"CUSTOMER_NAME:" + inputRecord.KEY_CLIENT,
Environment.NewLine,
"Output:",
"Code: " + output.ERROR_CODE };
if (output.ERROR_CODE == "E")
msgs.Add("Error Message: " + output.ERROR_MESSAGE);
else
{
msgs.Add("IPMS_ORDER_NO: " + (output.OUTPUT_ARRAY.Count == 0 ? "None" :
string.Join(",", output.OUTPUT_ARRAY.Select(x => x.SAP_ORDER_NO).Distinct().OrderBy(x => x))));
}
File.WriteAllText(fileStr1,
string.Join(Environment.NewLine, msgs));
}
#region Methods to convert Data
Table to
Class
private static List<T> Covert
DataSetToEntity<T>(Data
Table dt) where T :
class, new()
{
List<PropertyInfo> propertyInfoList = GetModelPropertyList<T>();
List<T> entityList = new List<T>(dt.Rows.Count);
T entity;
foreach (DataRow dr in dt.Rows)
{
entity = new T();
FillFieldsValue<T>(dr, entity, propertyInfoList);
entityList.Add(entity);
}
return entityList;
}
private static List<PropertyInfo> GetModelPropertyList<T>()
{
Type entityType = typeof(T);
PropertyInfo[] props = entityType.GetProperties();
List<PropertyInfo> propertyInfoList = new List<PropertyInfo>(props);
return propertyInfoList;
}
private static void FillFieldsValue<T>(DataRow dr, T entity, List<PropertyInfo> fieldNameList)
{
HashSet<
string> columns = new HashSet<
string>(dr.
Table.Columns.Cast<DataColumn>().Select(x => x.ColumnName.ToUpper()).ToList<
string>());
foreach (PropertyInfo propertyInfo in fieldNameList)
{
String fieldName = propertyInfo.Name;
if (columns.Contains(fieldName.ToUpper()))
{
Object fieldValue = dr[fieldName];
ProcessFieldValueToEntity<T>(ref entity, fieldValue, propertyInfo);
}
}
}
private static void ProcessFieldValueToEntity<U>(ref U entity,
object fieldValue, PropertyInfo pInfo)
{
//Check the fieldValue whether is match the Property Type.
//if Property Type is Generic Type, (means Nullable<int>) then return value is int type.
Type pType = CheckFieldValueType(fieldValue, pInfo.PropertyType, pInfo.Name);
pInfo.SetValue(entity, GetFieldValue(fieldValue, pType), null);
}
private static Type CheckFieldValueType(
object fieldValue, Type pType,
string propName)
{
if (pType.IsGenericType)
{
pType = pType.GetGenericArguments()[0];
return CheckFieldValueType(fieldValue, pType, propName);
}
else
{
// if it is number type, return decimal
if (fieldValue.GetType() == typeof(double) || fieldValue.GetType() == typeof(decimal) || fieldValue.GetType() == typeof(Int16) || fieldValue.GetType() == typeof(Int32) || fieldValue.GetType() == typeof(Int64))
return pType;
else if (fieldValue.GetType() == pType)
return pType;
else if (Type.GetTypeCode(fieldValue.GetType()) == TypeCode.DBNull)
return typeof(DBNull);
else
throw new InvalidCastException(
string.Format("Can not convert filedValue type \"{0}\" to Property \"{1}\"'s Type \"{2}\".", fieldValue.GetType().Name, propName, pType.Name));
}
}
private static
object GetFieldValue(
object fieldValue, Type propType)
{
if (fieldValue == DBNull.Value || fieldValue.To
String().Length == 0)
return null;
else
return Convert.ChangeType(fieldValue, propType);
}
#endregion
}
}
此博客展示了一段C#代码，利用OracleDataAdapter更新数据集。代码中创建了适配器，获取连接，设置更新命令，填充数据集，获取数据表和行，更新数据行中的工资信息，最后使用适配器更新EMPINFO表。
扫一扫
5132
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
