Save System.Data.DataTable to new SQL Table

  • Thread starter Thread starter Angus Logan [MVP]
  • Start date Start date
A

Angus Logan [MVP]

Hi,

Has anyone got any pre-written code which will allow for a DataTable to be
saved to a new table in SQL.

I would imagine it would go something like:
1) populate the datatable
2) does the table exist in SQL
3) if not create the table as per the data table
4) save teh contents of the datatable to SQL

Regards
--
________________________________________

Angus Logan (MCAD/MCDBA/MCP/MVP for MCMS)
Product Specialist
Microsoft Application Solutions
Data#3 Limited
E (e-mail address removed)
BLOG www.anguslogan.com
________________________________________
 
Angus Logan said:
Hi,

Has anyone got any pre-written code which will allow for a DataTable to be
saved to a new table in SQL.
Click to expand...

DTS would handle this (only if your RDBMS is MS/SQl Server)
I would imagine it would go something like:
1) populate the datatable
Click to expand...


One safe method is to use stored procedure and loop thru the DataTabe to
populate each record.
2) does the table exist in SQL
3) if not create the table as per the data table
Click to expand...

your "sproc" can do this before inserting data: Example:

if not exists(select * from dbo.sysobjects where
id=object_id(N'[dbo].[Dentists]')
and OBJECTPROPERTY(id,N'IsUserTable')=1)
CREATE TABLE [dbo].[Dentists](...fields here...)

*** The reason i consider this is "safe" because you only grant execute
privilege on the "sproc" to whomever running the app.. Not the table itself.

John
 
Angus Logan said:
Hi,

Has anyone got any pre-written code which will allow for a DataTable to be
saved to a new table in SQL.

I would imagine it would go something like:
1) populate the datatable
2) does the table exist in SQL
3) if not create the table as per the data table
4) save teh contents of the datatable to SQL

Regards
Click to expand...
Hi Angus:

Other than creating the table if it doesn't exist, this approach will work
and the good part is that it's really easy to do
http://www.knowdotnet.com/articles/datasetmerge.html.

You can simply look through the schema of the table before calling Update ,
construct your ddl statement and then fire it against the db, then call
Update. This could probably be done in under 12 lines of code total - 20
tops. I think I have a routine too that will create the Sql Statement w/
Sql Server - let me look through my stuff when I get home and I'll post the
code for that (although it's really simple and basically just a loop and a
strinbuilder with an ExecuteNonQuery command.
 
Cool Thanks!
--
________________________________________

Angus Logan (MCAD/MCDBA/MCP/MVP for MCMS)
Product Specialist
Microsoft Application Solutions
Data#3 Limited
E (e-mail address removed)
BLOG www.anguslogan.com
________________________________________
 
Cheers!
--
________________________________________

Angus Logan (MCAD/MCDBA/MCP/MVP for MCMS)
Product Specialist
Microsoft Application Solutions
Data#3 Limited
E (e-mail address removed)
BLOG www.anguslogan.com
________________________________________
WJ said:
Angus Logan said:
Hi,

Has anyone got any pre-written code which will allow for a DataTable to
be saved to a new table in SQL.
Click to expand...

DTS would handle this (only if your RDBMS is MS/SQl Server)
I would imagine it would go something like:
1) populate the datatable
Click to expand...


One safe method is to use stored procedure and loop thru the DataTabe to
populate each record.
2) does the table exist in SQL
3) if not create the table as per the data table
Click to expand...

your "sproc" can do this before inserting data: Example:

if not exists(select * from dbo.sysobjects where
id=object_id(N'[dbo].[Dentists]')
and OBJECTPROPERTY(id,N'IsUserTable')=1)
CREATE TABLE [dbo].[Dentists](...fields here...)

*** The reason i consider this is "safe" because you only grant execute
privilege on the "sproc" to whomever running the app.. Not the table
itself.

John
Click to expand...
 
Angus,

I have removed a table from the server, from which I have the xsd still and
an xmldataset, so I could do two thinks at once.

Create a sample and use it myself

:-)

This is about step 3 and 4 for what I saw never a complete sample, if you
want step 1 and 2 as well, than look at our site, in a lot of the samples
there is a dataset and/or a dataset created.

http://www.windowsformsdatagridhelp.com/default.aspx

The sample bellow is just at the beginning (but tested and working), however
in my opinion for a starting sample sufficient.

\\\
Private Sub ConvertFromStronglyTyped(ByVal ds As DataSet, _
ByVal ServerTableName As String, ByVal dsTableName As String)
Dim SQLString As New System.Text.StringBuilder
SQLString.Append("CREATE TABLE " & ServerTableName & " (")
For Each itm As DataColumn In ds.Tables(dsTableName).Columns
SQLString.Append(" " & itm.ColumnName & " ")
Dim mytype As String = itm.DataType.ToString
Select Case mytype.ToLower
Case "system.string"
SQLString.Append(" nvarchar (50), ")
Case "system.boolean"
SQLString.Append(" bit, ")
End Select
Next
Dim primkey0 As String = DirectCast(ds.Tables _
(dsTableName).PrimaryKey, DataColumn())(0).ColumnName
SQLString.Append("CONSTRAINT [ pk_" & primkey0 & _
"] PRIMARY KEY CLUSTERED (" & primkey0 & "))")
Dim strSQL As String = SQLString.ToString
Dim Conn As New SqlConnection _
("Server=localhost;DataBase=GEN;Integrated Security=SSPI")
Try
Conn.Open()
Try
Dim cmd As New SqlCommand(strSQL, Conn)
cmd.ExecuteNonQuery()
Catch Ex As SqlException
MessageBox.Show(Ex.Message)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
Conn.Close()
End Try
End Sub
////

I hope this helps,

Cor
 
Great!

Thanks!

--
________________________________________

Angus Logan (MCAD/MCDBA/MCP/MVP for MCMS)
Product Specialist
Microsoft Application Solutions
Data#3 Limited
E (e-mail address removed)
BLOG www.anguslogan.com
________________________________________
 
Back
Top