How To Create a SQL Server Database Programmatically by Using ADO.NET and Visual C# .NET

Posted: September 26, 2011 in Aspx.NET Posts, Java Posts, JavaScript Posts, Random Posts
Tags:

This step-by-step article shows you how to create a Microsoft SQL Server database by using ADO.NET and Visual C# .NET because programmers often need to create Databases programmatically.

Steps to Create a SQL Server Database

To create the database, follow these steps:

  1. Create a new Visual C# .NET Windows application.
  2. Place a button on Form1. Change the button’s Name property to btnCreateDatabase, and then change the Text property to Create Database.
  3. Use the using statement on the System and System.Data namespaces so that you do not have to qualify declarations in those namespaces later in your code. Add the following code to the General Declarationssection of Form1:

    using System;
    using System.Data.SqlClient;
  4. Switch to Form view, and then double-click Create Database to add the clickevent handler. Add the following sample code to the handler:

        String str;
        SqlConnection myConn = new SqlConnection ("Server=localhost;Integrated security=SSPI;database=master");
    
        str = "CREATE DATABASE MyDatabase ON PRIMARY " +
            "(NAME = MyDatabase_Data, " +
            "FILENAME = 'C:\\MyDatabaseData.mdf', " +
            "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
            "LOG ON (NAME = MyDatabase_Log, " +
            "FILENAME = 'C:\\MyDatabaseLog.ldf', " +
            "SIZE = 1MB, " +
            "MAXSIZE = 5MB, " +
            "FILEGROWTH = 10%)";
    
        SqlCommand myCommand = new SqlCommand(str, myConn);
        try 
        {
            myConn.Open();
    	myCommand.ExecuteNonQuery();
    	MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        catch (System.Exception ex)
        {
    	MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        finally
        {
    	if (myConn.State == ConnectionState.Open)
    	{
    	    myConn.Close();
    	}
        }
  5. Change the connection string to point to your computer running SQL Server, and then verify that the Database argument is set to Master or is blank.
  6. Press F5 or CTRL+F5 to run the project, and then click Create Database.
  7. Use the Server Explorer to verify that the database is created.

NOTES:

  • This code creates a custom database with specific properties.
  • The folder that is going to hold the created .mdf and .ldf files must already exist before you run the code or an exception will be generated.
  • If you want to create a database that is similar to the SQL Server Model database, and you want the database in the default location, then change the strvariable in the code, as in the following sample code:

    str = "CREATE DATABASE MyDatabase"
Advertisements
Comments
  1. Desi says:

    How can you create the database using textboxes rather than hard-coding.

  2. simply
    where i put hard coded data
    u get value of ur text box
    like textbox name or id is MyDatabasetxtbox and MyDatabase_Datatxtbox
    and above code become like that …..
    str = “CREATE DATABASE “+ MyDatabasetxtbox.Text +”ON PRIMARY ” +
    “(NAME =”+ MyDatabase_Datatxtbox.Text +,” ………..
    and so on ………

  3. Desi says:

    im getting errors and step through the code to get an exception. It says incorrect syntax near “=” i viewed the entire code and it seem to be correct and moved the equal sign to eliminate the error but still get it. Is it possible if you can check this code to see where i went wrong?
    I appreciate for your assistance. Thanks.

    ********************************************************************************************************
    CODE:
    string str;
    SqlConnection connection = new SqlConnection(“Data Source=.\\SqlExpress; Initial Catalog=master;Integrated Security=true”);
    str = “Create Database ” + txtDatabaseName.Text + ” ON PRIMARY”
    + “(NAME =” + txtDatabaseName.Text
    + “FILENAME = ‘C:\\MYDB.mdf’,”
    + “SIZE = 2MB, MAXSIZE= 10MB, FILEGROWTH= 10%)”
    + “LOG ON = (NAME = MYDB_Log,”
    + “FILENAME = MYDB.ldf,”
    + “SIZE = 1MB,”
    + “MAXSIZE = 5MB,”
    + “FILEGROWTH = 10%)”;
    SqlCommand command = new SqlCommand(str, connection);
    try
    {
    connection.Open();
    command.ExecuteNonQuery();
    MessageBox.Show(“DataBase is Created Successfully”, “Database”, MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    catch (System.Exception ex)
    {
    MessageBox.Show(ex.ToString(), “Database”, MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    finally
    {
    if (connection.State == ConnectionState.Open)
    {
    connection.Close();
    }

    }
    }
    }

    END CODE

    Is it because this part of the code is missing?
    + “(NAME =” + txtDatabaseName.Text
    txtDatabaseName.Text should go inside the parenthesis after the equal sign of NAME?

  4. Eva Angelina says:

    I like this post, enjoyed this one appreciate it for putting up.

  5. blackjack 2 case w/ skulls says:

    I really like your writing style, great information, appreciate it for putting up :D. “I hate mankind, for I think myself one of the best of them, and I know how bad I am.” by Joseph Baretti.

  6. hybrid car rental says:

    I consider something truly special in this site.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s