Click here to Skip to main content
15,890,717 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I have a specific requirement which goes like this,

There will be a DataTable in my C# application the structure of that is dynamic. I wanted that to be stored with the same structure into DB.

For Ex: I have a DataTable with Name Test and TestID and TestValue are two DataColumns in it.
I wanted in DB to create a Table as Test and with columns TestID and TestValue also copy the data as well from the data table.

So whenever I get a data Table a new table should be created in the DB.

This requirement can be done either through C# code itself or even using a stored procedure in SQL Server.

Please help me in this.

Thanks & Regards,
Mathi.
Posted
Updated 19-Jan-14 20:07pm
v2
Comments
Karthik_Mahalingam 20-Jan-14 3:27am    
Dude its a big concept..
i have done this before..
Mathi2code 20-Jan-14 4:10am    
Bro please help me in this...
I'm in desperate need of this concept...
Karthik_Mahalingam 20-Jan-14 4:24am    
do u know sql stored procedures ? c# Reflection ??
Mathi2code 20-Jan-14 4:45am    
I know stored procs but reflection have to learn...
Karthik_Mahalingam 20-Jan-14 5:14am    
ok i will give the sample code structure, but you only have to build the rest.
coz i am not having that code now...

Hi Mathi,
this is a very big concept to do..as you need to take care on tons of validation

Try like this
First try to understand the code.
keep break point and try to analyse what is happening and customize as per your needs.
this is the basic structure for your need....

C#
class Program
{

    static void Main(string[] args)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("NumberColumn", typeof(int));
        dt.Rows.Add(1);
        dt.Rows.Add(2);

        string tableName = "yourTableName";
         string spName = "sp_" + tableName;
        StringBuilder createTableStatement = new StringBuilder(); // string builder to create table schema
        StringBuilder createInsertProcedure = new StringBuilder(); // string builder to create insert SP
        createTableStatement.Append("create table " + tableName + " ( ");
        createInsertProcedure.Append("create procedure sp_" + tableName + " (");

        string csvColName = "", csvparams = "";
        foreach (DataColumn col in dt.Columns)  // iterating the datatable columns
        {
            string name = col.ColumnName;
            string sqldatatype = col.DataType == typeof(int) ? " int " : " varchar (100) "; // this condition you have to do for all the data types
            string eachColumn = string.Format("{0} {1}", name, sqldatatype);
            string eachParam = string.Format("@{0} {1}", name, sqldatatype);
            createTableStatement.Append(eachColumn);
            createInsertProcedure.Append(eachParam);
            csvColName += name + ",";
            csvparams += "@" + name + " ,";

        }
        csvColName = csvColName.Trim().TrimEnd(',');
        csvparams = csvparams.Trim().TrimEnd(',');
        createTableStatement.Append(")"); // now the table has been created....
        Console.WriteLine(createTableStatement); //output :create table yourTableName ( NumberColumn  int )
        createInsertProcedure.Append(") as begin insert into " + tableName + " ( ");
        createInsertProcedure.Append(csvColName);
        createInsertProcedure.Append(" ) values ( ");
        createInsertProcedure.Append(csvColName);
        createInsertProcedure.Append(" )");   // use this string to create a stored procedure .ADO.net
        Console.WriteLine(createInsertProcedure); //output : create procedure sp_yourTableName (@NumberColumn  int ) as begin insert into 
        //  yourTableName ( NumberColumn ) values ( NumberColumn )

        // now you know the sp name ie; sp_yourTableName
        // you can insert each rows as follows 
        var con = new SqlConnection ("your conn string");
        SqlCommand cmd = new SqlCommand(spName ,con );
        cmd.CommandType = CommandType.StoredProcedure;
        foreach (DataRow row in dt.Rows)
        {
            con.Open();
            foreach (DataColumn col in dt.Columns)
                cmd.Parameters.AddWithValue("@" + col.ColumnName, row[col.ColumnName]);
            cmd.ExecuteNonQuery();
            con.Close();
        }

    }
}
 
Share this answer
 
Comments
Karthik_Mahalingam 20-Jan-14 9:41am    
string name = col.ColumnName;
string sqldatatype = col.DataType == typeof(int) ? " int " : " varchar (100) "; // this condition you have to do for all the data types
string eachColumn = string.Format("{0} {1}", name, sqldatatype);
string eachParam = string.Format("@{0} {1}", name, sqldatatype);


these lines you have to take care of lot of validations. i have done only for one column...
 
Share this answer
 
v2
A stored proc is a waste of time. You will still string mash a 'create table' statement. So, iterate over the properties in your data table and build a 'create table' statement in SQL.

This is almost certainly a stupid thing to do. You're better off building a table that allows for you to store the data you want to store, with an additional column to denote which 'table' the data is in.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900