Protected Sub Btnimp_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Btnimp.Click
Dim CurrentFileName As String
Dim CurrentPath As String
Dim dt1 As String
Dim dt2 As String
CurrentFileName = myFile1.FileName
If (Path.GetExtension(CurrentFileName).ToLower <> ".csv") Then
ClientScript.RegisterStartupScript(Me.GetType(), "ShowAlert", ShowAlert("Error : Please Insert Excel File(.csv)"))
Exit Sub
End If
If Not IsNothing(myFile1.PostedFile) Then
Dim UlFileName As String
Dim i As Integer
UlFileName = "Upload/" & System.IO.Path.GetFileName(myFile1.ToString)
'*** Save Images ***'
myFile1.PostedFile.SaveAs(Server.MapPath(UlFileName))
End If
Dim strPath As String = "csv/"
Dim dt As New System.Data.DataTable
If Me.myFile1.HasFile Then
Me.myFile1.SaveAs(Server.MapPath(strPath & myFile1.FileName))
'*** Read CSV to DataTable ***'
dt = CsvCreateDataTable(strPath, Trim(myFile1.FileName))
'*** Insert to Database ***'
InsertToDatabase(dt)
End If
End Sub
Function CsvCreateDataTable(ByVal strPath, ByVal strFilesName) As DataTable
Dim objConn As New OleDbConnection
Dim dtAdapter As OleDbDataAdapter
Dim dt As New DataTable
Dim dr As System.Data.DataRow
Dim i As Integer
Dim strConnString As String
'*** Column ***'
dt.Columns.Add("Serial")
dt.Columns.Add("Model")
dt.Columns.Add("Name")
dt.Columns.Add("User")
dt.Columns.Add("Type")
dt.Columns.Add("EMPLOYEEID")
dt.Columns.Add("DEPTCODE")
dt.Columns.Add("DEPTNAME")
dt.Columns.Add("TEL")
dt.Columns.Add("INVOICEDATE")
dt.Columns.Add("EXPIRE")
dt.Columns.Add("Brand")
dt.Columns.Add("Mail")
dt.Columns.Add("Fac")
dt.Columns.Add("Location")
dt.Columns.Add("Team")
dt.Columns.Add("Vendor_code")
dt.Columns.Add("Vendor_name")
Dim StrWer As StreamReader
Dim strLine As String
StrWer = File.OpenText(Server.MapPath(strPath & strFilesName))
Do Until StrWer.EndOfStream
strLine = StrWer.ReadLine()
If Trim(strLine) <> "" Then
'*** Rows ***'
If Mid(strLine, 1, 6) <> "Serial" Then
dr = dt.NewRow
dr("Serial") = Split(strLine, ",")(0)
dr("Model") = Split(strLine, ",")(1)
dr("Name") = Split(strLine, ",")(2)
dr("User") = Split(strLine, ",")(3)
dr("Type") = Split(strLine, ",")(4)
dr("EMPLOYEEID") = Split(strLine, ",")(5)
dr("DEPTCODE") = Split(strLine, ",")(6)
dr("DEPTNAME") = Split(strLine, ",")(7)
dr("TEL") = Split(strLine, ",")(8)
dr("INVOICEDATE") = Split(strLine, ",")(9)
dr("EXPIRE") = Split(strLine, ",")(10)
dr("Brand") = Split(strLine, ",")(11)
dr("Mail") = Split(strLine, ",")(12)
dr("Fac") = Split(strLine, ",")(13)
dr("Location") = Split(strLine, ",")(14)
dr("Team") = Split(strLine, ",")(15)
dr("Vendor_code") = Split(strLine, ",")(16)
dr("Vendor_name") = Split(strLine, ",")(17)
dt.Rows.Add(dr)
End If
End If
Loop
StrWer.Close()
Return dt '*** Return DataTable ***
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Partial Class ReadCsv
Inherits System.Web.UI.Page
Private dataTable As DataTable
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
dataTable = New DataTable()
dataTable = ReadData()
GridView1.DataSource = dataTable
AddHandler GridView1.RowDataBound, AddressOf GridView1_RowDataBound
GridView1.DataBind()
AddHandler ImportButton.Click, AddressOf ImportButton_Click
End Sub
Protected Function ReadData() As DataTable
' อ่านข้อมูลจาก csv มาเก็บไว้ใน DataTable
Dim oleDbConnectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties='TEXT;HDR=Yes;FMT=Delimited;Format=Delimited(,)'", Page.Server.MapPath("~/"))
Dim oleDbConnection As New OleDbConnection(oleDbConnectionString)
Dim oleDbCommandString As String = "SELECT [เวลา] As [OnAirTime], [รายการ] As [Schedule] FROM [Book1.csv]"
Dim oleDbCommand As New OleDbCommand(oleDbCommandString, oleDbConnection)
Dim Dt As New DataTable()
Dim dataAdapter As New OleDbDataAdapter()
dataAdapter.SelectCommand = oleDbCommand
dataAdapter.Fill(Dt)
Return Dt
End Function
Protected Sub ImportButton_Click(ByVal sender As Object, ByVal e As EventArgs)
' insert ข้อมูลใน DataTable ลงฐานข้อมูล
Dim sqlConnectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=SqlDatabase;Integrated Security=True"
Dim sqlConnection As New SqlConnection(sqlConnectionString)
Dim sqlCommandString As String = "Insert Into [MyTable] ([OnAirTime], [Schedule]) Values (@OnAirTime, @Schedule)"
Dim sqlCommand As New SqlCommand(sqlCommandString, sqlConnection)
'รูปแบบการกำหนดค่า: ชื่อพารามิเตอร์, ดาต้าไทป์, ขนาด, ชื่อคอลั่มของดาต้าเทเบิลที่จะป้อนให้พารามิเตอร์
sqlCommand.Parameters.Add("@OnAirTime", SqlDbType.NVarChar, 50, "OnAirTime")
sqlCommand.Parameters.Add("@OnAirTime", SqlDbType.NVarChar, 200, "Schedule")
Dim dataAdapter As New SqlDataAdapter()
dataAdapter.InsertCommand = sqlCommand
dataAdapter.Update(dataTable) 'กำหนดดาต้าเทเบิลที่ใช้ในการ insert
End Sub
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
e.Row.Cells(0).Text = DateTime.Parse(e.Row.Cells(0).Text).ToShortTimeString()
e.Row.Cells(0).HorizontalAlign = HorizontalAlign.Center
End If
End Sub
End Class
Load balance : Server 02
