how to create sql server database table from c# code

Creating a database table.

private void CreateTableBtn_Click(object sender, System.EventArgs e)
{
// Open the connectionif( 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 tablesql = "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 returs 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 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 show 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 object such as index, rule, and users. The code listed in Listing 8 creates one rule and index on myTable.

Note: Create Index 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 statement.

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 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 stored procedure and view data we have created earlier. As you can see using views and stored procedures work same as you use a SQL Statement. We have discussed working with Views and stored procedures in 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 connectionif( conn.State == ConnectionState.Open)
conn.Close();
ConnectionString ="Integrated Security=SSPI;" +
"Initial Catalog=mydb;" +
"Data Source=localhost;";
conn.ConnectionString = ConnectionString;
conn.Open(); // Create a data adapterSqlDataAdapter da = new SqlDataAdapter
("SELECT * FROM myTable", conn); // Create DataSet, fill it and view in data gridDataSet 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 connectionif( conn.State == ConnectionState.Open)
conn.Close();
ConnectionString ="Integrated Security=SSPI;" +
"Initial Catalog=mydb;" +"Data Source=localhost;";
conn.ConnectionString = ConnectionString;
conn.Open(); // Create a data adapterSqlDataAdapter da = new SqlDataAdapter("myProc", conn); // Create DataSet, fill it and view in data gridDataSet 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 connectionif( conn.State == ConnectionState.Open)
conn.Close();
ConnectionString ="Integrated Security=SSPI;" +
"Initial Catalog=mydb;" +
"Data Source=localhost;";
conn.ConnectionString = ConnectionString;
conn.Open(); // Create a data adapterSqlDataAdapter da = new SqlDataAdapter
("SELECT * FROM myView", conn); // Create DataSet, fill it and view in data gridDataSet 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 applicaton// Or from Close buttonprivate void AppExit()
{if (reader != null)
reader.Close();if (conn.State == ConnectionState.Open)
conn.Close();
}