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....
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();
StringBuilder createInsertProcedure = new StringBuilder();
createTableStatement.Append("create table " + tableName + " ( ");
createInsertProcedure.Append("create procedure sp_" + tableName + " (");
string csvColName = "", csvparams = "";
foreach (DataColumn col in dt.Columns)
{
string name = col.ColumnName;
string sqldatatype = col.DataType == typeof(int) ? " int " : " varchar (100) ";
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(")");
Console.WriteLine(createTableStatement);
createInsertProcedure.Append(") as begin insert into " + tableName + " ( ");
createInsertProcedure.Append(csvColName);
createInsertProcedure.Append(" ) values ( ");
createInsertProcedure.Append(csvColName);
createInsertProcedure.Append(" )");
Console.WriteLine(createInsertProcedure);
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();
}
}
}