locked
Create Dynamic table in SQL using C# RRS feed

  • Question

  • User-1251655565 posted

    Dears,

    How i can create dynamic table of columns by using C#. User must specify number of columns in the table by webpage and press generate button.

    ASP.NET C#

    Regards,

    Thursday, October 20, 2016 2:45 PM

All replies

  • User-691209617 posted

    Hi Asp.net king,

    Please see the below mention code in order to generate dynamic table.

    int totalObjects = rivers.Count;
    int totalCells = desiredRows * desiredColumns;
    HtmlTableRow newRow = new HtmlTableRow();
    for( i = 0; i < totalCells; i++ )
    {
        // make a new row when you get the desired number of columns
        // skip the first, empty row
        if( i % desiredColumns == 0 && i != 0 )
        {
            myTable.Rows.Add( newRow );
            newRow = new HtmlTableRow();
        }
        // keep putting in cells
        if( i < totalObjects )
            row.Cells.Add(new HtmlTableCell(rivers[i]));
        // if we have no more objects, put in empty cells to fill out the table
        else
            row.Cells.Add(new HtmlTableCell(""));
    }

    Hope it helps.

    Thursday, October 20, 2016 2:57 PM
  • User-1251655565 posted

    Thanks for your reply. but table is not HTML table, it's a database SQL table.

    Thursday, October 20, 2016 2:59 PM
  • User-691209617 posted

    Run DDL command for SQL Server using C#. 

    http://stackoverflow.com/questions/4541050/creating-new-sql-server-table-with-c-sharp

    Example:

    http://www.c-sharpcorner.com/UploadFile/mahesh/CreatingDBProgrammaticallyMCB11282005064852AM/CreatingDBProgrammaticallyMCB.aspx

    It is using Windows forms but you can adapt the idea easily for asp.net.

    Also, Try to make sqlconnection and then use DDL language query like Create Table....thats it...in that sql statement you can specify which column has to be clustered index and also primary key...

    Thursday, October 20, 2016 3:09 PM
  • User-271186128 posted

    Hi Join,

    You could refer to the following code:

    protected void CreateTable(object sender, EventArgs e)
    {
        string query = "IF OBJECT_ID('dbo.CustomersTest', 'U') IS NULL ";
        query += "BEGIN ";
        query += "CREATE TABLE [dbo].[CustomersTest](";
        query += "[CustomerId] INT IDENTITY(1,1) NOT NULL CONSTRAINT pkCustomerId PRIMARY KEY,";
        query += "[Name] VARCHAR(100) NOT NULL,";
        query += "[Country] VARCHAR(50) NOT NULL";
        query += ")";
        query += " END";
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(query))
            {
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }

    More details, see: http://www.aspsnippets.com/Articles/Create-and-Drop-SQL-Server-Table-programmatically-using-ADONet-C-and-VBNet.aspx

    Best regards,
    Dillion

    Wednesday, October 26, 2016 7:49 AM
  • User2057738320 posted

    as you want  dynamic  create table columns,  so, there are SQL for row

    if you want create table table_abc use code below 

    create table table_abc (id nvarchar(20) not null);

    before you create table_abc you need make sure table_abc is not exists. if exists, you can use  below sql to delete table

    drop table table_abc  

     

    the code below is to create a new row username:

    string query = " alter table table_abc add column username nvarchar(100); "

    please note when add column, you need speicial column data type, this code is  nvarchar(100) , there are more data type,like int, datetime ,decimal(10,2) 

    if you want delete  username column, use 

    string sql="alter table  table_abc drop column username";

    if you want update username  data type  from nvarchar(100) to nvarchar(200) you can use

    string sql=" alter table  table_abc  alter column displayname   nvarchar(200) "

    if you want update username column to displayname  you can use code below:   NOTE: this is a little difficult, you need use EXEC SP_rename store procedure to rename column.

    string sql=" EXEC sp_rename 'table_abc.username', 'displayname', 'COLUMN' ";

    finally,

    if maybe you need to get all table column to make sure column is exists or not

    DataSet ColumnList=new DataSet();   
     string sql="select 1 * from  table_abc";//exe this sql to dataset
    
    string columnname="";
    foreach(DataColumn dc in ColumnList.Table[0].Columns)
    {
    //GET ALL COLUMN NAMES columnname=dc.ColumnName; }

    hope this useful~~
     

    Wednesday, October 26, 2016 10:44 AM
  • User-174653821 posted

    sql = "create table tablename (";

    for(int i=0;i<txtNumberofColumns; i++)

    {

    sql += "col" + i + "varchar2(200),";

    }

    sql+= ")";

    execute sql query and it will create table with user define number of columns

    Wednesday, November 2, 2016 10:20 AM