Web (ASP.NET) สอบถาม C# ในการ up text file แล้ว insert ลง sql server
แปลงเป็น ASP.NET + SQL Server (C#) ให้ครับCode (C#) <%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<%@ 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)
{
SqlConnection objConn = new SqlConnection();
SqlDataAdapter dtAdapter;
DataTable dt = new DataTable();
String strConnString;
strConnString = "Server=localhost;Uid=sa;PASSWORD=;database=mydatabase;Max Pool Size=400;Connect Timeout=600;";
objConn = new SqlConnection(strConnString);
objConn.Open();
String strSQL;
strSQL = "SELECT * FROM " + strFilesName;
dtAdapter = new SqlDataAdapter(strSQL, objConn);
dtAdapter.Fill(dt);
dtAdapter = null;
objConn.Close();
objConn = null;
return dt; //*** Return DataTable ***//
}
protected void InsertToDatabase(DataTable dt)
{
System.Data.SqlConnection objConn;
System.Data.SqlCommand objCmd;
String strConnString,strSQL;
int i;
strConnString = "Server=localhost;Uid=sa;PASSWORD=;database=mydatabase;Max Pool Size=400;Connect Timeout=600;";
objConn = new System.Data.SqlConnection(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.SqlCommand();
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>
Date :
2011-12-07 10:20:25By :
webmaster
ขอบคุณก๊าบบบผม
Date :
2011-12-09 15:09:38By :
MaI2shi
รบกวนอีกเรื่องครับ ถ้าเราต้องการตัด ข้อความใส่ array โดยปกติใช้ \n มีตัวอื่นอีกไหมครับที่แทน \n
Date :
2011-12-14 17:33:12By :
MaI2shi
อันนี้ตัวอย่างการอ่าน Text File เก็บลงใน DataTablethaicreate.txt C001,Win Weerachai,[email protected] ,TH,1000000,600000[email protected] ,EN,2000000,800000[email protected] ,US,3000000,600000[email protected] ,US,4000000,100000 Code (C#) DataTable dt = new DataTable();
DataRow dr = null;
//*** Column ***'
dt.Columns.Add("CustomerID");
dt.Columns.Add("Name");
dt.Columns.Add("Email");
dt.Columns.Add("CountryCode");
dt.Columns.Add("Budget");
dt.Columns.Add("Used");
StreamReader StrWer = default(StreamReader);
string readLine = null;
StrWer = File.OpenText(Server.MapPath("thaicreate.txt"));
while (!(StrWer.EndOfStream)) {
readLine = StrWer.ReadLine();
//*** Rows ***'
dr = dt.NewRow();
dr["CustomerID"] = Strings.Split(readLine, ",")[0];
dr["Name"] = Strings.Split(readLine, ",")[1];
dr["Email"] = Strings.Split(readLine, ",")[2];
dr["CountryCode"] = Strings.Split(readLine, ",")[3];
dr["Budget"] = Strings.Split(readLine, ",")[4];
dr["Used"] = Strings.Split(readLine, ",")[5];
dt.Rows.Add(dr);
}
StrWer.Close();
Date :
2012-03-22 10:09:10By :
webmaster
Strings.Split มันคืออะไรกันครับ
Date :
2012-10-13 12:00:59By :
kanong
Server 04
Load balance : Server 04
