asp.net 读取文本文件并插入数据库的实现代码

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();
}
}
}