.net读Csv并导入SqlServer数据库
using System;using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using qiupeng.Public;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data;
namespace qphr.JiGouManage
{
public partial class SengChanchanpin : System.Web.UI.Page
{
Db List2 = new Db();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button2_Click(object sender, EventArgs e)
{
String findName = "select count(*) from qp_hr_Kqwj where wjname='" + FileUpload2.FileName + "'";
int findNum = List2.GetCount(findName);
String Fname = FileUpload2.FileName;
if (findNum > 0)
{
Response.Write("<script language='javascript'>alert('已经上传过该文件!');</script>");
}
else
{
if (FileUpload2.HasFile)
{
try
{
FileUpload2.PostedFile.SaveAs(Server.MapPath("~/HumanManage/Excel/") + FileUpload2.FileName);
ResultMessage2.Text = typename(FileUpload2) + " 上载成功";
Label2.Text = typename(FileUpload2);
Label3.Text = FileUpload2.FileName;
}
catch (Exception ex)
{
ResultMessage2.Text = FileUpload2.FileName + " 上传发生错误";
throw ex;
}
}
}
}
protected void Sujudaoru(object sender, EventArgs e)
{
}
//把EXCEL文件上传到服务器并返回文件路径
private String typename(FileUpload fileloads)
{
string fullfilename = fileloads.PostedFile.FileName;
string filename = fullfilename.Substring(fullfilename.LastIndexOf("\\") + 1);
string type = fullfilename.Substring(fullfilename.LastIndexOf(".") + 1);
string murl = "";
if (type == "xls" || type == "csv" || type == "xlsx")
{
fileloads.PostedFile.SaveAs(Server.MapPath("~/HumanManage/Excel/") + "\\" + filename);
murl = (Server.MapPath("~/HumanManage/Excel/") +filename).ToString();
}
else
{
Response.Write("<script language='javascript'>alert('导入文件格式不对!');</script>");
}
return murl;
}
//
//把excel数据读入dataset返回l数据集
private DataSet xsldata(string filepath)
{
string strCon = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + filepath + ";Extensions=asc,csv,tab,txt;";
//string strCon = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;IMEX=1';";
//string strCon = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + filepath + ";Extended Properties='Text;FMT=Delimited;HDR=YES';";
//string strCon = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + filepath + ";Extended Properties='Text;FMT=Delimited;HDR=YES;'";
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
Conn.Open();
string strCom = "SELECT * FROM "+Label3.Text;
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "Csv");
Conn.Close();
return ds;
}
protected void Button1_Click(object sender, EventArgs e)
{
//批量添加信息
try
{
string fileurl = Label2.Text ;//调用typename方法取得excel文件路径
DataSet ds = new DataSet();//取得数据集
ds = xsldata(fileurl);
int errorcount = 0;//记录错误信息条数
int insertcount = 0;//记录插入成功条数
int updatecount = 0;//记录更新信息条数
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string ShenchanStyle = ds.Tables[0].Rows[i][0].ToString();
string ShenchanDatetime = ds.Tables[0].Rows[i][1].ToString();
string ShenchanBianhao = ds.Tables[0].Rows[i][2].ToString();
string ShenchanShuliang = ds.Tables[0].Rows[i][3].ToString();
if (ShenchanStyle != "" && ShenchanDatetime != "" && ShenchanBianhao != "" && ShenchanShuliang != "")
{
string sql_insert1 = "insert into qp_hr_Kqdr (style,datetime,bianhao,shuliang)values('" + ShenchanStyle + "','" + ShenchanDatetime + "','" + ShenchanBianhao + "','" + ShenchanShuliang + ")";
int ResultO = List2.ExeSql(sql_insert1);
//SqlCommand insertcmd = new SqlCommand("insert into test (yuangongid,data)values('" + yuangongid + "','" + da + "')", con);
//insertcmd.ExecuteNonQuery();
insertcount++;
if (ResultO == 0)
{
Response.Write("<script language='javascript'>alert('数据有错，检查是否符合标准格式！');window.location.href='UploadKqwj.aspx'</script>");
break;
}
}
else
{
errorcount++;
}
}
Response.Write("<script language='javascript'>alert('" + insertcount + "条数据导入成功！" + updatecount + "条数据更新成功！" + errorcount + "条数据部分信息为空没有导入！');</script>");
}
catch (Exception s)
{
Response.Write("<script language='javascript'>alert('导入失败！');</script>");
}
}
}
} --------------------编程问答-------------------- 什么意思，错误又是什么呢？单步调试总能发现问题吧？ --------------------编程问答-------------------- 贴一堆代码，不知所云。。。 --------------------编程问答-------------------- string strCon = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + filepath + ";Extensions=asc,csv,tab,txt;";
应该是这个错误了，但是现在也没有解决办法。
补充：.NET技术 , C#
