Thursday, December 15, 2011

Data Table to Data base in c#

Title:How to insert the data in to database using data table in using c#

As per example i have to insert the data from CSV file. For this task i have done two steps.One is,Bind the data to data table and then iterate the each row of data table then we can insert the data in to Data Base .Here i have checked the whether the data is empty or null in column of data table.i have shown another query in below to insert the data with out check

Code Behind:
string insertQuery = "INSERT into [Table name](Id,field2,field3,field4,field5,field6,field7,field8,field9,field10,field11) Values({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},'{10}')";
foreach (DataRow dr in Datatable.Rows)
cmd.Connection = con;
cmd.CommandText = string.Format(insertQuery, string.IsNullOrEmpty(Convert.ToString(dr["Col1"]).Trim()) ? null : dr["Col1"].ToString().Trim(),
         string.IsNullOrEmpty(Convert.ToString(dr["Col2"]).Trim()) ? null : dr["Col2"].ToString().Trim(),
         string.IsNullOrEmpty(Convert.ToString(dr["Col3"]).Trim()) ? null : dr["Col3"].ToString().Trim(),
         string.IsNullOrEmpty(Convert.ToString(dr["Col4"]).Trim()) ? null : dr["Col4"].ToString().Trim(),
         string.IsNullOrEmpty(Convert.ToString(dr["Col5"]).Trim()) ? null : dr["Col5"].ToString().Trim(),
         string.IsNullOrEmpty(Convert.ToString(dr["Col6"]).Trim()) ? null : dr["Col6"].ToString().Trim(),
         string.IsNullOrEmpty(Convert.ToString(dr["Col7"]).Trim()) ? null : dr["Col7"].ToString().Trim(),
         string.IsNullOrEmpty(Convert.ToString(dr["Col8"]).Trim()) ? null : dr["Col8"].ToString().Trim(),
         string.IsNullOrEmpty(Convert.ToString(dr["Col9"]).Trim()) ? null : dr["Col9"].ToString().Trim(),
         string.IsNullOrEmpty(Convert.ToString(dr["Col10"]).Trim()) ? null : dr["Col10"].ToString().Trim(),DateTime.Now);
The second one is:
cmd.CommandText = string.Format(insertQuery, dr["col1"], dr["col2"], dr["col3"], dr["col4"], dr["col5"],dr["col6"];

No comments: