Hello readers! In this Blog we will understand how can we create new database and new table in SQL Server using .Net . SQL provides statements to create new databases and tables. In the Code below we are executing those statements to create database and table programmatically. Here we are also checking whether the databasee and the table already exist. If not then we are creating them.

//FUNCTION TO CREATE NEW DATABASE WITH GIVEN NAME IN SQL SERVER 
private void CreateNewDB()
{
    String strSqlConn, strDBName;
    Boolean isDBExist = false;
    SqlConnection sqlConn=new SqlConnection("Server=localhost;Integrated security=SSPI;database=master"); // CONNECTION  USED TO OPEN SQL SERVER 
    try
    {
        strDBName = "NewDatabase"; // NAME OF NEW DATABASE
        strSqlConn="Select * from master.dbo.sysdatabases where name='" + (strDBName + "'"); // QUERY TO CHECK WHETHER THIS DATABASE ALREADY EXISTS 

        sqlConn.Open(); // OPENS THE DATABASE CONNECTION WITH THE PROPERTY SETTING SPECIFIED BY CONNECTIONSTRING ABOVE
        SqlCommand sqlCommand = new SqlCommand(strSqlConn, sqlConn); // SQL STATEMENT TO EXECUTE AGAINST A SQL SERVER DATABASE

        SqlDataReader sqlDataReader= sqlCommand.ExecuteReader();
        if (sqlDataReader.HasRows) // RETURNS TRUE IF THE DATABASE EXIST
            isDBExist = true;

        sqlDataReader.Close(); // CLOSE THE SQLDATAREADER OBJECT
        sqlDataReader.Dispose();// RELEASSES ALL RESOURCES USED BY THE CURRENT INSTANCE 

        if(isDBExist)
            return; // RETURN IF DATABASE ALREADY EXISTS

        strSqlConn = "CREATE DATABASE " + strDBName; // QUERY TO CREATE NEW DATABASE

        sqlCommand = new SqlCommand(strSqlConn, sqlConn);
        sqlCommand.ExecuteNonQuery(); 
    }
    catch (System.Exception ex)
    {
       // HANDLE EXCEPTION
    }
    finally
    {
            // IF SQL CONECTION IS OPEN , CLOSE IT 
        if (sqlConn.State == ConnectionState.Open)
        {
            sqlConn.Close();
        }
    }
}


//FUNCTION TO CREATE NEW TABLE WITH GIVEN NAME IN SQL SERVER 
private void CreateNewTable()
{
    String strSqlConn, strTableName;
    Boolean tableExist = false;
    SqlConnection sqlConn = new SqlConnection("Server=localhost;Initial Catalog=NewDatabase;Integrated security=SSPI"); // CONNECTION  USED TO OPEN SQL SERVER DATABASE
    try
    {
        strTableName = "NewTable"; // NAME OF NEW TABLE
        strSqlConn = "SELECT * FROM sys.tables WHERE name ='" + (strTableName + "'"); // QUERY TO CHECK WHETHER THIS TABLE ALREADY EXISTS 

        sqlConn.Open(); // OPENS THE DATABASE CONNECTION WITH THE PROPERTY SETTING SPECIFIED BY CONNECTIONSTRING ABOVE
        SqlCommand sqlCommand = new SqlCommand(strSqlConn, sqlConn); // SQL STATEMENT TO EXECUTE AGAINST A SQL SERVER DATABASE

        SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
        if (sqlDataReader.HasRows) // RETURNS TRUE IF THE TABLE EXIST 
            tableExist = true;

        sqlDataReader.Close(); // CLOSE THE SQLDATAREADER OBJECT
        sqlDataReader.Dispose();// RELEASSES ALL RESOURCES USED BY THE CURRENT INSTANCE 

        if (tableExist) // RETURN IF TABLE ALREADY EXIST
            return;

        strSqlConn = "CREATE TABLE " + strTableName + "( TableCol_1 varchar(255), TaleCol_2 int) ; "; // QUERY TO CREATE NEW TABLE
        sqlCommand = new SqlCommand(strSqlConn, sqlConn);
        sqlCommand.ExecuteNonQuery();
    }
    catch (System.Exception ex)
    {
        // HANDLE EXCEPTION
    }
    finally
    {
            // IF SQL CONECTION IS OPEN , CLOSE IT 
        if (sqlConn.State == ConnectionState.Open)
        {
            sqlConn.Close(); 
        }
    }
}