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