none
create table by code

    Question

  •  

    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

    Sunday, August 12, 2007 3:02 PM

Answers

  • 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
    --- 

    Monday, August 13, 2007 6:49 PM
    Moderator

All replies

  •  

    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
    ---

    Sunday, August 12, 2007 3:34 PM
    Moderator
  • 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

    Sunday, August 12, 2007 4:52 PM
    Moderator
  • 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()

    Monday, August 13, 2007 1:49 PM
  • 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
    Monday, August 13, 2007 2:18 PM
  • 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
    --- 

    Monday, August 13, 2007 6:49 PM
    Moderator
  •  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.

     

    Tuesday, August 14, 2007 2:08 PM