create table by code

Hi

I developed a program. This program will use a SQL server database. How can I create the database using code. I think I need to use sql scipt like this:

create database customers

But where should I write the script and How can I make VB code implement the script.

thank

Open a connection to the server using a SQLCommand and issue the statements for creating a table / database. The appropiate syntax for creating the script can be seen in the SQL Servers BOL.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

The title says you want to create a Table, but your question is asking about creating a database, no sure which you want since they are different, but...

There are always multiple ways to do stuff, so you might want to explore a couple different ways. In code, the two that come to mind are using a SqlConnection object to run your script. The script would be run using the SqlCommand object. Both of these are part of the System.Data.SqlClient namespace which offers many tools for connecting to and running commands in SQL Server. (Note: Depending on the complexity of your script, you may need to parse it into multiple commands. For example, the SqlCommand object can only take a single batch (i.e. the stuff separated by GO when you use the query window, so you have to run those separately.)

Another option for work with database in code is SQL Management Objects or SMO. Rather than using T-SQL scripts as you would do with System.Data.SqlClient, SMO exposes the objects in SQL Server and lets you manipulate them like any other programatic object. You can find examples of working with database in SMO here, and working with Tables in SMO here.

The is no one right way, use the method that makes the most sense for you.

Mike

|||

Thank you very much.

I used the following code. It worked in the first time. But when I debugged the program for the second time, I had this error " database customs already exists"

How can I solve this problem?

And can you give me a sql script to create one database with one three-column table?

thanks

Dim cnString As String = "Data Source=.\sqlexpress;Integrated Security=True;Pooling=False"

Dim con As SqlClient.SqlConnection = New SqlClient.SqlConnection(cnString)

Dim cmdAs SqlClient.SqlCommand = New SqlClient.SqlCommand()

con.Open()

cmd.CommandType = CommandType.Text

cmd.Connection = conGet

cmd.CommandText = "create database [customs]"

cmd.ExecuteNonQuery()

con.Close()

|||Once the database is created, you need not attempt to create it again.

To create a table, look into the CREATE TABLE command...

http://msdn2.microsoft.com/en-us/library/aa258255(SQL.80).aspx

Further, you might want to look into using Sql Server Management Studio (or the Express version) to run TSQL scripts.

http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en
|||

In addition you can check the existance before actually creating the table using some query like following:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'SomeTableName'

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

In addition you can check the existance before actually creating the table using some query like following:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'SomeTableName'

Jens K. Suessmeyer

http://www.sqlserver2005.de

It should be even simpler to use the OBJECT_ID('SomeTableName') and check it against NULL. Though this is not restricted to tables only, ie

SELECT OBJECT_ID('SomeTableName')

could also match any other object.