1,上传txt文件至公司系统 2,读取需要的内容 3,将内容插入到数据库中(需要判断重复) 4,与现有订单数据进行对比 本程序只研究读取需要的内容和插入数据
using System; using System.Data; using System.Collections.Generic; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.IO; using System.Text; using System.Text.RegularExpressions; using service.DAL; using System.Reflection; namespace service.fin { [Serializable] public class SettlementCmbModel { public SettlementCmbModel() { } #region Model private int _id; private string _terminalno; private DateTime ? _tradetime; private string _batchno; private string _channel; private string _empowerno; private string _orderno; private string _cardno; private string _cardmark; private string _tradetype; private string _businesstype; private string _goodsno; private string _stages; private decimal ? _tradeamount; private decimal ? _commissionamount; private decimal ? _returnfee; private decimal ? _memberspoint1; private decimal ? _memberspoint2; private decimal ? _mpcmbpercent; private decimal ? _mpvalue; private decimal ? _realamount; /// <summary> /// /// </summary> public int Id { set { _id = value; } get { return _id; } } /// <summary> /// /// </summary> public string TerminalNo { set { _terminalno = value; } get { return _terminalno; } } /// <summary> /// /// </summary> public DateTime ? TradeTime { set { _tradetime = value; } get { return _tradetime; } } /// <summary> /// /// </summary> public string BatchNo { set { _batchno = value; } get { return _batchno; } } /// <summary> /// /// </summary> public string Channel { set { _channel = value; } get { return _channel; } } /// <summary> /// /// </summary> public string EmpowerNo { set { _empowerno = value; } get { return _empowerno; } } /// <summary> /// /// </summary> public string OrderNo { set { _orderno = value; } get { return _orderno; } } /// <summary> /// /// </summary> public string CardNo { set { _cardno = value; } get { return _cardno; } } /// <summary> /// /// </summary> public string CardMark { set { _cardmark = value; } get { return _cardmark; } } /// <summary> /// /// </summary> public string TradeType { set { _tradetype = value; } get { return _tradetype; } } /// <summary> /// /// </summary> public string BusinessType { set { _businesstype = value; } get { return _businesstype; } } /// <summary> /// /// </summary> public string GoodsNo { set { _goodsno = value; } get { return _goodsno; } } /// <summary> /// /// </summary> public string Stages { set { _stages = value; } get { return _stages; } } /// <summary> /// /// </summary> public decimal ? TradeAmount { set { _tradeamount = value; } get { return _tradeamount; } } /// <summary> /// /// </summary> public decimal ? CommissionAmount { set { _commissionamount = value; } get { return _commissionamount; } } /// <summary> /// /// </summary> public decimal ? ReturnFee { set { _returnfee = value; } get { return _returnfee; } } /// <summary> /// /// </summary> public decimal ? MembersPoint1 { set { _memberspoint1 = value; } get { return _memberspoint1; } } /// <summary> /// /// </summary> public decimal ? MembersPoint2 { set { _memberspoint2 = value; } get { return _memberspoint2; } } /// <summary> /// /// </summary> public decimal ? MPCmbPercent { set { _mpcmbpercent = value; } get { return _mpcmbpercent; } } /// <summary> /// /// </summary> public decimal ? MPValue { set { _mpvalue = value; } get { return _mpvalue; } } /// <summary> /// /// </summary> public decimal ? RealAmount { set { _realamount = value; } get { return _realamount; } } #endregion Model } public class SettlementCmbDal { public SettlementCmbDal() { } #region 增加数据 public static int Add(SettlementCmbModel model) { StringBuilder strSql = new StringBuilder(); StringBuilder strSql1 = new StringBuilder(); StringBuilder strSql2 = new StringBuilder(); if (model.TerminalNo != null ) { strSql1.Append( " TerminalNo, " ); strSql2.Append( " ' " + model.TerminalNo + " ', " ); } if (model.TradeTime != null ) { strSql1.Append( " TradeTime, " ); strSql2.Append( " ' " + model.TradeTime + " ', " ); } if (model.BatchNo != null ) { strSql1.Append( " BatchNo, " ); strSql2.Append( " ' " + model.BatchNo + " ', " ); } if (model.Channel != null ) { strSql1.Append( " Channel, " ); strSql2.Append( " ' " + model.Channel + " ', " ); } if (model.EmpowerNo != null ) { strSql1.Append( " EmpowerNo, " ); strSql2.Append( " ' " + model.EmpowerNo + " ', " ); } if (model.OrderNo != null ) { strSql1.Append( " OrderNo, " ); strSql2.Append( " ' " + model.OrderNo + " ', " ); } if (model.CardNo != null ) { strSql1.Append( " CardNo, " ); strSql2.Append( " ' " + model.CardNo + " ', " ); } if (model.CardMark != null ) { strSql1.Append( " CardMark, " ); strSql2.Append( " ' " + model.CardMark + " ', " ); } if (model.TradeType != null ) { strSql1.Append( " TradeType, " ); strSql2.Append( " ' " + model.TradeType + " ', " ); } if (model.BusinessType != null ) { strSql1.Append( " BusinessType, " ); strSql2.Append( " ' " + model.BusinessType + " ', " ); } if (model.GoodsNo != null ) { strSql1.Append( " GoodsNo, " ); strSql2.Append( " ' " + model.GoodsNo + " ', " ); } if (model.Stages != null ) { strSql1.Append( " Stages, " ); strSql2.Append( " ' " + model.Stages + " ', " ); } if (model.TradeAmount != null ) { strSql1.Append( " TradeAmount, " ); strSql2.Append( "" + model.TradeAmount + " , " ); } if (model.CommissionAmount != null ) { strSql1.Append( " CommissionAmount, " ); strSql2.Append( "" + model.CommissionAmount + " , " ); } if (model.ReturnFee != null ) { strSql1.Append( " ReturnFee, " ); strSql2.Append( "" + model.ReturnFee + " , " ); } if (model.MembersPoint1 != null ) { strSql1.Append( " MembersPoint1, " ); strSql2.Append( "" + model.MembersPoint1 + " , " ); } if (model.MembersPoint2 != null ) { strSql1.Append( " MembersPoint2, " ); strSql2.Append( "" + model.MembersPoint2 + " , " ); } if (model.MPCmbPercent != null ) { strSql1.Append( " MPCmbPercent, " ); strSql2.Append( "" + model.MPCmbPercent + " , " ); } if (model.MPValue != null ) { strSql1.Append( " MPValue, " ); strSql2.Append( "" + model.MPValue + " , " ); } if (model.RealAmount != null ) { strSql1.Append( " RealAmount, " ); strSql2.Append( "" + model.RealAmount + " , " ); } strSql.Append( " insert into CRM_Settlement_CMB( " ); strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1 )); strSql.Append( " ) " ); strSql.Append( " values ( " ); strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1 )); strSql.Append( " ) " ); strSql.Append( " ;select @@IDENTITY " ); return SqlHelper.ExecuteNonQuery(strSql.ToString()); } #endregion #region 获取model public static SettlementCmbModel GetModel(DataRow dr) { SettlementCmbModel model = new SettlementCmbModel(); model.TerminalNo = dr[ " TerminalNo " ].ToString(); if (dr[ " TradeTime " ].ToString() != "" ) { model.TradeTime = DateTime.Parse(dr[ " TradeTime " ].ToString()); } model.BatchNo = dr[ " BatchNo " ].ToString(); model.Channel = dr[ " Channel " ].ToString(); model.EmpowerNo = dr[ " EmpowerNo " ].ToString(); model.OrderNo = dr[ " OrderNo " ].ToString(); model.CardNo = dr[ " CardNo " ].ToString(); model.CardMark = dr[ " CardMark " ].ToString(); model.TradeType = dr[ " TradeType " ].ToString(); model.BusinessType = dr[ " BusinessType " ].ToString(); model.GoodsNo = dr[ " GoodsNo " ].ToString(); model.Stages = dr[ " Stages " ].ToString(); if (dr[ " TradeAmount " ].ToString() != "" ) { model.TradeAmount = decimal .Parse(dr[ " TradeAmount " ].ToString()); } if (dr[ " CommissionAmount " ].ToString() != "" ) { model.CommissionAmount = decimal .Parse(dr[ " CommissionAmount " ].ToString()); } if (dr[ " ReturnFee " ].ToString() != "" ) { model.ReturnFee = decimal .Parse(dr[ " ReturnFee " ].ToString()); } if (dr[ " MembersPoint1 " ].ToString() != "" ) { model.MembersPoint1 = decimal .Parse(dr[ " MembersPoint1 " ].ToString()); } if (dr[ " MembersPoint2 " ].ToString() != "" ) { model.MembersPoint2 = decimal .Parse(dr[ " MembersPoint2 " ].ToString()); } if (dr[ " MPCmbPercent " ].ToString() != "" ) { model.MPCmbPercent = decimal .Parse(dr[ " MPCmbPercent " ].ToString()); } if (dr[ " MPValue " ].ToString() != "" ) { model.MPValue = decimal .Parse(dr[ " MPValue " ].ToString()); } if (dr[ " RealAmount " ].ToString() != "" ) { model.RealAmount = decimal .Parse(dr[ " RealAmount " ].ToString()); } return model; } #endregion } public partial class ReadTxt : System.Web.UI.Page { protected void Page_Load( object sender, EventArgs e) { } protected void txtInput() { // 反射的方法获取字段 SettlementCmbModel model = new SettlementCmbModel(); PropertyInfo[] obj = model.GetType().GetProperties(); DataTable dt = new DataTable(); foreach (PropertyInfo p in obj) { dt.Columns.Add(p.Name); } // 构造DataRow StreamReader sr = new StreamReader(Server.MapPath( " text.txt " ), System.Text.Encoding.GetEncoding( " GB2312 " )); while ( ! sr.EndOfStream) { string s = sr.ReadLine(); if (s.StartsWith( " NET " )) { s = s.Trim(); s = s.Replace( " - " , " - " ); string [] ss = s.Split( ' ' ); object [] datarow = new object [dt.Columns.Count]; int i = 0 ; foreach ( string st in ss) { if (st != "" ) { datarow[i] = st; i ++ ; } } dt.Rows.Add(datarow); } } sr.Close(); // 执行插入数据库动作 int j = 0 ; foreach (DataRow dr in dt.Rows) { SettlementCmbModel _model = SettlementCmbDal.GetModel(dr); // 处理交易时间导入时出现在问题 string s = _model.TerminalNo; int year = int .Parse(s.Substring( 0 , 4 )); int month = int .Parse(s.Substring( 4 , 2 )); int day = int .Parse(s.Substring( 6 , 2 )); string st = _model.TradeTime.ToString(); string [] str = st.Split( ' ' ); string [] stri = str[ 1 ].Split( ' : ' ); int hour = int .Parse(stri[ 0 ]); int minute = int .Parse(stri[ 1 ]); int second = int .Parse(stri[ 2 ]); DateTime de = new DateTime(year, month, day, hour, minute, second); _model.TradeTime = de; _model.TerminalNo = " NET " ; // 订单号头加一个0 _model.OrderNo = " 0 " + _model.OrderNo; if (SqlHelper.GetRecordCount( " CRM_Settlement_CMB " , " OrderNo like ' " + _model.OrderNo + " ' " ) == 0 ) { j += SettlementCmbDal.Add(_model); } } this .Literal1.Text = " 成功更新 " + j + " 行 " ; // StringBuilder sb = new StringBuilder(); // sb.Append("<table>"); // foreach (DataRow dr in dt.Rows) // { // sb.Append("<tr>"); // foreach (DataColumn dc in dt.Columns) // { // sb.Append("<td>" + dr[dc.ColumnName].ToString() + "</td>"); // } // sb.Append("</tr>"); // } // sb.Append("</table>"); // this.Literal1.Text = sb.ToString(); } } } 详细出处参考:http: // www.jb51.net/article/23198.htm
思路是读取txt,然后构造DataTable,然后由反射方式取得model,再插入
转载于:https://www.cnblogs.com/spark_wu/archive/2010/05/10/1731548.html