Haminteu Haminteu - 3 months ago 23
C# Question

Grid Bind then Insert into SQL Table

I have the following code:


protected void btnGetData_Click(object sender, EventArgs e)
{
clearGrid();

bool isValid = true;
DateTime fromDate, toDate;

DateTime.TryParse(txtFromDate.Text, out fromDate);
DateTime.TryParse(txtToDate.Text, out toDate);

if (String.IsNullOrEmpty(txtTag1.Text) && String.IsNullOrEmpty(txtTag2.Text) && String.IsNullOrEmpty(txtTag3.Text) &&
String.IsNullOrEmpty(txtTag4.Text) && String.IsNullOrEmpty(txtTag5.Text))
{
ltErrorMessage.Text = "Tag cannot empty";
isValid = false;
}

if (fromDate == DateTime.MinValue || toDate == DateTime.MinValue)
{
ltErrorMessage.Text = "Not valid date";
isValid = false;
}

if (toDate < fromDate)
{
ltErrorMessage.Text = "To Date cannot be earlier than From Date";
isValid = false;
}

if (isValid)
{
TimeSpan ts = new TimeSpan(1, 0, 0);

if (ddlTimeSpan.SelectedValue == "d")
{
ts = new TimeSpan(Convert.ToInt32(txtTimeSpan.Text), 0, 0, 0);
}
else if (ddlTimeSpan.SelectedValue == "h")
{
ts = new TimeSpan(Convert.ToInt32(txtTimeSpan.Text), 0, 0);
}
else if (ddlTimeSpan.SelectedValue == "m")
{
ts = new TimeSpan(0, Convert.ToInt32(txtTimeSpan.Text), 0);
}
else if (ddlTimeSpan.SelectedValue == "s")
{
ts = new TimeSpan(0, 0, Convert.ToInt32(txtTimeSpan.Text));
}


var service = new PILoaderService.PILoaderServiceClient();

try
{
service.Endpoint.Address = new System.ServiceModel.EndpointAddress(pisvcendpoint);
service.ClientCredentials.Windows.ClientCredential = new System.Net.NetworkCredential(pisvcuser, pisvcpassword);
service.ClientCredentials.Windows.AllowedImpersonationLevel = TokenImpersonationLevel.Impersonation;

List<dynamic> PIResult = new List<dynamic>();

string[] data1 = null;
string[] data2 = null;
string[] data3 = null;
string[] data4 = null;
string[] data5 = null;

if (!String.IsNullOrEmpty(txtTag1.Text))
{
data1 = service.GetPIData(serverName, conString, fromDate, toDate, ts, txtTag1.Text);
}
if (!String.IsNullOrEmpty(txtTag2.Text))
{
data2 = service.GetPIData(serverName, conString, fromDate, toDate, ts, txtTag2.Text);
}
if (!String.IsNullOrEmpty(txtTag3.Text))
{
data3 = service.GetPIData(serverName, conString, fromDate, toDate, ts, txtTag3.Text);
}
if (!String.IsNullOrEmpty(txtTag4.Text))
{
data4 = service.GetPIData(serverName, conString, fromDate, toDate, ts, txtTag4.Text);
}
if (!String.IsNullOrEmpty(txtTag5.Text))
{
data5 = service.GetPIData(serverName, conString, fromDate, toDate, ts, txtTag5.Text);
}
if (data1.Count() == 0 && data2.Count() == 0 && data3.Count() == 0 && data4.Count() == 0 && data5.Count() == 0)
{
ltErrorMessage.Text = "Result contains no data";
}
else
{
var pointDate = fromDate;
foreach (var item in data1)
{
PIResult.Add(new { Date = pointDate.ToString("dd-MMM-yyyy HH:mm"), Tag = txtTag1.Text, Value = item });
pointDate = pointDate.Add(ts);
}
if (data2 != null)
{
pointDate = fromDate;
foreach (var item in data2)
{
PIResult.Add(new { Date = pointDate.ToString("dd-MMM-yyyy HH:mm"), Tag = txtTag2.Text, Value = item });
pointDate = pointDate.Add(ts);
}
}
if (data3 != null)
{
pointDate = fromDate;
foreach (var item in data3)
{
PIResult.Add(new { Date = pointDate.ToString("dd-MMM-yyyy HH:mm"), Tag = txtTag3.Text, Value = item });
pointDate = pointDate.Add(ts);
}
}
if (data4 != null)
{
pointDate = fromDate;
foreach (var item in data4)
{
PIResult.Add(new { Date = pointDate.ToString("dd-MMM-yyyy HH:mm"), Tag = txtTag4.Text, Value = item });
pointDate = pointDate.Add(ts);
}
}
if (data5 != null)
{
pointDate = fromDate;
foreach (var item in data5)
{
PIResult.Add(new { Date = pointDate.ToString("dd-MMM-yyyy HH:mm"), Tag = txtTag5.Text, Value = item });
pointDate = pointDate.Add(ts);
}
}
}
grdPIData.DataSource = PIResult.OrderBy(o => o.Date).ThenBy(o => o.Tag);
grdPIData.DataBind();
}
catch (Exception ex)
{
ltErrorMessage.Text = ex.ToString();
}
finally
{
service.Close();
}
}
}


The result is bind into one of the GridView. My question is, How can I "
Insert
" the result into SQL Table? The scenario is, Everytime I clicked the button, it will load into GridView then I have another button which is the Upload button. The action is to insert all the data on the GridView into one of the table in SQL Server.


Really appreciated. Thank you.

Answer

got the answer. :)

protected void insertdata(object sender, EventArgs e)
{
     foreach (GridViewRow row in grdPIData.Rows)
     {
          string sCon = ConfigurationManager.ConnectionStrings["conName"].ConnectionString);
          SqlConnection con = new SqlConnection(sCon);
          SqlCommand cmd = new SqlCommand("Insert into Table1(oDate, oTag, oVal) Values ('" & row.Cells[0].Text.ToString() + "' + row.Cells[1].Text.ToString() + "' + '" + row.Cells[2].Text.ToString() + "')", con);
          con.Open();
          cmd.ExecuteNonQuery();
          con.Close();
     }
}
Comments