Dismiss Notice

Register now to be one of the first members of this SharePoint Community! Click here it just takes seconds!

Dismiss Notice
Welcome Guest from Country Flag

How to check Database Table already exists in SQL DB and How to create new Database Table...

Discussion in 'SharePoint Blogs' started by ahamed-fazil-buhari,, Sep 12, 2017.

  1. ahamed-fazil-buhari,

    ahamed-fazil-buhari, Guest

    Blog Posts:
    0
    0
    0
    0
    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.
    [​IMG]

    Happy Coding
    Ahamed

    Continue reading...
     

Share This Page

LiveZilla Live Chat Software