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(); 
} 
} 
} 
 
思路是讀取txt，然后構造DataTable，然后由反射方式取得model，再插入數據庫，本例為方便說明，將幾個類放在一個頁中，有更牛比的方法請指教，謝謝
 
 




聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com