asp.net 解析csv 逐条插入数据库

类：

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文件");
                }
            }
        }


    }
}