Hi indradeo,
You need to loop through the GridViewRows and insert in database.
And make use of parameterized query.
Refer the updated code.
protected void Page_Load(object sender, EventArgs e)
{
if (Session["load"] != null)
Session["load"] = Convert.ToString(Convert.ToInt32(Session["load"]) + 1);
else
Session["load"] = 1;
lblTime.Text = DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss tt");
//string filePath = "//10.10.4.15/daily hr data/AQ1/AQ1.csv";
//string filePath = @"\\10.10.4.15\daily hr data\AQ1\AQ1.csv";
string filePath = @"D:\AAQMS Data\AQ1.csv";
string csvData = " ";
try
{
using (var stream = File.Open(filePath, FileMode.Open))
{
using (var reader = new StreamReader(stream))
{
//Read the contents of CSV file.
csvData = reader.ReadToEnd();
}
}
//Create a DataTable.
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[]
{
new DataColumn("Parameters", typeof(string)),
new DataColumn("PM10", typeof(string)),
new DataColumn("PM2.5",typeof(string)),
new DataColumn("SO2", typeof(string)),
new DataColumn("NO", typeof(string)),
new DataColumn("NO2", typeof(string)),
new DataColumn("NOx", typeof(string)),
new DataColumn("CO2", typeof(string)),
new DataColumn("Temp" ,typeof(string)),
new DataColumn("WD", typeof(string)),
new DataColumn("WS", typeof(string)),
new DataColumn("RH",typeof(string)),
new DataColumn("SR", typeof(string)),
new DataColumn("Rain_Gauge",typeof(string)),
new DataColumn(" ",typeof(string)),
new DataColumn(".",typeof(string))
});
dt.Rows.Add("UoM", "µg/m3", "µg/m3", "µg/m3", "µg/m3", "µg/m3", "µg/m3", "µg/m3", "degreC", "DEGREE", "m/s", "%", "W/m2", "m m");
//Execute a loop over the rows.
bool firstRow = true;
// foreach (string row in csvData.Split('\n'))
foreach (string row in System.IO.File.ReadAllLines(filePath).Skip(6)) // .Skip(1) is for skipping header
{
if (firstRow)
{
firstRow = false;
}
else
{
if (!string.IsNullOrEmpty(row))
{
int i = 0;
DataRow dr = dt.NewRow();
foreach (string cell in row.Split(';'))
{
dr[i] = cell;
i++;
}
dt.Rows.Add(dr);
// To Insert Values in Table
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
// Loop through the GridViewRows and insert in database.
foreach (GridViewRow gvRow in GridView1.Rows)
{
this.Insertaaqms(gvRow.Cells[1].Text, gvRow.Cells[2].Text, gvRow.Cells[3].Text, gvRow.Cells[4].Text, gvRow.Cells[5].Text, gvRow.Cells[6].Text, gvRow.Cells[7].Text, gvRow.Cells[8].Text, gvRow.Cells[9].Text, gvRow.Cells[10].Text, gvRow.Cells[11].Text, gvRow.Cells[12].Text, gvRow.Cells[13].Text);
}
}
catch (Exception ex)
{
// ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('AAQMS Station File open in another location.');", true);
Label1.Text = "Station File Open In Another Location.!";
return;
}
}
private void Insertaaqms(string pm_tn, string pm_two, string so, string no, string no_two, string nox, string co_two, string tmp, string wd, string ws, string rh, string sr, string rn_gag)
{
string sql = "insert into aaqms_insrt(pm_tn, pm_two, so, no, no_two, nox, co_two, tmp, wd, ws, rh, sr, rn_gag) values (@pm_tn, @pm_two, @so, @no, @no_two, @nox, @co_two, @tmp, @wd, @ws, @rh, @sr, @rn_gag)";
string connStr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@pm_tn", pm_tn);
cmd.Parameters.AddWithValue("@pm_two", pm_two);
cmd.Parameters.AddWithValue("@so", so);
cmd.Parameters.AddWithValue("@no", no);
cmd.Parameters.AddWithValue("@no_two", no_two);
cmd.Parameters.AddWithValue("@nox", nox);
cmd.Parameters.AddWithValue("@co_two", co_two);
cmd.Parameters.AddWithValue("@tmp", tmp);
cmd.Parameters.AddWithValue("@wd", wd);
cmd.Parameters.AddWithValue("@ws", ws);
cmd.Parameters.AddWithValue("@rh", rh);
cmd.Parameters.AddWithValue("@sr", sr);
cmd.Parameters.AddWithValue("@rn_gag", rn_gag);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}