dn_learner
asked on
importing Excel to SQL server - runs a bit slow - can we make it work faster ??
Hello All,
Iam new to the world of ASP .Net. Right now iam building an application that will IMPORT about 5,000 records from an Excel spreadsheet to a table in MS SQL Server. Right now the code works correctly, but i feel it is not efficient and takes a little bit of more time in doing the import. Could you guys throw some light on how i can make the code run more faster ? Someone suggested me that i can use DataAdapter and update the table in the database thru an update method available with it. I dont know how to do it? Could anyone share with me a snippet of code that does this ?
Here is my code:
Private Sub ProcessRecords()
Dim ds2 As New DataSet
' readExcelSheet is a user-defined function that reads a spreadsheet and returns a DataSet object
ds2 = readExcelSheet("C:\Inetpub \wwwroot\P roject1\Bo ok2.xls", "SELECT * FROM [Sheet1$]")
Dim myConnection As SqlConnection = Connection() ' user-defined function that returns a SQLConnection object
myConnection.Open()
Dim strSQL As String = "insert_member" ' stored procedure that inserts records
Dim myCommand As New SqlCommand(strSQL, myConnection)
myCommand.CommandType = CommandType.StoredProcedur e
myCommand.Parameters.Add(" @salutatio n", SqlDbType.NVarChar)
myCommand.Parameters.Add(" @firstname ", SqlDbType.NVarChar)
myCommand.Parameters.Add(" @lastname" , SqlDbType.NVarChar)
myCommand.Parameters.Add(" @company", SqlDbType.NVarChar)
Dim i, j As Integer
Response.Write(Date.Now() & "<br>")
For i = 0 To ds2.Tables("Members").Rows .Count() - 1
myCommand.Parameters("@sal utation"). Value = ds2.Tables("Members").Rows (i).Item(" sal")
myCommand.Parameters("@fir stname").V alue = ds2.Tables("Members").Rows (i).Item(" firstname" )
myCommand.Parameters("@las tname").Va lue = ds2.Tables("Members").Rows (i).Item(" lastname")
myCommand.Parameters("@com pany").Val ue = ds2.Tables("Members").Rows (i).Item(" company")
j = myCommand.ExecuteNonQuery( )
If (j > 0) Then
Response.Write("Record Inserted - " & i + 1 & "<br>")
End If
Next
Response.Write(Date.Now() & "<br>")
myConnection.Close()
End Sub
Please reply soon.
Thank You.
Iam new to the world of ASP .Net. Right now iam building an application that will IMPORT about 5,000 records from an Excel spreadsheet to a table in MS SQL Server. Right now the code works correctly, but i feel it is not efficient and takes a little bit of more time in doing the import. Could you guys throw some light on how i can make the code run more faster ? Someone suggested me that i can use DataAdapter and update the table in the database thru an update method available with it. I dont know how to do it? Could anyone share with me a snippet of code that does this ?
Here is my code:
Private Sub ProcessRecords()
Dim ds2 As New DataSet
' readExcelSheet is a user-defined function that reads a spreadsheet and returns a DataSet object
ds2 = readExcelSheet("C:\Inetpub
Dim myConnection As SqlConnection = Connection() ' user-defined function that returns a SQLConnection object
myConnection.Open()
Dim strSQL As String = "insert_member" ' stored procedure that inserts records
Dim myCommand As New SqlCommand(strSQL, myConnection)
myCommand.CommandType = CommandType.StoredProcedur
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
Dim i, j As Integer
Response.Write(Date.Now() & "<br>")
For i = 0 To ds2.Tables("Members").Rows
myCommand.Parameters("@sal
myCommand.Parameters("@fir
myCommand.Parameters("@las
myCommand.Parameters("@com
j = myCommand.ExecuteNonQuery(
If (j > 0) Then
Response.Write("Record Inserted - " & i + 1 & "<br>")
End If
Next
Response.Write(Date.Now() & "<br>")
myConnection.Close()
End Sub
Please reply soon.
Thank You.
In SQL Server you can use DTS to import from Excel sheet to table. Is that an option?
a datareader would be faster than a dataset
Aeros
Aeros
Public Sub Submit1_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit1.ServerClick
'Variable declerations
Dim cnnMisc As New OleDb.OleDbConnection(Conf igurationS ettings.Ap pSettings( "SiteDB"))
Dim cnnExcel As New OleDb.OleDbConnection("Pro vider=Micr osoft.Jet. OLEDB.4.0; User ID=Admin;Data Source=C:\Inetpub\wwwroot\ Blaise\Upl oad.XLS;Mo de=ReadWri te;Extende d Properties=""Excel 8.0;HDR=YES;IMEX=1"";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False")
Dim cmdReadChanges As New OleDb.OleDbCommand
Dim cmdClearTable As New OleDb.OleDbCommand
Dim cmdWriteChanges As New OleDb.OleDbCommand
Dim drChanges As OleDb.OleDbDataReader
Dim intColIndex, intContentLength As Integer
Dim strStockNumber, strYear, strMake, strModel, strColor, strPrice, strFileName, strDealership, strDealershipID, strNew, strTempNew, strMiles As String
Dim temp As Text.StringBuilder
'Extract File Name,get DealerID, and New boolean value
strFileName = System.IO.Path.GetFileName WithoutExt ension(Fil e1.Value)
strDealership = strFileName
If strDealership.StartsWith(" Montoursvi lle") Then
strDealershipID = 1
ElseIf strDealership.StartsWith(" Selinsgrov e") Then
strDealershipID = 2
ElseIf strDealership.StartsWith(" MountCarme l") Then
strDealershipID = 3
ElseIf strDealership.StartsWith(" WilkesBarr e") Then
strDealershipID = 4
ElseIf strDealership.StartsWith(" Williamspo rt0") Then
strDealershipID = 5
ElseIf strDealership.StartsWith(" Hughesvill e") Then
strDealershipID = 6
ElseIf strDealership.StartsWith(" Williamspo rt1") Then
strDealershipID = 7
ElseIf strDealership.StartsWith(" Sunbury") Then
strDealershipID = 8
ElseIf strDealership.StartsWith(" Bloomsburg ") Then
strDealershipID = 9
End If
strTempNew = strFileName.EndsWith("New" )
If strTempNew = "True" Then
strNew = "True"
Else
strNew = "False"
End If
'Set connections and select Excel data
cmdReadChanges.Connection = cnnExcel
cmdClearTable.Connection = cnnMisc
cmdWriteChanges.Connection = cnnMisc
If strNew = "True" Then
cmdReadChanges.CommandText = "SELECT * FROM [RWTemp$] WHERE [F1] IS NOT NULL AND [F3] NOT LIKE '--%' AND [WEBS] <> 'MODEL DESCRIPTION'"
Else
cmdReadChanges.CommandText = "SELECT * FROM [RWTemp$] WHERE [F2] IS NOT NULL AND [F3] NOT LIKE '--%' AND [F4] <> 'MODEL DESCRIPTION'"
End If
'Save Excel data locally
If Not (File1.PostedFile Is Nothing) Then
Try
File1.PostedFile.SaveAs("C :\Inetpub\ wwwroot\bl aise\uploa d.xls")
Span1.InnerHtml = "<b>Upload Successful!</b>"
Catch ex As Exception
Span1.InnerHtml = "Error saving file <b>C:\\" & _
File1.Value & "</b><br>" & ex.ToString()
End Try
End If
'Select New/Used and Get strings from Excel data accordingly
cnnExcel.Open()
cnnMisc.Open()
drChanges = cmdReadChanges.ExecuteRead er(Command Behavior.C loseConnec tion)
drChanges.Read()
'Clear appropriate information based on dealer and new/used status before importation
cmdClearTable.CommandText = "DELETE * FROM Vehicle WHERE DealershipID = " & strDealershipID & " AND New = " & strNew
cmdClearTable.ExecuteNonQu ery()
While drChanges.Read()
For intColIndex = 0 To drChanges.HasRows = False
If strNew = "True" Then
If drChanges.IsDBNull(0) Then
strStockNumber = "N/A"
Else
strStockNumber = drChanges.GetString(0)
End If
strYear = drChanges.GetString(1)
strMake = drChanges.GetString(2)
strModel = drChanges.GetString(3)
If drChanges.IsDBNull(4) Then
strColor = "N/A"
Else
strColor = drChanges.GetString(4)
End If
If drChanges.IsDBNull(5) Then
strPrice = 0
Else
strPrice = drChanges.GetString(5)
End If
Else
strStockNumber = drChanges.GetString(0)
strYear = drChanges.GetDouble(1)
strMake = drChanges.GetString(2)
strModel = drChanges.GetString(3)
If drChanges.IsDBNull(4) Then
strColor = "N/A"
Else
strColor = drChanges.GetString(4)
End If
If drChanges.IsDBNull(5) Then
strPrice = 0
Else
strPrice = drChanges.GetDouble(5)
End If
If drChanges.IsDBNull(6) Then
strMiles = 0
Else
strMiles = drChanges.GetDouble(6)
End If
End If
'Insert new data into database based upon New boolean value
'Response.Write(drChanges. GetValue(i ntColIndex ) & "<br>")
If drChanges.HasRows And strNew = "True" Then
cmdWriteChanges.CommandTex t = "INSERT INTO Vehicle (StockNumber,[Year],Make,M odel,Color ,Price,Dea lershipID, New) VALUES (" & "'" & strStockNumber & "'," & strYear & ",'" & strMake & "','" & strModel & "','" & strColor & "'," & strPrice & "," & strDealershipID & "," & strNew & ")"
cmdWriteChanges.ExecuteNon Query()
Else
cmdWriteChanges.CommandTex t = "INSERT INTO Vehicle (StockNumber,[Year],Make,M odel,Color ,Price,Dea lershipID, New,Miles) VALUES (" & "'" & strStockNumber & "'," & strYear & ",'" & strMake & "','" & strModel & "','" & strColor & "'," & strPrice & "," & strDealershipID & "," & strNew & "," & strMiles & ")"
cmdWriteChanges.ExecuteNon Query()
End If
Next
End While
'Close and dispose of connections
drChanges.Close()
cnnExcel.Close()
cnnMisc.Close()
cmdReadChanges.Dispose()
cmdWriteChanges.Dispose()
cmdClearTable.Dispose()
cnnExcel.Dispose()
cnnMisc.Dispose()
End Sub
'Variable declerations
Dim cnnMisc As New OleDb.OleDbConnection(Conf
Dim cnnExcel As New OleDb.OleDbConnection("Pro
Dim cmdReadChanges As New OleDb.OleDbCommand
Dim cmdClearTable As New OleDb.OleDbCommand
Dim cmdWriteChanges As New OleDb.OleDbCommand
Dim drChanges As OleDb.OleDbDataReader
Dim intColIndex, intContentLength As Integer
Dim strStockNumber, strYear, strMake, strModel, strColor, strPrice, strFileName, strDealership, strDealershipID, strNew, strTempNew, strMiles As String
Dim temp As Text.StringBuilder
'Extract File Name,get DealerID, and New boolean value
strFileName = System.IO.Path.GetFileName
strDealership = strFileName
If strDealership.StartsWith("
strDealershipID = 1
ElseIf strDealership.StartsWith("
strDealershipID = 2
ElseIf strDealership.StartsWith("
strDealershipID = 3
ElseIf strDealership.StartsWith("
strDealershipID = 4
ElseIf strDealership.StartsWith("
strDealershipID = 5
ElseIf strDealership.StartsWith("
strDealershipID = 6
ElseIf strDealership.StartsWith("
strDealershipID = 7
ElseIf strDealership.StartsWith("
strDealershipID = 8
ElseIf strDealership.StartsWith("
strDealershipID = 9
End If
strTempNew = strFileName.EndsWith("New"
If strTempNew = "True" Then
strNew = "True"
Else
strNew = "False"
End If
'Set connections and select Excel data
cmdReadChanges.Connection = cnnExcel
cmdClearTable.Connection = cnnMisc
cmdWriteChanges.Connection
If strNew = "True" Then
cmdReadChanges.CommandText
Else
cmdReadChanges.CommandText
End If
'Save Excel data locally
If Not (File1.PostedFile Is Nothing) Then
Try
File1.PostedFile.SaveAs("C
Span1.InnerHtml = "<b>Upload Successful!</b>"
Catch ex As Exception
Span1.InnerHtml = "Error saving file <b>C:\\" & _
File1.Value & "</b><br>" & ex.ToString()
End Try
End If
'Select New/Used and Get strings from Excel data accordingly
cnnExcel.Open()
cnnMisc.Open()
drChanges = cmdReadChanges.ExecuteRead
drChanges.Read()
'Clear appropriate information based on dealer and new/used status before importation
cmdClearTable.CommandText = "DELETE * FROM Vehicle WHERE DealershipID = " & strDealershipID & " AND New = " & strNew
cmdClearTable.ExecuteNonQu
While drChanges.Read()
For intColIndex = 0 To drChanges.HasRows = False
If strNew = "True" Then
If drChanges.IsDBNull(0) Then
strStockNumber = "N/A"
Else
strStockNumber = drChanges.GetString(0)
End If
strYear = drChanges.GetString(1)
strMake = drChanges.GetString(2)
strModel = drChanges.GetString(3)
If drChanges.IsDBNull(4) Then
strColor = "N/A"
Else
strColor = drChanges.GetString(4)
End If
If drChanges.IsDBNull(5) Then
strPrice = 0
Else
strPrice = drChanges.GetString(5)
End If
Else
strStockNumber = drChanges.GetString(0)
strYear = drChanges.GetDouble(1)
strMake = drChanges.GetString(2)
strModel = drChanges.GetString(3)
If drChanges.IsDBNull(4) Then
strColor = "N/A"
Else
strColor = drChanges.GetString(4)
End If
If drChanges.IsDBNull(5) Then
strPrice = 0
Else
strPrice = drChanges.GetDouble(5)
End If
If drChanges.IsDBNull(6) Then
strMiles = 0
Else
strMiles = drChanges.GetDouble(6)
End If
End If
'Insert new data into database based upon New boolean value
'Response.Write(drChanges.
If drChanges.HasRows And strNew = "True" Then
cmdWriteChanges.CommandTex
cmdWriteChanges.ExecuteNon
Else
cmdWriteChanges.CommandTex
cmdWriteChanges.ExecuteNon
End If
Next
End While
'Close and dispose of connections
drChanges.Close()
cnnExcel.Close()
cnnMisc.Close()
cmdReadChanges.Dispose()
cmdWriteChanges.Dispose()
cmdClearTable.Dispose()
cnnExcel.Dispose()
cnnMisc.Dispose()
End Sub
I'm with CJ_S, you should be using DTS, it's built for import/export operations like this. No reason I see to reinvent the wheel.
ASKER
Hi all,
I have to do this import programatically using ASP .Net and VB.Net that iam doing currently. I do NOT want to use DTS....
Praneetha, could you please share a snippet of code on how to update the DB with a DataAdapter. The URL that u mentioned doesn't discuss anything about updating the DataAdapter.
Please help.
Anyways guys, thanks for your fast response and iam still looking for the best solution to solve this problem. Aero, i dont know exactly what your code is doing? Could u please edit my code and address my concern ?
Thank You.
I have to do this import programatically using ASP .Net and VB.Net that iam doing currently. I do NOT want to use DTS....
Praneetha, could you please share a snippet of code on how to update the DB with a DataAdapter. The URL that u mentioned doesn't discuss anything about updating the DataAdapter.
Please help.
Anyways guys, thanks for your fast response and iam still looking for the best solution to solve this problem. Aero, i dont know exactly what your code is doing? Could u please edit my code and address my concern ?
Thank You.
In that case you could perhaps use the following SQL statement:
INSERT INTO databasename.dbo.MyTableNa me (your, fields, here)
SELECT your, fields, here
FROM [Sheet1$]
from a linked server. The Linked Server must be set in SQL Server, but can be an ODBC connection.
I'm just passing options... I am not telling you what to do.
CJ.
INSERT INTO databasename.dbo.MyTableNa
SELECT your, fields, here
FROM [Sheet1$]
from a linked server. The Linked Server must be set in SQL Server, but can be an ODBC connection.
I'm just passing options... I am not telling you what to do.
CJ.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconUpdatingDatabaseWithDataAdapterDataSet.asp
but u need to declare database table with adapter before u declare dataadapter with temp table..and then write a trigger or something to insert into main table(table u want it to insert) whenever there is insert in temp table.... https://www.experts-exchange.com/questions/20953988/Update-Database-using-datagrid-based-on-dataset.html
but u need to declare database table with adapter before u declare dataadapter with temp table..and then write a trigger or something to insert into main table(table u want it to insert) whenever there is insert in temp table.... https://www.experts-exchange.com/questions/20953988/Update-Database-using-datagrid-based-on-dataset.html
ASKER
Hello Praneetha,
Could you please edit my code at the top of the forum addressing my concern. I know iam asking too much .... please help me out.
Basically, my code is calling the stored procedure 5,000 times and i think its not good from a performance point of view. Could u just modify my code and show me how u do it with a DataAdapter using Update method?
The links that you provided are indeed helpfull.
Thank You again.
Could you please edit my code at the top of the forum addressing my concern. I know iam asking too much .... please help me out.
Basically, my code is calling the stored procedure 5,000 times and i think its not good from a performance point of view. Could u just modify my code and show me how u do it with a DataAdapter using Update method?
The links that you provided are indeed helpfull.
Thank You again.
Hi dn_learner,
i am little busy today..will try to get back to you again tomorrow..sorry about that...
good luck
i am little busy today..will try to get back to you again tomorrow..sorry about that...
good luck
ASKER
Hi praneetha,
I understand your situation. I will be eagerly awaiting for your response.
Thank You.
I understand your situation. I will be eagerly awaiting for your response.
Thank You.
Hi,
i have this example code in C# - I am not a vb.net person..but it should not be hard to translate it to vb.net - if you have questions i will try to help..and i am sure there are many other experts who partcicipated in this post can assist u 2...
ok my sqltable has
column1,column2,column3
and my excel book contains
column1 column2 column3
test1 test1 test1
test2 test2 test2
here is the code to read from excel and convert it to dataset....
#region exceltodataset
string strConn;
strConn = "Provider=Microsoft.Jet.OL EDB.4.0;" + "Data Source=C:\\exceltest.xls;" + "Extended Properties=Excel 4.0;";
//You must use the $ after the object you reference in the spreadsheet
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet);
#endregion
code to update database with dataset object...
#region datasettodb
string ConnStr="connection string ";
DataSet ds=new DataSet();
SqlConnection myConnection=new SqlConnection(ConnStr);
try
{
SqlDataAdapter myAdapter=new SqlDataAdapter("select * from tbl_Excel",myConnection);
SqlCommandBuilder custCB = new SqlCommandBuilder(myAdapte r);
myAdapter.SelectCommand.Co mmandType = CommandType.Text;
custCB.RefreshSchema(); // this is for adapter to write own insert command..or else you can write your own insert command...
myAdapter.Fill(ds);
foreach(DataRow dr1 in myDataSet.Tables[0].Rows)
{
DataRow dr=ds.Tables[0].NewRow();
dr[0]=dr1[0];
dr[1]=dr1[1];
dr[2]=dr1[2];
ds.Tables[0].Rows.Add(dr);
}
DataSet ds1=ds.GetChanges();
myAdapter.Update(ds1.Table s[0]);
}
catch(SqlException excep)
{
throw excep;
}
finally
{
myConnection.Close();
}
#endregion
wil try to look at your code and see if i can convert to C#...in the mean while you can try it too..you can try with sample table and sample excel and see how it works...
Thank You.
Good Luck.
i have this example code in C# - I am not a vb.net person..but it should not be hard to translate it to vb.net - if you have questions i will try to help..and i am sure there are many other experts who partcicipated in this post can assist u 2...
ok my sqltable has
column1,column2,column3
and my excel book contains
column1 column2 column3
test1 test1 test1
test2 test2 test2
here is the code to read from excel and convert it to dataset....
#region exceltodataset
string strConn;
strConn = "Provider=Microsoft.Jet.OL
//You must use the $ after the object you reference in the spreadsheet
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet);
#endregion
code to update database with dataset object...
#region datasettodb
string ConnStr="connection string ";
DataSet ds=new DataSet();
SqlConnection myConnection=new SqlConnection(ConnStr);
try
{
SqlDataAdapter myAdapter=new SqlDataAdapter("select * from tbl_Excel",myConnection);
SqlCommandBuilder custCB = new SqlCommandBuilder(myAdapte
myAdapter.SelectCommand.Co
custCB.RefreshSchema(); // this is for adapter to write own insert command..or else you can write your own insert command...
myAdapter.Fill(ds);
foreach(DataRow dr1 in myDataSet.Tables[0].Rows)
{
DataRow dr=ds.Tables[0].NewRow();
dr[0]=dr1[0];
dr[1]=dr1[1];
dr[2]=dr1[2];
ds.Tables[0].Rows.Add(dr);
}
DataSet ds1=ds.GetChanges();
myAdapter.Update(ds1.Table
}
catch(SqlException excep)
{
throw excep;
}
finally
{
myConnection.Close();
}
#endregion
wil try to look at your code and see if i can convert to C#...in the mean while you can try it too..you can try with sample table and sample excel and see how it works...
Thank You.
Good Luck.
ASKER
Praneetha,
You have been really helpfull. You are really a GURU in ASP.Net.
Let me try your code and i will get back to you as soon as iam done with it.
Thank You.
Bye.
You have been really helpfull. You are really a GURU in ASP.Net.
Let me try your code and i will get back to you as soon as iam done with it.
Thank You.
Bye.
ASKER
Praneetha,
Could u please tell me what exactly is the tbl_Excel in the code statement below? Is is the table from SQL Server or the sheet from the excel file ?
What is the purpose of doing this ?
SqlDataAdapter myAdapter=new SqlDataAdapter("select * from tbl_Excel",myConnection);
Please reply soon.
Could u please tell me what exactly is the tbl_Excel in the code statement below? Is is the table from SQL Server or the sheet from the excel file ?
What is the purpose of doing this ?
SqlDataAdapter myAdapter=new SqlDataAdapter("select * from tbl_Excel",myConnection);
Please reply soon.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Praneetha,
With your valuble help, iam able to read data from an excel file and put it into a SQL Server table.
Since i have an auto-increment column called 'memberID' in my sql server table 'Members', iam not selecting that column in my select statement of the dataAdapter and therefore it works perfectly fine. Note that in my excel file i do not have a column called 'memberID' but do have the other 4 columns corresponding to my 'Members' table in sql server.
Now, please clarify some questions that i have regarding your code that i have used.
1) What is the purpose of using the SqlCommandBuilder and what role is it playing in our code ?
2) what does myCB.RefreshSchema has to do in our code ?
3) Lastly, performance-wise , which code is very efficient? the one that i have below OR the one at the top question of the forum-thread where iam calling the stored procedure many times within a for loop ?
Please let me know.
You have been indeed very helpfull for me in learning .Net.
Thank You.
Here is my current version of the code based on your code:
-------------------------- ---------- ---------- ---------- ---------- --
Private Sub Process_Records()
Dim i, j As Integer
Dim dt2 As New DataTable
' readExcelSheet is a user-defined function that returns a DataTable containing records from the excel spreadsheet
dt2 = readExcelSheet("C:\Inetpub \wwwroot\P roject1\Bo ok2.xls", "SELECT * FROM [Sheet1$]")
Dim myConnection As SqlConnection = Connection() 'user-defined function that returns a SqlConnection object
Dim ds As New DataSet
myConnection.Open()
Dim myAdapter As New SqlDataAdapter("SELECT salutation, first_name, last_name, company FROM Members", myConnection)
Dim myCB As New SqlCommandBuilder(myAdapte r)
Dim dr1 As DataRow
myAdapter.SelectCommand.Co mmandType = CommandType.Text
myCB.RefreshSchema()
myAdapter.Fill(ds, "Members")
i = dt2.Rows.Count()
For Each dr1 In dt2.Rows
Dim dr As DataRow = ds.Tables("Members").NewRo w()
dr(0) = dr1(0)
dr(1) = dr1(1)
dr(2) = dr1(2)
dr(3) = dr1(3)
ds.Tables("Members").Rows. Add(dr)
Next
Dim ds1 As New DataSet
ds1 = ds.GetChanges()
myAdapter.Update(ds1, "Members")
Response.Write("Number of records inserted" & i )
myConnection.Close()
End Sub
With your valuble help, iam able to read data from an excel file and put it into a SQL Server table.
Since i have an auto-increment column called 'memberID' in my sql server table 'Members', iam not selecting that column in my select statement of the dataAdapter and therefore it works perfectly fine. Note that in my excel file i do not have a column called 'memberID' but do have the other 4 columns corresponding to my 'Members' table in sql server.
Now, please clarify some questions that i have regarding your code that i have used.
1) What is the purpose of using the SqlCommandBuilder and what role is it playing in our code ?
2) what does myCB.RefreshSchema has to do in our code ?
3) Lastly, performance-wise , which code is very efficient? the one that i have below OR the one at the top question of the forum-thread where iam calling the stored procedure many times within a for loop ?
Please let me know.
You have been indeed very helpfull for me in learning .Net.
Thank You.
Here is my current version of the code based on your code:
--------------------------
Private Sub Process_Records()
Dim i, j As Integer
Dim dt2 As New DataTable
' readExcelSheet is a user-defined function that returns a DataTable containing records from the excel spreadsheet
dt2 = readExcelSheet("C:\Inetpub
Dim myConnection As SqlConnection = Connection() 'user-defined function that returns a SqlConnection object
Dim ds As New DataSet
myConnection.Open()
Dim myAdapter As New SqlDataAdapter("SELECT salutation, first_name, last_name, company FROM Members", myConnection)
Dim myCB As New SqlCommandBuilder(myAdapte
Dim dr1 As DataRow
myAdapter.SelectCommand.Co
myCB.RefreshSchema()
myAdapter.Fill(ds, "Members")
i = dt2.Rows.Count()
For Each dr1 In dt2.Rows
Dim dr As DataRow = ds.Tables("Members").NewRo
dr(0) = dr1(0)
dr(1) = dr1(1)
dr(2) = dr1(2)
dr(3) = dr1(3)
ds.Tables("Members").Rows.
Next
Dim ds1 As New DataSet
ds1 = ds.GetChanges()
myAdapter.Update(ds1, "Members")
Response.Write("Number of records inserted" & i )
myConnection.Close()
End Sub
hi dn_learner,
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconupdatingdatabasewithdataadapterdataset.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommondataadapterclassupdatetopic.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandbuilderclasstopic.asp
all these articles will answer your questions about dataadapter...i would be glad to help if you have more questions after that...
i am sure this way of doing is lot faster then hitting the database every row in a excel sheet....do u see any difference performance ways...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconupdatingdatabasewithdataadapterdataset.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommondataadapterclassupdatetopic.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandbuilderclasstopic.asp
all these articles will answer your questions about dataadapter...i would be glad to help if you have more questions after that...
i am sure this way of doing is lot faster then hitting the database every row in a excel sheet....do u see any difference performance ways...
ASKER
Hi,
Once again thank you so much for all your help. I really appreciate it.
Well, iam not that good at research but i did put Response.write(Date.Now()) statements before and after the block of code that inserted or updated the data from my excel file to sql server.
When i was using the calls to stored procedure from the for loop, it took 3 seconds to insert 3559 records from excel file to the sqlserver table.
When i used the DataAdapter update method, it took surprisingly 5 seconds to do the import process.
But iam sure that no matter it took 5 seconds to do the import, performance wise it is better since it consumed less system resources and less network traffic. Thats all i can conclude.. What do u have to say ??
Since iam new to EE forum, is there a way i can directly contact you thru this forum coz iam not sure if in the future, i post a question and you might not reply or may be away from computer ??? :-(
Please let me know.
Thanks.
Once again thank you so much for all your help. I really appreciate it.
Well, iam not that good at research but i did put Response.write(Date.Now())
When i was using the calls to stored procedure from the for loop, it took 3 seconds to insert 3559 records from excel file to the sqlserver table.
When i used the DataAdapter update method, it took surprisingly 5 seconds to do the import process.
But iam sure that no matter it took 5 seconds to do the import, performance wise it is better since it consumed less system resources and less network traffic. Thats all i can conclude.. What do u have to say ??
Since iam new to EE forum, is there a way i can directly contact you thru this forum coz iam not sure if in the future, i post a question and you might not reply or may be away from computer ??? :-(
Please let me know.
Thanks.
hi,
yeah i think making database trip once is always better...
but u still have to take care of concurrency though....(incase more then one person is trying to modify the data)
well i don't think you can directly write to me thru EE...i participate in EE alot so you can post your question here..that way you get other experts expertise too....
good luck.
yeah i think making database trip once is always better...
but u still have to take care of concurrency though....(incase more then one person is trying to modify the data)
well i don't think you can directly write to me thru EE...i participate in EE alot so you can post your question here..that way you get other experts expertise too....
good luck.
Hi,
Above discussion found very useful for importing data from excel into DB. But i would like to know some more details regarding this.
1. How to update existing DB data(>> already imported from excel), when trying to import data from updated excel file?
2. Is there any way to compare two dataset data(s) for the changes made?
Thanks in Advance for your time!
Pro
Above discussion found very useful for importing data from excel into DB. But i would like to know some more details regarding this.
1. How to update existing DB data(>> already imported from excel), when trying to import data from updated excel file?
2. Is there any way to compare two dataset data(s) for the changes made?
Thanks in Advance for your time!
Pro
ASKER
Hi,
For your number 2 question, there is a method which u can use like this:
myDataSet.HasChanges() and also
myDataSet.GetChanges() ..
Do you have any idea for question number 1 ?? I can think but dont know how to exactly implement it ?
Thank You.
For your number 2 question, there is a method which u can use like this:
myDataSet.HasChanges() and also
myDataSet.GetChanges() ..
Do you have any idea for question number 1 ?? I can think but dont know how to exactly implement it ?
Thank You.
Hi,
Methods you have mentioned are used to get a copy of the dataset containing all the changes made to it since it was last loaded(GETCHANGES) & HasChanges method of a dataset returns true if changes have been made in the dataset.
eg;
* NewDataSet = myDataSet.GetChanges(DataR owState.Mo dified)
* myDataSet.HasChanges(DataR owState.Mo dified)
But actually i want to compare two different datasets. As per above discussion, I am able to insert data into DB from excel. Its works fine. Problem arises while trying to update DB from same excel with some more additional data(s).
I thought of using two different datasets, one for existing data from DB and another one for retreiving data from the excel. Finally compare 2 datasets & update DB with only new changes made in Excel.
I dont know whether i am on the right path? Help me!
Thank You!
Methods you have mentioned are used to get a copy of the dataset containing all the changes made to it since it was last loaded(GETCHANGES) & HasChanges method of a dataset returns true if changes have been made in the dataset.
eg;
* NewDataSet = myDataSet.GetChanges(DataR
* myDataSet.HasChanges(DataR
But actually i want to compare two different datasets. As per above discussion, I am able to insert data into DB from excel. Its works fine. Problem arises while trying to update DB from same excel with some more additional data(s).
I thought of using two different datasets, one for existing data from DB and another one for retreiving data from the excel. Finally compare 2 datasets & update DB with only new changes made in Excel.
I dont know whether i am on the right path? Help me!
Thank You!
ASKER
I would suggest you to post a new thread on this forum...
Someone would definitely answer your concerns...
Let me know when you do it.
thanks
Someone would definitely answer your concerns...
Let me know when you do it.
thanks
you can create a dataset from excel and then use dataset to update or insert into sql...
see if that helps...