(C#) ASP.NET Upload & Import CSV to Database |
(C#) ASP.NET Upload & Import CSV to Database เป็นการอัพโหลดไฟล์ CSV และทำการ Insert ข้อมูลลงใน Database
Language Code : VB.NET ||  C#
Framework : 1,2,3,4
AspNetUploadCsvToDatabase.aspx
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<%@ Page Language="C#" Debug="true" %>
<script runat="server">
void btnUpload_OnClick(Object sender,EventArgs e)
{
String strPath = "csv/";
DataTable dt;
if(this.fiUpload.HasFile)
{
this.fiUpload.SaveAs(Server.MapPath(strPath + fiUpload.FileName));
//*** Read CSV to DataTable ***//
dt = CsvCreateDataTable(strPath,fiUpload.FileName);
//*** Insert to Database ***//
InsertToDatabase(dt);
}
}
//*** Convert CSV to DataTable ***//
protected DataTable CsvCreateDataTable(String strPath,String strFilesName)
{
OleDbConnection objConn = new OleDbConnection();
OleDbDataAdapter dtAdapter;
DataTable dt = new DataTable();
String strConnString;
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Server.MapPath(strPath) +
";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;Format=Delimited(,)'";
objConn = new OleDbConnection(strConnString);
objConn.Open();
String strSQL;
strSQL = "SELECT * FROM " + strFilesName;
dtAdapter = new OleDbDataAdapter(strSQL, objConn);
dtAdapter.Fill(dt);
dtAdapter = null;
objConn.Close();
objConn = null;
return dt; //*** Return DataTable ***//
}
protected void InsertToDatabase(DataTable dt)
{
System.Data.OleDb.OleDbConnection objConn;
System.Data.OleDb.OleDbCommand objCmd;
String strConnString,strSQL;
int i;
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
Server.MapPath("database/mydatabase.mdb")+";";
objConn = new System.Data.OleDb.OleDbConnection(strConnString);
objConn.Open();
//*** Loop Insert ***//
for( i = 0 ; i <= dt.Rows.Count - 1 ; i++)
{
try
{
strSQL = "INSERT INTO customer2 (CustomerID,Name,Email,CountryCode,Budget,Used) " +
"VALUES ('"+dt.Rows[i]["CustomerID"]+"','"+dt.Rows[i]["Name"]+"','"+dt.Rows[i]["Email"]+"'" +
" ,'"+dt.Rows[i]["CountryCode"]+"','"+dt.Rows[i]["Budget"]+"','"+dt.Rows[i]["Used"]+"')";
objCmd = new System.Data.OleDb.OleDbCommand();
objCmd.Connection = objConn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = strSQL;
objCmd.ExecuteNonQuery();
this.lblText.Text = this.lblText.Text + "["+dt.Rows[i]["CustomerID"]+"] Inserted <br>";
}
catch (Exception err)
{
this.lblText.Text = this.lblText.Text + "["+dt.Rows[i]["CustomerID"]+"] Not Insert <br>";
}
}
objCmd = null;
objConn.Close();
objConn = null;
}
</script>
<html>
<head>
<title>ThaiCreate.Com ASP.NET - CSV</title>
</head>
<body>
<form id="form1" runat="server">
<asp:FileUpload id="fiUpload" runat="server"></asp:FileUpload>
<input id="btnUpload" type="button" OnServerClick="btnUpload_OnClick" value="Upload" runat="server" />
<hr />
<asp:Label id="lblText" runat="server"></asp:Label>
</form>
</body>
</html>
Screenshot
|
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน 
Load balance : Server 03
