(C#) ASP.NET Import CSV to Database |
(C#) ASP.NET Import CSV to Database เป็นการเขียน ASP.NET ใช้ ADO.NET อ่านไฟล์ CSV และทำการ INSERT ลงใน Database
Language Code : VB.NET ||  C#
Framework : 1,2,3,4
csv/customer.csv
CustomerID,Name,Email,CountryCode,Budget,Used
"C001","Win Weerachai","[email protected]","TH","1,000,000.00","600,000.00"
"C002","John Smith","[email protected]","EN","2,000,000.00","800,000.00"
"C003","Jame Born","[email protected]","US","3,000,000.00","600,000.00"
"C004","Chalee Angel","[email protected]","US","4,000,000.00","100,000.00"
AspNetCsvToDatabase.aspx
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<%@ Page Language="C#" Debug="true" %>
<script runat="server">
void Page_Load(object sender,EventArgs e)
{
DataTable dt;
//*** Read CSV to DataTable ***//
dt = CsvCreateDataTable("csv/","customer.csv");
//*** 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:Label id="lblText" runat="server"></asp:Label>
</form>
</body>
</html>
Screenshot
|
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน 
Load balance : Server 00
