asp.Net解析csvデータベースを1つずつ挿入
9070 ワード
クラス:
バックグラウンドコード:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;
using Model;
namespace BLL.BLLCustom
{
public class ReadCsv
{
private string _filename;
private string _filepath;
private OleDbConnection OleCon = new OleDbConnection();
private OleDbCommand OleCmd = new OleDbCommand();
private OleDbDataAdapter OleDa = new OleDbDataAdapter();
private SqlConnection SqlCon = new SqlConnection();
/// <summary>
/// :
/// </summary>
/// <param name="FileName"> Csv </param>
/// <param name="FilePath"> ( Csv )</param>
public ReadCsv(string FileName, string FilePath)
{
_filename = FileName;
_filepath = FilePath;
}
#region Csv DataSet
/// <summary>
/// Csv DataSet
/// </summary>
/// <returns>Csv </returns>
public DataSet Csv()
{
DataSet CsvData = new DataSet();
OleCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _filepath + ";Extended Properties='Text;FMT=Delimited;HDR=YES;'";
OleCon.Open();
OleCmd.Connection = OleCon;
OleCmd.CommandText = "select * From " + _filename;
OleDa.SelectCommand = OleCmd;
try
{
OleDa.Fill(CsvData, "Csv");
return CsvData;
}
catch
{
return CsvData;
}
finally
{
OleCon.Close();
OleCmd.Dispose();
OleDa.Dispose();
OleCon.Dispose();
}
}
#endregion
}
}
バックグラウンドコード:
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using Common;
using BLL.BLLCustom;
using Model;
namespace YESSHOP.AdminSupplies.ProductList
{
public partial class ProductUpload : System.Web.UI.Page
{
private int ProductAllCount;
private int ProductSuccess=0;
private int ProductFalse = 0;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (!productFileUpload.HasFile)
{
Js.Alert(" !");
}
else
{
if (Path.GetExtension(productFileUpload.FileName).ToLower() == ".csv")
{
try
{
productFileUpload.PostedFile.SaveAs(Server.MapPath("~/")+productFileUpload.FileName);
ReadCsv RC = new ReadCsv(productFileUpload.FileName, Server.MapPath("~/"));
DataTable ProductDt = RC.Csv().Tables[0];
ProductAllCount = ProductDt.Rows.Count;
for (int i = 0; i < ProductDt.Rows.Count; i++)
{
ModelProduct model = new ModelProduct();
model.SupplierId = Convert.ToInt32(ProductDt.Rows[i]["SupplierId"]);
model.ProductName = ProductDt.Rows[i]["ProductName"].ToString();
model.StyleID = Convert.ToInt32(ProductDt.Rows[i]["StyleID"]);
model.Color = ProductDt.Rows[i]["Color"].ToString();
model.ProductNum = ProductDt.Rows[i]["ProductNum"].ToString();
model.CategoriesID = Convert.ToInt32(ProductDt.Rows[i]["CategoriesID"]);
model.SupplyPrice =Convert.ToDecimal(ProductDt.Rows[i]["SupplyPrice"]);
model.AdvicePrice = Convert.ToDecimal(ProductDt.Rows[i]["AdvicePrice"]);
model.ShopPrice = Convert.ToDecimal(ProductDt.Rows[i]["ShopPrice"]);
model.MarketPrice = Convert.ToDecimal(ProductDt.Rows[i]["MarketPrice"]);
model.Stocks = Convert.ToInt32(ProductDt.Rows[i]["Stocks"]);
model.ProductWeight =Convert.ToDecimal(ProductDt.Rows[i]["ProductWeight"]);
model.ProductUnit = ProductDt.Rows[i]["ProductUnit"].ToString();
model.IsRebate =Convert.ToBoolean(ProductDt.Rows[i]["IsRebate"]);
model.Recommend = Convert.ToBoolean(ProductDt.Rows[i]["Recommend"]);
model.Latest = Convert.ToBoolean(ProductDt.Rows[i]["Latest"]);
model.HotSale = Convert.ToBoolean(ProductDt.Rows[i]["HotSale"]);
model.SpecialOffer = Convert.ToBoolean(ProductDt.Rows[i]["SpecialOffer"]);
model.Marque = ProductDt.Rows[i]["Marque"].ToString();
model.ProductBrandID = Convert.ToInt32(ProductDt.Rows[i]["ProductBrandID"]);
model.UpSelling = Convert.ToBoolean(ProductDt.Rows[i]["UpSelling"]);
model.Keywords = ProductDt.Rows[i]["Keywords"].ToString();
model.ProductSummary = ProductDt.Rows[i]["ProductSummary"].ToString();
model.ProductDescirption = ProductDt.Rows[i]["ProductDescirption"].ToString();
model.VisitNum = Convert.ToInt32( ProductDt.Rows[i]["VisitNum"]);
model.SortNo = Convert.ToInt32( ProductDt.Rows[i]["SortNo"]);
model.CreateTime =Convert.ToDateTime(ProductDt.Rows[i]["CreateTime"]);
model.UpdateTime =Convert.ToDateTime(ProductDt.Rows[i]["UpdateTime"]);
model.IsInningStore =Convert.ToBoolean(ProductDt.Rows[i]["IsInningStore"]);
model.Recycle = Convert.ToBoolean(ProductDt.Rows[i]["Recycle"]);
model.Audit = Convert.ToInt32(ProductDt.Rows[i]["Audit"]);
model.Auditor = Convert.ToInt32(ProductDt.Rows[i]["Auditor"]);
model.AuditOpinion = ProductDt.Rows[i]["AuditOpinion"].ToString();
model.AuditTime = Convert.ToDateTime(ProductDt.Rows[i]["AuditTime"]);
model.ProductTypeId = Convert.ToInt32(ProductDt.Rows[i]["ProductTypeId"]);
model.VCategoriesID = Convert.ToInt32(ProductDt.Rows[i]["VCategoriesID"]);
model.SalePrice =Convert.ToDecimal(ProductDt.Rows[i]["SalePrice"]);
model.Remark = ProductDt.Rows[i]["Remark"].ToString();
try
{
if (new BLL.BLLProduct().Add(model)>0)
{
ProductSuccess += 1;
}
else
{
ProductFalse += 1;
}
}
catch
{
}
}
//Response.Write(" :" + ProductSuccess + " <br/><br/> :" + ProductSuccess + " ");
//GridView1.DataSource = RC.Csv();
//GridView1.DataBind();
//Js.Alert(" ");
}
catch (Exception ex)
{
Js.Alert(" ");
throw ex;
}
}
else
{
Js.Alert(" .csv ");
}
}
}
}
}