How can a datatable be written to a new SQL Server table?

[Rod]

I am struggling with ado.net within an asp.net project.

I want to import an Excel spreadsheet and write it to a temporary table on a
SQL Server from where it can be manipulated.

I can import the Excel file to a dataset successfully but cannot move the
data to the SQL Server.

My attempts have been focussed round:
creating a SQL Server table based on the structure of the
dataset.datatable, (this has been successful),
using a dataadapter to create a new dataset based on the newly created
SQL Server table, (this has been apparently successful),
merging the new dataset with the one imported from Excel, (this has
been successful),
using dataadapter.Update(dataset) to write the data back to the SQL
Server. (This is a failure.)

I have tried to use the command builder to construct the insert command but
at the point at which the update should occur the insert sqlcommand is
nothing.

The code I use to create the SQL Server table and attempt to write the data
are below.

Any help would be greatly appreciated, including (especially) alternatives
to my idea.

Thanks in anticipation.

Rod Scoullar.


** Create Table Function
** The function accepts a DataTable object and the name of the SQL Server
table to be created
** It returns True if the table was successfully created

Private Function CreateSQLServerTable(ByVal tbl As System.Data.DataTable,
ByVal strTableName As String) As Boolean
Dim blnRVal As Boolean = False

' Drops table if it already exists

Try
Dim strSQL As String = "DROP TABLE " & strTableName
conn.Open() ' The connection is declared elsewhere
Dim cmd As System.Data.SqlClient.SqlCommand = conn.CreateCommand()
cmd.CommandText = strSQL
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
Catch e As Exception
System.Diagnostics.Debug.WriteLine(e.ToString())
Finally
conn.Close()
End Try

' Builds CREATE TABLE statement

Try
Dim strSQL As String = "CREATE TABLE " & strTableName & " (ID integer
IDENTITY (1,1) PRIMARY KEY"
Dim strFlds As String = ""
Dim strType As String
Dim fld As System.Data.DataColumn
tbl = dsSSData.Tables("Table")
For Each fld In tbl.Columns
Select Case fld.DataType.Name
Case "String"
Dim intFldLen As Integer = 1
Dim row As System.Data.DataRow
For Each row In tbl.Rows
If Not IsDBNull(row(fld)) Then
If row(fld).length() > intFldLen Then intFldLen = row(fld).length()
End If
Next
strType = "varchar(" & CStr(intFldLen) & ")"
Case "Double"
strType = "float"
Case Else
strType = fld.DataType.Name
End Select
strFlds &= ", [" & fld.ColumnName & "] " & strType & " NULL"
Next
strSQL &= strFlds & ")"

' Creates table

conn.Open()
Dim cmd As System.Data.SqlClient.SqlCommand = conn.CreateCommand()
cmd.CommandText = strSQL
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
blnRVal = True

Catch e As Exception
System.Diagnostics.Debug.WriteLine(e.ToString())
Finally
conn.Close()
End Try

Return (blnRVal)
End Function

** Fill Table Function
** The function accepts a DataTable object and the name of the SQL Server
table to be filled
Private Function FillSQLServerTable(ByVal tbl As System.Data.DataTable,
ByVal strTableName As String) As Boolean
Dim blnRVal As Boolean = False
Try
conn.Open()
Dim strSQL As String = "SELECT * FROM " & strTableName
Dim ds As New System.Data.DataSet
Dim da As New System.Data.SqlClient.SqlDataAdapter
da.MissingSchemaAction = MissingSchemaAction.AddWithKey
da.SelectCommand = New System.Data.SqlClient.SqlCommand(strSQL, conn)
Dim cb As System.Data.SqlClient.SqlCommandBuilder = New
System.Data.SqlClient.SqlCommandBuilder(da)
da.Fill(ds)
ds.Merge(tbl)
ds.AcceptChanges()
da.Update(ds)
blnRVal = True
Catch ex As Exception
System.Diagnostics.Debug.WriteLine(ex.ToString())
Finally
conn.Close()
End Try
Return(blnRVal)
End Function

This function returns True even though no data has been written.

 

[William \(Bill\) Vaughn]

I would try to use BCP or DTS for this. You might be able to do this in TSQL
using the BCP operator or in SQL DMO.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

I am struggling with ado.net within an asp.net project.

I want to import an Excel spreadsheet and write it to a temporary table on
a SQL Server from where it can be manipulated.

I can import the Excel file to a dataset successfully but cannot move the
data to the SQL Server.

My attempts have been focussed round:
creating a SQL Server table based on the structure of the
dataset.datatable, (this has been successful),
using a dataadapter to create a new dataset based on the newly
created SQL Server table, (this has been apparently successful),
merging the new dataset with the one imported from Excel, (this has
been successful),
using dataadapter.Update(dataset) to write the data back to the SQL
Server. (This is a failure.)

I have tried to use the command builder to construct the insert command
but at the point at which the update should occur the insert sqlcommand is
nothing.

The code I use to create the SQL Server table and attempt to write the
data are below.

Any help would be greatly appreciated, including (especially) alternatives
to my idea.

Thanks in anticipation.

Rod Scoullar.


** Create Table Function
** The function accepts a DataTable object and the name of the SQL Server
table to be created
** It returns True if the table was successfully created

Private Function CreateSQLServerTable(ByVal tbl As System.Data.DataTable,
ByVal strTableName As String) As Boolean
Dim blnRVal As Boolean = False

' Drops table if it already exists

Try
Dim strSQL As String = "DROP TABLE " & strTableName
conn.Open() ' The connection is declared elsewhere
Dim cmd As System.Data.SqlClient.SqlCommand = conn.CreateCommand()
cmd.CommandText = strSQL
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
Catch e As Exception
System.Diagnostics.Debug.WriteLine(e.ToString())
Finally
conn.Close()
End Try

' Builds CREATE TABLE statement

Try
Dim strSQL As String = "CREATE TABLE " & strTableName & " (ID integer
IDENTITY (1,1) PRIMARY KEY"
Dim strFlds As String = ""
Dim strType As String
Dim fld As System.Data.DataColumn
tbl = dsSSData.Tables("Table")
For Each fld In tbl.Columns
Select Case fld.DataType.Name
Case "String"
Dim intFldLen As Integer = 1
Dim row As System.Data.DataRow
For Each row In tbl.Rows
If Not IsDBNull(row(fld)) Then
If row(fld).length() > intFldLen Then intFldLen = row(fld).length()
End If
Next
strType = "varchar(" & CStr(intFldLen) & ")"
Case "Double"
strType = "float"
Case Else
strType = fld.DataType.Name
End Select
strFlds &= ", [" & fld.ColumnName & "] " & strType & " NULL"
Next
strSQL &= strFlds & ")"

' Creates table

conn.Open()
Dim cmd As System.Data.SqlClient.SqlCommand = conn.CreateCommand()
cmd.CommandText = strSQL
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
blnRVal = True

Catch e As Exception
System.Diagnostics.Debug.WriteLine(e.ToString())
Finally
conn.Close()
End Try

Return (blnRVal)
End Function

** Fill Table Function
** The function accepts a DataTable object and the name of the SQL Server
table to be filled
Private Function FillSQLServerTable(ByVal tbl As System.Data.DataTable,
ByVal strTableName As String) As Boolean
Dim blnRVal As Boolean = False
Try
conn.Open()
Dim strSQL As String = "SELECT * FROM " & strTableName
Dim ds As New System.Data.DataSet
Dim da As New System.Data.SqlClient.SqlDataAdapter
da.MissingSchemaAction = MissingSchemaAction.AddWithKey
da.SelectCommand = New System.Data.SqlClient.SqlCommand(strSQL, conn)
Dim cb As System.Data.SqlClient.SqlCommandBuilder = New
System.Data.SqlClient.SqlCommandBuilder(da)
da.Fill(ds)
ds.Merge(tbl)
ds.AcceptChanges()
da.Update(ds)
blnRVal = True
Catch ex As Exception
System.Diagnostics.Debug.WriteLine(ex.ToString())
Finally
conn.Close()
End Try
Return(blnRVal)
End Function

This function returns True even though no data has been written.

 

[Rod]

Bill,

Thanks for the response. I will try using DTS.

However it would have been nice to write a dataset to SQL Server. I could
always write it one row at a time.

I wondered whether I couldn't use the commandbuilder because I created the
table with an identity column. I read your article on identity fields and
datasets and in that you constructed the commands yourself rather than
having them built for you.

Thanks again, Rod.

 

[Steven Cheng[MSFT]]

Hi Rod,

In addition to Bill's suggestion, here are some of my understandings on the
problem you mentioned:

As you said
==========
using dataadapter.Update(dataset) to write the data back to the SQL
Server. (This is a failure.)
==========

Does the failure means the adapter.Update didn't insert any records from
the DataSet into backend database , nor did it through any exception , yes?

If so, I think the problem is likely that the DataRows in the Merged
datatable are all under UnChanged mode( which means needn't to update
/insert/ delete them when updating the DataSet. So have you try looping all
the DataRows in the DataTable before calling adapter.Update to see whether
it was the problem?
If there're any other findings, please feel free to post here.

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Rod]

Steven,

Your interpretation of 'failure' is correct.

I have just done as you suggested and checked the rowstate just before
calling update and the rowstate is indeed Unchanged.

I assumed that filling the dataset via the dataadapter.fill(dataset) from an
empty SQL Server table, and then merging the dataset containing the data
with (into) it would mark the merged records as Added because they were new
in the dataset read from the SQL Server table.

I can't see any way to change the rowstate programatically so I presume this
means that I have to add the rows one by one to the dataset.datatable.
Pity, the merge option would have been more efficient.

Thanks for you help. I spent many hours trying to discover why it wouldn't
work. At least I increased my understanding of dataadapters. I read every
word of the help files several times over.

Rod.

 

[Kevin Yu [MSFT]]

Hi Rod,

The RowState has been changed to Unchanged because in your code,
ds.AcceptChanges() is called before update. We needn't call AcceptChanges
method because da.Update will call it for us.

Also, if you are merging your Excel data to a newly created table in SQL
Server, you can try the following steps.

1. Fill the DataSet from Excel. Before filling set the DataAdapter's
AcceptChangesDuringFill property to false, so that all the rows' RowState
property are set to Added.
2. Update the SQL Server table directly from the DataSet using another
DataAdapter without merging with an empty Dataset.

You can also use DTS which is much more simpler and reliable.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Rod]

Thank you again.

I had taken the ds.AcceptChanges() out and the rowstate was still Unchanged.
Your suggestion about setting the AcceptChangesDuringFill to false should
solve that issue.

I'll try to use you other suggestions as well. I don't want to use DTS
because I was hoping to construct a general function that would write a
datatable to a new SQL Server table regardless of the source of the
datatable

I think you have given me enough clues to get my code working.

Much appreciated.

Rod.

 

[Kevin Yu [MSFT]]

You're welcome, Rod.

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."