create table using ado.net

  • Thread starter Thread starter suzy
  • Start date Start date
S

suzy

Hello,

I understand it is possible to create a SQL server table (with primary keys,
default values, etc) using ado.net/c#.

Can someone point me in the right direction about how to do this please
(with a link to an example etc).

thanks.
 
Suzy:

Yes, you can. Here's the total code. Notice I commented out hte line where
I check for the existence of the table first (you should do the check or
risk throwing an exception, I was just being lazy.) Anyway, if you have a
valid connection string and permissions to create a table from the machine
you are on(if youare using SSPI or if you have a valid username and password
if you arne't using SSPI) then this will do it for you.... HTH,

Bill

Dim sb As New System.Text.StringBuilder

Dim KeepGoing As Boolean = False

Dim cn As New SqlConnection("YourConnectionString")

' sb.Append("if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Tbl_Invoice_Information_Tbl_Facilities]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)")

sb.Append("CREATE TABLE [dbo].[Tbl_FacilitiesTest] (")

sb.Append("[Facility] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,")

sb.Append("[Facility_Initials] [nvarchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,")

sb.Append("[Dept_Number] [int] NOT NULL ,")

sb.Append("[BillingType] [smallint] NOT NULL ,")

sb.Append("[Dictation_Code] [tinyint] NULL ")

sb.Append(") ON [PRIMARY]")

Dim cmd As New SqlCommand(sb.ToString, cn)

Try

If cn.State <> ConnectionState.Open Then cn.Open()

KeepGoing = True

Catch ex As SqlException

Debug.Assert(False, ex.ToString)

End Try

If Not KeepGoing Then

'Alert the user and ask them what they want to do, we cna't

'go any further until we have a good connection. This should

'be in a while loop but I'm lazy this morning..

Else

Try

Dim i As Integer = cmd.ExecuteNonQuery

Catch ex As SqlException

Debug.Assert(False, ex.ToString)

Finally

If cn.State <> ConnectionState.Closed Then cn.Close()

End Try

cn.Dispose()

cmd.Dispose()

End If
 
Hi, thanks for your help but both your examples use string concatenation to
execute a query.

Is it not possible to do it by creating a datatable, and adding fields to it
and then "submitting" this to the db so it creates it?

If the methods shown in your posts are more effecient then I will go down
that route.

PS: William, did you see me reply to the sql update question post yet?



William Ryan eMVP said:
Suzy:

Yes, you can. Here's the total code. Notice I commented out hte line where
I check for the existence of the table first (you should do the check or
risk throwing an exception, I was just being lazy.) Anyway, if you have a
valid connection string and permissions to create a table from the machine
you are on(if youare using SSPI or if you have a valid username and password
if you arne't using SSPI) then this will do it for you.... HTH,

Bill

Dim sb As New System.Text.StringBuilder

Dim KeepGoing As Boolean = False

Dim cn As New SqlConnection("YourConnectionString")

' sb.Append("if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Tbl_Invoice_Information_Tbl_Facilities]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)")

sb.Append("CREATE TABLE [dbo].[Tbl_FacilitiesTest] (")

sb.Append("[Facility] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,")

sb.Append("[Facility_Initials] [nvarchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,")

sb.Append("[Dept_Number] [int] NOT NULL ,")

sb.Append("[BillingType] [smallint] NOT NULL ,")

sb.Append("[Dictation_Code] [tinyint] NULL ")

sb.Append(") ON [PRIMARY]")

Dim cmd As New SqlCommand(sb.ToString, cn)

Try

If cn.State <> ConnectionState.Open Then cn.Open()

KeepGoing = True

Catch ex As SqlException

Debug.Assert(False, ex.ToString)

End Try

If Not KeepGoing Then

'Alert the user and ask them what they want to do, we cna't

'go any further until we have a good connection. This should

'be in a while loop but I'm lazy this morning..

Else

Try

Dim i As Integer = cmd.ExecuteNonQuery

Catch ex As SqlException

Debug.Assert(False, ex.ToString)

Finally

If cn.State <> ConnectionState.Closed Then cn.Close()

End Try

cn.Dispose()

cmd.Dispose()

End If

suzy said:
Hello,

I understand it is possible to create a SQL server table (with primary keys,
default values, etc) using ado.net/c#.

Can someone point me in the right direction about how to do this please
(with a link to an example etc).

thanks.
Click to expand...
Click to expand...
 
Suzy:

If the table isn't already in existence in a Database then you are going to
have to create it. You could use a DataTable to build a Create Table Script
with, but if it doesn't exist, a DataTable object isn't going ot make it for
you. DataTable columns have types that ultimately map back to
SYstem.Types...however, what would you make a string? Varchar/Varchar2 etc?
It would take some guess work or some specificying and DataTables come from
plain old System.Data which doesn't know about RDBMS specific
implementations.

For the record though, you could loop through your DataTable and build a
script from there to fire against your DB,doing this dynamically isn't all
that complicated...

I'll check your other question now. I just got a few minutes to get caught
up.

HTH,

Bill
suzy said:
Hi, thanks for your help but both your examples use string concatenation to
execute a query.

Is it not possible to do it by creating a datatable, and adding fields to it
and then "submitting" this to the db so it creates it?

If the methods shown in your posts are more effecient then I will go down
that route.

PS: William, did you see me reply to the sql update question post yet?



William Ryan eMVP said:
Suzy:

Yes, you can. Here's the total code. Notice I commented out hte line where
I check for the existence of the table first (you should do the check or
risk throwing an exception, I was just being lazy.) Anyway, if you have a
valid connection string and permissions to create a table from the machine
you are on(if youare using SSPI or if you have a valid username and password
if you arne't using SSPI) then this will do it for you.... HTH,

Bill

Dim sb As New System.Text.StringBuilder

Dim KeepGoing As Boolean = False

Dim cn As New SqlConnection("YourConnectionString")

' sb.Append("if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Tbl_Invoice_Information_Tbl_Facilities]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)")

sb.Append("CREATE TABLE [dbo].[Tbl_FacilitiesTest] (")

sb.Append("[Facility] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,")

sb.Append("[Facility_Initials] [nvarchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,")

sb.Append("[Dept_Number] [int] NOT NULL ,")

sb.Append("[BillingType] [smallint] NOT NULL ,")

sb.Append("[Dictation_Code] [tinyint] NULL ")

sb.Append(") ON [PRIMARY]")

Dim cmd As New SqlCommand(sb.ToString, cn)

Try

If cn.State <> ConnectionState.Open Then cn.Open()

KeepGoing = True

Catch ex As SqlException

Debug.Assert(False, ex.ToString)

End Try

If Not KeepGoing Then

'Alert the user and ask them what they want to do, we cna't

'go any further until we have a good connection. This should

'be in a while loop but I'm lazy this morning..

Else

Try

Dim i As Integer = cmd.ExecuteNonQuery

Catch ex As SqlException

Debug.Assert(False, ex.ToString)

Finally

If cn.State <> ConnectionState.Closed Then cn.Close()

End Try

cn.Dispose()

cmd.Dispose()

End If

suzy said:
Hello,

I understand it is possible to create a SQL server table (with primary keys,
default values, etc) using ado.net/c#.

Can someone point me in the right direction about how to do this please
(with a link to an example etc).

thanks.
Click to expand...
Click to expand...
Click to expand...
 
Suzy, check out my article on this subject..

http://www.knowdotnet.com/articles/ddl.html
I'm also almost finished with one on creatinga DB Table from a
DataTable..check the site later tonight, I should have it up around dinner
time.


suzy said:
Hi, thanks for your help but both your examples use string concatenation to
execute a query.

Is it not possible to do it by creating a datatable, and adding fields to it
and then "submitting" this to the db so it creates it?

If the methods shown in your posts are more effecient then I will go down
that route.

PS: William, did you see me reply to the sql update question post yet?



William Ryan eMVP said:
Suzy:

Yes, you can. Here's the total code. Notice I commented out hte line where
I check for the existence of the table first (you should do the check or
risk throwing an exception, I was just being lazy.) Anyway, if you have a
valid connection string and permissions to create a table from the machine
you are on(if youare using SSPI or if you have a valid username and password
if you arne't using SSPI) then this will do it for you.... HTH,

Bill

Dim sb As New System.Text.StringBuilder

Dim KeepGoing As Boolean = False

Dim cn As New SqlConnection("YourConnectionString")

' sb.Append("if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Tbl_Invoice_Information_Tbl_Facilities]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)")

sb.Append("CREATE TABLE [dbo].[Tbl_FacilitiesTest] (")

sb.Append("[Facility] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,")

sb.Append("[Facility_Initials] [nvarchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,")

sb.Append("[Dept_Number] [int] NOT NULL ,")

sb.Append("[BillingType] [smallint] NOT NULL ,")

sb.Append("[Dictation_Code] [tinyint] NULL ")

sb.Append(") ON [PRIMARY]")

Dim cmd As New SqlCommand(sb.ToString, cn)

Try

If cn.State <> ConnectionState.Open Then cn.Open()

KeepGoing = True

Catch ex As SqlException

Debug.Assert(False, ex.ToString)

End Try

If Not KeepGoing Then

'Alert the user and ask them what they want to do, we cna't

'go any further until we have a good connection. This should

'be in a while loop but I'm lazy this morning..

Else

Try

Dim i As Integer = cmd.ExecuteNonQuery

Catch ex As SqlException

Debug.Assert(False, ex.ToString)

Finally

If cn.State <> ConnectionState.Closed Then cn.Close()

End Try

cn.Dispose()

cmd.Dispose()

End If

suzy said:
Hello,

I understand it is possible to create a SQL server table (with primary keys,
default values, etc) using ado.net/c#.

Can someone point me in the right direction about how to do this please
(with a link to an example etc).

thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top