How to check Database Table already exists in SQL DB and How to create new Database Table using C# programmatically


Ahamed Fazil Buhari
SharePoint Developer
Published On :   11 Sep 2017
Visit Count
Today :  4    Total :   188
Plan, Migrate, Secure, Report
SharePoint & Office 365 Tool. Simple & Easy to Use. 15-Day Trial!

Sharegate: Kick-Ass Tool
Think Your SharePoint & Office 365 Are Secure ? Find Out Now!


Hello everyone, in this article we will see how to check if particular Database Table is available in the SQL and if it does not exists then we can create New Database Table. Use the below function to check Database Table name is available or not and it return Boolean value, True if Table available and False if Table not available.

 //Getting Connection String from App.config file
 string conString = ConfigurationManager.ConnectionStrings["CS"].ConnectionString.ToString();
 using (SqlConnection sqlCon = new SqlConnection(conString))
 {
 bool fixTableExists = CheckSQLTable(sqlCon, "MyTable");
        //Create New Table If table is not available
        if (!fixTableExists)
        	FixedTableCreation(sqlCon, fixedTable);
 }
  //Method used to check SQL Database Table availability
  public static bool CheckSQLTable(SqlConnection sqlCon, string tableName)
         {
             bool checkTable;
             string sqlCmdExTableCheck = @"SELECT count(*) as IsExists FROM dbo.sysobjects where id = object_id('[dbo].[" + tableName + "]')";
 
             using (SqlCommand sqlCmd = new SqlCommand(sqlCmdExTableCheck, sqlCon))
             {
                 sqlCon.Open();
                 try
                 {
                     checkTable = ((int)sqlCmd.ExecuteScalar() == 1);
                     {
                         sqlCon.Close();
                         return checkTable;
                     }
                 }
                 catch
                 {
                     checkTable = false;
                     sqlCon.Close();
                     return checkTable;
                 }
             }
         }
 
         //Method used to create Database Table
         public static void TableCreation(SqlConnection sqlCon, string tableName)
         {
             string myQuery = "IF OBJECT_ID('" + tableName + "', 'U') IS NULL ";
             myQuery += "BEGIN ";
             myQuery += "CREATE TABLE " + tableName + "(";
             myQuery += "Name VARCHAR(50),";
             myQuery += "ID [int],";         
             myQuery += "DOB [DATE]";
             myQuery += ")";
             myQuery += " END";
 
             using (SqlCommand sqlCmd = new SqlCommand(myQuery, sqlCon))
             {
                 sqlCon.Open();
                 sqlCmd.ExecuteNonQuery();
                 sqlCon.Close();
             }
         }
 

The below Table has been created created using TableCreation(SqlConnection sqlCon, string tableName) function.

clip_image001

Happy Coding

Ahamed

SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Categories

SharePoint Analytics