C# Create a Database Programmatically

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 11786 Views - Last Post: 04 March 2012 - 08:03 AM Rate Topic: -----

#1 bravo659  Icon User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 181
  • Joined: 18-May 08

C# Create a Database Programmatically

Posted 02 March 2012 - 06:28 AM

Hi, I already know how to create a database from the Sql Management Studio and know how to create tables, update and remove information from a database via windows form application. But i am having trouble how to create a database.

In creating a database I have a form one button named: Create Database.
I also have a textbox user enters a database name, named: txtDatabaseName.Text.

Syntax:

String createDatabase;
            SqlConnection myConn = new SqlConnection("Server=localhost;Integrated security=SSPI;database=master");

 createDatabase = "CREATE DATABASE" + txtDatabase.Text + "ON PRIMARY " +
                "(NAME =" + txtDatabase.Text +
                "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%)";




In the form a user enters a database name into the textbox and click on the create database button which will create a database with a messagebox advising the user the database was successfully created.

Entire Code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace MSDN_Create_Database
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnCreateDatabase_Click(object sender, EventArgs e)
        {
            String CreateDatabase;
            SqlConnection connection = new SqlConnection("Server=localhost;Integrated security=SSPI;database=master");

            CreateDatabase = "CREATE DATABASE" + txtDatabase.Text + "ON PRIMARY " +
                "(NAME =" + txtDatabase.Text +
                "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 command = new SqlCommand(CreateDatabase, 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();
                }
            }

        }
    }
}

I don't get errors but the program freezes.
Any help on this is well appreciated.
Thanks.


This post has been edited by bravo659: 02 March 2012 - 06:32 AM


Is This A Good Question/Topic? 0
  • +

Replies To: C# Create a Database Programmatically

#2 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Re: C# Create a Database Programmatically

Posted 02 March 2012 - 06:51 AM

So basically, you haven't tried to debug this yourself? Try putting a breakpoint on line 35, and check the value of CreateDatabase. You should see the issue almost immediately.
Was This Post Helpful? 0
  • +
  • -

#3 bravo659  Icon User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 181
  • Joined: 18-May 08

Re: C# Create a Database Programmatically

Posted 02 March 2012 - 07:04 AM

View Posteclipsed4utoo, on 02 March 2012 - 08:51 AM, said:

So basically, you haven't tried to debug this yourself? Try putting a breakpoint on line 35, and check the value of CreateDatabase. You should see the issue almost immediately.


My error is incorrect syntax near "="

my error is here: CreateDatabase = "CREATE DATABASE " + txtDatabase.Text + " ON PRIMARY " +
"(NAME = " + txtDatabase.Text +

how can i generate the textbox entry to reflect the name of the database highlighted in yellow?

This post has been edited by eclipsed4utoo: 02 March 2012 - 07:15 AM

Was This Post Helpful? 0
  • +
  • -

#4 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Re: C# Create a Database Programmatically

Posted 02 March 2012 - 07:17 AM

So did you actually debug and look at the value of CreateDatabase? Because you still have another issue. Just by eye-balling the text, it looks like you have 2 issues with your original code. You fixed one(putting spaces between "CREATE DATABASE", the database name, and "ON"). Now you need to find the other one.
Was This Post Helpful? 0
  • +
  • -

#5 bravo659  Icon User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 181
  • Joined: 18-May 08

Re: C# Create a Database Programmatically

Posted 02 March 2012 - 07:28 AM

View Posteclipsed4utoo, on 02 March 2012 - 09:17 AM, said:

So did you actually debug and look at the value of CreateDatabase? Because you still have another issue. Just by eye-balling the text, it looks like you have 2 issues with your original code. You fixed one(putting spaces between "CREATE DATABASE", the database name, and "ON"). Now you need to find the other one.


Ok got it.
Now what im looking at is the FILENAME is the creation of the .mdf file that must exist before using.
I actually forgot how to create this .mdf file.
I thought by creating the database was sufficient to create the .mdf, and .ldf files automatically?
Was This Post Helpful? 0
  • +
  • -

#6 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Re: C# Create a Database Programmatically

Posted 02 March 2012 - 07:31 AM

I noticed you are trying to write the files directly to the C: drive. If you are running Windows 7, this will most likely not work, as most user accounts can't generate files to that part of the hard drive. What I normally do is create a directory off of the C: drive...like "Databases"(C:\Databases). Then I create my databases there.

This would be on my development machine. If you are wanting to do this for a released version of software, you would probably want to do this in the user profile section(Local Data or Roaming Data), as the user will always have write access to those directories.
Was This Post Helpful? 1
  • +
  • -

#7 bravo659  Icon User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 181
  • Joined: 18-May 08

Re: C# Create a Database Programmatically

Posted 02 March 2012 - 07:36 AM

View Posteclipsed4utoo, on 02 March 2012 - 09:31 AM, said:

I noticed you are trying to write the files directly to the C: drive. If you are running Windows 7, this will most likely not work, as most user accounts can't generate files to that part of the hard drive. What I normally do is create a directory off of the C: drive...like "Databases"(C:\Databases). Then I create my databases there.

This would be on my development machine. If you are wanting to do this for a released version of software, you would probably want to do this in the user profile section(Local Data or Roaming Data), as the user will always have write access to those directories.


I'm running Windows 7.
I was thinking of that to create a folder so then the syntax are correct and will write to the folder section?
so instead of writing MyDatabase.mdf i just point to an existing folder 'C:\\Databases'?
Was This Post Helpful? 0
  • +
  • -

#8 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Re: C# Create a Database Programmatically

Posted 02 March 2012 - 07:39 AM

If you create the directory, you should be able to change this..

FILENAME = 'C:\\MyDatabaseData.mdf'



to this...

FILENAME = 'C:\\Databases\\MyDatabaseData.mdf'



And it should create the database file. You would do the same for the log file also.
Was This Post Helpful? 0
  • +
  • -

#9 bravo659  Icon User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 181
  • Joined: 18-May 08

Re: C# Create a Database Programmatically

Posted 02 March 2012 - 07:54 AM

View Posteclipsed4utoo, on 02 March 2012 - 09:39 AM, said:

If you create the directory, you should be able to change this..

FILENAME = 'C:\\MyDatabaseData.mdf'



to this...

FILENAME = 'C:\\Databases\\MyDatabaseData.mdf'



And it should create the database file. You would do the same for the log file also.


I still get this error Invalid usage option FILENAME

I made the changes but still get the above error
The name of the database writes from the textbox which is good.
I changed the FILENAME option pointing to an existing directory in the C drive and
still get the Invalid usage option FILENAME error

The following are the changes:
 CreateDatabase = "CREATE DATABASE " + txtDatabase.Text +  " ON PRIMARY " +
                "(NAME = " + txtDatabase.Text + 
                " FILENAME = 'C:\\Database\\MyDatabase.mdf', " +
                "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
                "LOG ON (NAME = MyDatabase_Log, " +
                "FILENAME = 'C:\\Database\\MyDatabaseLog.ldf', " +
                "SIZE = 1MB, " +
                "MAXSIZE = 5MB, " +
                "FILEGROWTH = 10%)";


Was This Post Helpful? 0
  • +
  • -

#10 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Re: C# Create a Database Programmatically

Posted 02 March 2012 - 08:28 AM

You still have another problem in your generated SQL. You need a comma after Name = blahblah, so that it is between the database name from the textbox and the FILENAME keyword.
Was This Post Helpful? 0
  • +
  • -

#11 RexGrammer  Icon User is offline

  • Coding Dynamo
  • member icon

Reputation: 181
  • View blog
  • Posts: 777
  • Joined: 27-October 11

Re: C# Create a Database Programmatically

Posted 02 March 2012 - 11:16 AM

Some Reference:
CREATE DATABASE (Transact-SQL) (T-SQL)
SQL CREATE DATABASE Statement (SQL)

I guess this will help you to get to know the CREATE DATABASE statement a little better.

And when you hit on a problem don't immediately ask others for help, do a little research on your own, and if you really can't find anything then ask for help. Just a little google could return you loads of reading on the SQL CREATE DATABASE statement.

This post has been edited by RexGrammer: 02 March 2012 - 11:18 AM

Was This Post Helpful? 0
  • +
  • -

#12 bravo659  Icon User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 181
  • Joined: 18-May 08

Re: C# Create a Database Programmatically

Posted 03 March 2012 - 07:01 AM

View Posteclipsed4utoo, on 02 March 2012 - 10:28 AM, said:

You still have another problem in your generated SQL. You need a comma after Name = blahblah, so that it is between the database name from the textbox and the FILENAME keyword.


I did added the comma but i get the Invalid expression error

I've tried to include the comma as ("Name = ," or ("Name = ", or ("Name = " + txtDatabase.Text, + "FILENAME =

and still get the errors. when i use the same syntax in Sql Management Studio I don't get these errors it creates the database instantly.

I really don't know what i am missing.

Your help is well appreciated.
Was This Post Helpful? 0
  • +
  • -

#13 bravo659  Icon User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 181
  • Joined: 18-May 08

Re: C# Create a Database Programmatically

Posted 03 March 2012 - 07:07 AM

View PostRexGrammer, on 02 March 2012 - 01:16 PM, said:

Some Reference:
CREATE DATABASE (Transact-SQL) (T-SQL)
SQL CREATE DATABASE Statement (SQL)

I guess this will help you to get to know the CREATE DATABASE statement a little better.

And when you hit on a problem don't immediately ask others for help, do a little research on your own, and if you really can't find anything then ask for help. Just a little google could return you loads of reading on the SQL CREATE DATABASE statement.


Hi Rex, sure thing i understand, prior to coming here for help i researched this issue with several websites to no avail. It tells me about comma, syntax and i am doing it correct but still get my errors. I've been in the MSDN, CodeProject, Daniweb, Sql Course which is the first place i look to ensure my syntax is correct.

But here i am coding for a windows application using a form, one textbox and one button that should create the database so i can start creating tables, insert data, update data and remove data which is not a problem i already have several programs that i can retrieve, add and delete information from the sql database.

For some reason i can't get passed the CREATE DATABASE statement.

I appreciate for your help.
Was This Post Helpful? 0
  • +
  • -

#14 bravo659  Icon User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 181
  • Joined: 18-May 08

Re: C# Create a Database Programmatically

Posted 03 March 2012 - 07:32 AM

View PostRexGrammer, on 02 March 2012 - 01:16 PM, said:

Some Reference:
CREATE DATABASE (Transact-SQL) (T-SQL)
SQL CREATE DATABASE Statement (SQL)

I guess this will help you to get to know the CREATE DATABASE statement a little better.

And when you hit on a problem don't immediately ask others for help, do a little research on your own, and if you really can't find anything then ask for help. Just a little google could return you loads of reading on the SQL CREATE DATABASE statement.



This is how i created a database in Sql Management Studio:

CREATE DATABASE [Silver] ON  PRIMARY 
(NAME = 'Silver', 
 FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Silver.mdf' , 
 SIZE = 3072KB , 
 FILEGROWTH = 1024KB )
 LOG ON 
(NAME = 'Silver_log', 
 FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Silver_log.ldf' , 
 SIZE = 1024KB , 
 FILEGROWTH = 10%)



No problems the database was created immediately executing the code.
But when im doing it in windows application form I get all of the errors.
I also used the self generated wizard to give me the code as well, to no avail.
Was This Post Helpful? 0
  • +
  • -

#15 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6058
  • View blog
  • Posts: 23,496
  • Joined: 23-August 08

Re: C# Create a Database Programmatically

Posted 03 March 2012 - 09:09 AM

Are you listening at all?

WHAT IS THE VALUE IN THE VARIABLE CreateDatabase AFTER YOU'VE APPENDED YOUR DATA??? Print it out, set a debugging breakpoint on it, whatever. Check the value in that variable against what you know works. Post it here if you can't find the error.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2