Create a SQL Server Database dynamically in C#

Mahesh Chand

SQL provides statements to create new databases and database objects. You can execute these statements from your program to create databases programmatically.

In this article, I'll show you how to create a new SQL Server database and its objects such as a table, stored procedures, views and add and view data. I'll also show you how to change database table schema programmatically. You'll see how SQL statement ALTER TABLE is useful when you need to change a database table schema programmatically.

Not only that, but this article also shows you how to view contents from a database table, stored procedures, and views.

SQL not only lets you select, add, and delete data from databases table, it also provides commands to manage databases. Using SQL statements you can create database objects programmatically such as a table, view, stored procedure, rule, index, and so on. It also provides commands to alter a database and database schemas for example adding and deleting a column from a database table, adding some constraints to a column, and so on. This example shows you how to create a new database table, add data to it, create a view of the data, alter the database table, and then delete the newly created table.

In this application, I'll create a SQL Server database, create a database table, add data to it, create database objects such as views, stored procedures, rules, and index and view data in the data grid using Sql data provider.

To test this application, create a Windows application adds a data grid control and some button controls. You can even test code by adding only one button or one button for each activity. Our application form looks like Figure 1.

Figure 1. Creating a database and it's object application.

After adding controls, add the following variables in the beginning of the form class.

private string ConnectionString ="Integrated Security=SSPI;" +
"Initial Catalog=;" +
"Data Source=localhost;";
private SqlDataReader reader = null;
private SqlConnection conn = null;
private SqlCommand cmd = null;
private System.Windows.Forms.Button AlterTableBtn;
private string sql = null;
private System.Windows.Forms.Button CreateOthersBtn;
private System.Windows.Forms.Button button1;

First thing I'm going to do is create ExecuteSQLStmt method. This method executes a SQL statement against the SQL Sever database (mydb which I will create from my program) using Sql data providers using ExecuteNonQuery method. The ExecuteSQLStmt method is listed in Listing 1.

Listing 1. The ExecuteSQLStmt method.

private void ExecuteSQLStmt(string sql)
{
if (conn.State == ConnectionState.Open)
conn.Close();
ConnectionString = "Integrated Security=SSPI;" +
"Initial Catalog=mydb;" +
"Data Source=localhost;";
conn.ConnectionString = ConnectionString;
conn.Open();
cmd = new SqlCommand(sql, conn);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException ae)
{
MessageBox.Show(ae.Message.ToString());
}
}

After this, I'm going to create a new SQL Server database. The CREATE DATABASE SQL statement creates a database. The syntax of CREATE DATABASE depends on the database you create. Depending on the database type, you can also set values of database size, growth, and file name. Listing 2 creates a SQL Server database mydb and data files are stored in the C:\\mysql directory.

Listing 2. Creating a SQL Server database.

// This method creates a new SQL Server database
private void CreateDBBtn_Click(object sender, System.EventArgs e)
{
// Create a connection
conn = new SqlConnection(ConnectionString);
// Open the connection
if (conn.State != ConnectionState.Open)
conn.Open();
string sql = "CREATE DATABASE mydb ON PRIMARY"
+ "(Name=test_data, filename = 'C:\\mysql\\mydb_data.mdf', size=3,"
+ "maxsize=5, filegrowth=10%)log on"
+ "(name=mydbb_log, filename='C:\\mysql\\mydb_log.ldf',size=3,"
+ "maxsize=20,filegrowth=1)";
ExecuteSQLStmt(sql);
}

Now the next step is to create a table. You use CREATE TABLE SQL statement to create a table. In this statement, you define the table and schema (table columns and their data types). Listing 3 creates a table myTable with four-column listed in Table 1.

Table 1. New table myTable schema.

Column Name	Type	Size	Property
myId	integer	4	Primary Key
myName	char	50	Allow Null
myAddress	char	255	Allow Null
myBalance	float	8	Allow Null

Listing 4. Creating a database table.

private void CreateTableBtn_Click(object sender, System.EventArgs e)
{
// Open the connection
if (conn.State == ConnectionState.Open)
conn.Close();
ConnectionString = "Integrated Security=SSPI;" +
"Initial Catalog=mydb;" +
"Data Source=localhost;";
conn.ConnectionString = ConnectionString;
conn.Open();
sql = "CREATE TABLE myTable" +
"(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY," +
"myName CHAR(50), myAddress CHAR(255), myBalance FLOAT)";
cmd = new SqlCommand(sql, conn);
try
{
cmd.ExecuteNonQuery();
// Adding records the table
sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " +
"VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 23.98 ) ";
cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " +
"VALUES (1002, 'Anoop Singh', 'Lodi Road, DELHI', 353.64) ";
cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " +
"VALUES (1003, 'Rakesh M', 'Nag Chowk, Jabalpur M.P.', 43.43) ";
cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " +
"VALUES (1004, 'Madan Kesh', '4th Street, Lane 3, DELHI', 23.00) ";
cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
catch (SqlException ae)
{
MessageBox.Show(ae.Message.ToString());
}
}

As you can see from Listing 5, I also add data to the table using INSERT INTO SQL statement.

The CREATE PROCEDURE statement creates a stored procedure as you can see in Listing 10-18, I create a stored procedure myPoc which returns data result of SELECT myName and myAddress column.

Listing 5. Creating a stored procedure programmatically.

private void CreateSPBtn_Click(object sender, System.EventArgs e)
{
sql = "CREATE PROCEDURE myProc AS" +
" SELECT myName, myAddress FROM myTable GO";
ExecuteSQLStmt(sql);
}

Now I show you how to create views programmatically using CREATE VIEW SQL statement. As you can see from Listing 6, I create a view myView which is the result of myName column rows from myTable.

Listing 6. Creating a view using CREATE VIEW

private void CreateViewBtn_Click(object sender, System.EventArgs e)
{
sql = "CREATE VIEW myView AS SELECT myName FROM myTable";
ExecuteSQLStmt(sql);
}

The ALTER TABLE is a useful SQL statement if you need to change your database schema programmatically. The ALTER TABLE statement can be used to add and remove new columns to a table, changing column properties, data types, and constraints. The Listing 7 shows that I change the database schema of myTable by first change column data type range from 50 to 100 characters and by adding a new column newCol of TIMESTAMP type.

Listing 7. Using ALTER TABLE to change a database schema programmatically.

private void AlterTableBtn_Click(object sender, System.EventArgs e)
{
sql = "ALTER TABLE MyTable ALTER COLUMN" +
"myName CHAR(100) NOT NULL";
ExecuteSQLStmt(sql);
}

The new table schema looks like Table 2.

Table 2. MyTable after ALTER TABLE

Column Name	Type	Size	Property
myId	integer	4	Primary Key
myName	char	50	Allow Null
myAddress	char	255	Allow Null
myBalance	float	8	Allow Null
newCol	timestamp	8	Allow Null

You can also create other database objects such as index, rule, and users. The code listed in Listing 8 creates one rule and index on myTable.

Note: Create an Index that can only create an index if you don't have an index on a table. Otherwise, you will get an error message.

Listing 8. Creating rules and indexes using SQL statements.

private void CreateOthersBtn_Click(object sender, System.EventArgs e)
{
sql = "CREATE UNIQUE CLUSTERED INDEX " +
"myIdx ON myTable(myName)";
ExecuteSQLStmt(sql);
sql = "CREATE RULE myRule " +
"AS @myBalance >= 32 AND @myBalance < 60";
ExecuteSQLStmt(sql);
}

The DROP TABLE command can be used to delete a table and its data permanently. The code listed in Listing 9 deletes myTable.

Listing 9. Deleting table using DROP TABLE.

private void DropTableBtn_Click(object sender, System.EventArgs e)
{
string sql = "DROP TABLE MyTable ";
ExecuteSQLStmt(sql);
}

Now the next step is to view data from the table, view, and stored procedure. The ViewDataBtn_Click method listed in Listing 10 shows the entire data from the table. The ViewSPBtn_Click and ViewViewBtn_Click methods view the stored procedure and view the data we have created earlier. As you can see using views and stored procedures works the same as you use a SQL Statement. We have discussed working with Views and stored procedures at the beginning of this chapter. As you can see from Listing 10, 11, and 12, I view data from stored procedure and view.

Listing 10. Viewing data from a database table.

private void ViewDataBtn_Click(object sender, System.EventArgs e)
{
/// Open the connection
if (conn.State == ConnectionState.Open)
conn.Close();
ConnectionString = "Integrated Security=SSPI;" +
"Initial Catalog=mydb;" +
"Data Source=localhost;";
conn.ConnectionString = ConnectionString;
conn.Open();
// Create a data adapter
SqlDataAdapter da = new SqlDataAdapter
("SELECT * FROM myTable", conn);
// Create DataSet, fill it and view in data grid
DataSet ds = new DataSet("myTable");
da.Fill(ds, "myTable");
dataGrid1.DataSource = ds.Tables["myTable"].DefaultView;
}

Listing 11.Using a stored procedure to view data from a table.

private void ViewSPBtn_Click(object sender, System.EventArgs e)
{
/// Open the connection
if (conn.State == ConnectionState.Open)
conn.Close();
ConnectionString = "Integrated Security=SSPI;" +
"Initial Catalog=mydb;" + "Data Source=localhost;";
conn.ConnectionString = ConnectionString;
conn.Open();
// Create a data adapter
SqlDataAdapter da = new SqlDataAdapter("myProc", conn);
// Create DataSet, fill it and view in data grid
DataSet ds = new DataSet("SP");
da.Fill(ds, "SP");
dataGrid1.DataSource = ds.DefaultViewManager;
}

Listing 12.Using a view to view data from a table.

private void ViewViewBtn_Click(object sender, System.EventArgs e)
{
/// Open the connection
if (conn.State == ConnectionState.Open)
conn.Close();
ConnectionString = "Integrated Security=SSPI;" +
"Initial Catalog=mydb;" +
"Data Source=localhost;";
conn.ConnectionString = ConnectionString;
conn.Open();
// Create a data adapter
SqlDataAdapter da = new SqlDataAdapter
("SELECT * FROM myView", conn);
// Create DataSet, fill it and view in data grid
DataSet ds = new DataSet();
da.Fill(ds);
dataGrid1.DataSource = ds.DefaultViewManager;
}

Finally, I create AppExit method which releases the connection and reader objects and I call them from the Dispose method as you can see in Listing 13.

Listing 13. AppExit method

protected override void Dispose(bool disposing)
{
AppExit();
if (disposing)
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose(disposing);
}
// Called when you are done with the application
// Or from Close button
private void AppExit()
{
if (reader != null)
reader.Close();
if (conn.State == ConnectionState.Open)
conn.Close();
}

Summary

In this article, you saw how to create a new database and database objects including tables, stored procedures, views, and alter tables. You also saw how to delete these objects using SQL statements.

SQL Server Create DatabaseCreate Tablecreate databasesCreate SQL Server databaseruntime database create

18 Comments

Join the conversation! Your thoughts help the community grow.

Jed Official5y ago
How to dop a database sir?
Barath Kumar M6y ago
Thanks of lot sir,
Kuppurasu Nagaraj10y ago
Nice Sharing..
Vishal Mittal11y ago
hello sir , can you help me please... This error is occurring when i try to create dynamic database-A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
- Mahesh Chand10y ago
  The account you're using to connect to SQL Server may not have permissions or your network may not have open port. https://support.microsoft.com/en-us/kb/914277
Arjun Dhilod12y ago
hi i am using you Listing 4. Creating a database table. but only insert value one time from code second time not insert data
Arjun Dhilod12y ago
private void CreateTableBtn_Click(object sender, System.EventArgs e){ // Open the connection if( conn.State == ConnectionState.Open) conn.Close(); ConnectionString ="Integrated Security=SSPI;" "Initial Catalog=mydb;" "Data Source=localhost;"; conn.ConnectionString = ConnectionString; conn.Open(); sql = "CREATE TABLE myTable" "(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY," "myName CHAR(50), myAddress CHAR(255), myBalance FLOAT)" ; cmd = new SqlCommand(sql, conn); try { cmd.ExecuteNonQuery(); // Adding records the table sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " "VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 23.98 ) " ; cmd = new SqlCommand(sql, conn); cmd.ExecuteNonQuery(); sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " "VALUES (1002, 'Anoop Singh', 'Lodi Road, DELHI', 353.64) " ; cmd = new SqlCommand(sql, conn); cmd.ExecuteNonQuery(); sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " "VALUES (1003, 'Rakesh M', 'Nag Chowk, Jabalpur M.P.', 43.43) " ; cmd = new SqlCommand(sql, conn); cmd.ExecuteNonQuery(); sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " "VALUES (1004, 'Madan Kesh', '4th Street, Lane 3, DELHI', 23.00) " ; cmd = new SqlCommand(sql, conn); cmd.ExecuteNonQuery(); } catch(SqlException ae) { MessageBox.Show(ae.Message.ToString()); } }
Former member13y agoedited
i try to create database but following exception is raised.. system.data.sqlcleient.sqlexception {"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"} plz help me out...thanks..
Ante Sevo14y ago
Hi I'm tryin to develop web service application where I need to create database dinamicaly. I have problem in connecting to sql server. Do you know ifI have to make some changes in my web.config file in connectionstrings part? Thanks in advance.Ante
shakeel2dv14y ago
Hi, The article is quite informative and I am working on kind of same technique but I am having some error. I have to create multiple store procedures. Like follows: Create procedure ABC AS Some Statement GO Create procedure XYZ AS Some Statements GO ….. And it comes up with Error “Incorrect syntax near the keyword 'PROCEDURE'.” But as long as one procedure creation is concern it works. And as I have 100s of SPs to create. Shakeel
Desi Bravo14y ago
Hi i like your tutorial but was wondering if you can create a database programmatically using a textbox to enter the database name rather than hard-coding the files in code view? Thanks
kiuk Iu14y agoedited
please help me i created windows form add person and now need to save in another form list of client how i can make ??? please help me in visual C++
laxman s15y ago
Sir, i tried to create a database with the name entered by the user at runtime. con.Open(); cmd = new SqlCommand("create database ' "+textBox1.Text+" ' ", con); cmd.ExecuteNonQuery(); im getting incorrect syntax error. can u help me.. thanks
ravi jain16y ago
sir, i read the above article and understood very well, table name is specified in the query, my quest. is--------- can we create a table with the name entered by user in a textbox at runtime. is it possible, plz help me out, plz provide me source code also
- Mahesh Chand16y ago
  YES. Just pass table name from TextBox to the CREATE TABLE query.
Mayank Gupta16y ago
i m mayank Gupta, i m trying to connect a window form, with sqlserver2005 database through a disconnected environment, the user enter the values in the textbox at runtime. and it save to the database. plz help to solve this.... and plz provide source code with window form image... my email id is -- [email protected].
George Corzo18y ago
Do you know the reason why i am getting the error below when i try to connect to the SQL Server?An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Thanks.
ashutosh srivastava18y ago
please help me out, E.G Like the Northwind database consists of customer table and customerId as one of the field with description(size=5,name=customerId,required=no,allowZerolength=no) I want to get this description by c#(c-sharp)