Dynamically create table in Database with same name as user insert text into TextBox in asp.net

Description:-

       In this example we explain that how to create table at Run time and as userdefined name.in which example the user enter name in TextBox1.Text and then the table is created automatically with same name as user enter into the TextBox1.Text.

This is very UseFul Example Because suppose a sitution oocur like you have to Create a table of all user that are Register in your Website at that time you have to create a table at run time with Table Name is same as user enter it’s name in TextBox1.

Here is Query For How to Create table in Runtime

string cmdText = null;

        cmdText = cmdText + " BEGIN";

        cmdText = cmdText + " CREATE TABLE " + TextBox1.Text + "(id int IDENTITY(1,1),name varchar(MAX),subject varchar(255),file1 varchar(MAX),body1 varchar(MAX), date DateTime);";

        cmdText = cmdText + " END";

It is also very useful to store all user Data separtaly like XYZ user Data is stored in XYZ table and ABC user’s data are stored in ABC table.so you can maintain all user very easily.

In this Example we also Check if user is Exist with same name then you are not Registered and you must have to enter Different name in TextBox1.

Here is a Query to Check Table is Exist or Not

string f = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME='" + TextBox1.Text + "'";

        SqlCommand cmd3 = new SqlCommand(f, cn);

        SqlDataReader sr = cmd3.ExecuteReader();

        bool b = sr.HasRows;

        if (b)

        {

            ClientScript.RegisterClientScriptBlock(this.GetType(), "ke", "<script>alert('already exists');</script>");

        }

ModalPopup For Insert,Update,Delete CRUD operation in Popup


Code:-

string conn = @"Data Source=SQLDB;Initial Catalog=Demo;User ID=Demod;Password=Demo1@";

string query = "insert into stud (unm,pwd) values('"+TextBox1.Text+"','"+TextBox2.Text+"')";

      

        SqlConnection cn = new SqlConnection(conn);

        SqlCommand cmd = new SqlCommand(query, cn);

        string cmdText=null;

        cmdText = cmdText + " BEGIN";

        cmdText = cmdText + " CREATE TABLE " + TextBox1.Text + "(id int IDENTITY(1,1),name varchar(MAX),subject varchar(255),file1 varchar(MAX),body1 varchar(MAX), date DateTime);";

   

      cmdText = cmdText + " END";

      string cmdText1 = null;

      cmdText1 = cmdText1 + " BEGIN";

      cmdText1 = cmdText1 + " CREATE TABLE " + TextBox1.Text + "s"+ "(id int IDENTITY(1,1),name varchar(MAX),subject varchar(255),file1 varchar(MAX),body1 varchar(MAX), date DateTime);";

      cmdText1 = cmdText1 + " END";

      cn.Open();

      SqlCommand cmd1 = new SqlCommand(cmdText, cn);

    

      SqlCommand cmd2 = new SqlCommand(cmdText1, cn);

  

      string f = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME='"+TextBox1.Text+"'";

      SqlCommand cmd3 = new SqlCommand(f, cn);

      SqlDataReader sr = cmd3.ExecuteReader();

    

      bool b = sr.HasRows;

      if (b)

      {

          ClientScript.RegisterClientScriptBlock(this.GetType(), "ke", "<script>alert('already exists');</script>");

      }

      else

      {

          sr.Close();

          cmd.ExecuteNonQuery();

          cmd1.ExecuteNonQuery();

         cmd2.ExecuteNonQuery();

         ClientScript.RegisterClientScriptBlock(this.GetType(), "ke", "<script>alert('register successfully');</script>");

      }