(C#) ASP.NET Export Database To CSV |
(C#) ASP.NET Export Database To CSV ตัวอย่างการ Export ข้อมูลจาก Database เขียนไปไว้ในไฟล์ CSV โดยใช้ ADO.NET ผ่าน NameSpace ชื่อ System.Data.OleDb
Language Code : VB.NET ||  C#
Framework : 1,2,3,4
AspNetExportDatabaseToCsv.aspx
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<%@ Import Namespace="System.IO"%>
<%@ Page Language="C#" Debug="true" %>
<script runat="server">
void Page_Load(object sender,EventArgs e)
{
DataTable dt;
//*** Read DataTable ***//
dt = CreateDataTable();
//*** Export to CSV ***//
ExportToCSV(dt);
}
//*** Create to DataTable ***//
protected DataTable CreateDataTable()
{
OleDbConnection objConn = new OleDbConnection();
OleDbDataAdapter dtAdapter;
DataTable dt = new DataTable();
String strConnString;
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
Server.MapPath("database/mydatabase.mdb")+";";
objConn = new OleDbConnection(strConnString);
objConn.Open();
String strSQL;
strSQL = "SELECT * FROM customer ";
dtAdapter = new OleDbDataAdapter(strSQL, objConn);
dtAdapter.Fill(dt);
dtAdapter = null;
objConn.Close();
objConn = null;
return dt; //*** Return DataTable ***//
}
protected void ExportToCSV(DataTable dt)
{
System.Data.OleDb.OleDbConnection objConn;
System.Data.OleDb.OleDbCommand objCmd;
String strConnString,strSQL;
int i;
String strPath = "csv/";
String strFileName = "customer.csv";
//*** Create Text Files (Columns Default ***// ***//
StreamWriter StrWer;
StrWer = File.CreateText(Server.MapPath(strPath) + strFileName);
StrWer.Write("CustomerID,Name,Email,CountryCode,Budget,Used");
StrWer.Close();
//*** Connect to CSV ***//
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Server.MapPath(strPath) +
";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;Format=Delimited(,)'";
objConn = new System.Data.OleDb.OleDbConnection(strConnString);
objConn.Open();
//*** Loop Write CSV ***//
for(i = 0;i <= dt.Rows.Count - 1;i++)
{
strSQL = "INSERT INTO " + strFileName + " (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 = "Export Successfully <a href=" + strPath + strFileName + ">Click here</a> ";
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
|
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน 
