locked
Saving form data into database for later return RRS feed

  • Question

  • User-718146471 posted

    Hi there, I need some help here because I tried adapting your code to include all my fields and it does not seem to be storing the data in the session state (see http://forums.asp.net/post/6052879.aspx). Here is my code and many thanks again for your help:

    .cs

    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    
    namespace WASAuditSystem
    {
        public partial class GridViewDemo : System.Web.UI.Page
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!Page.IsPostBack)
                {
                    Vulnerabilities = GetVulnerabilities();
                    BindGrid();
                }
                else
                {
                    UpdateGridDataSource();
                }
    
            }
            //Mock repo
            private List<VulnerabilityToSystem> Vulnerabilities
            {
                get
                {
                    if (ViewState["VulnerabilityToSystemList"] != null)
                    {
                        return ((List<VulnerabilityToSystem>)ViewState["VulnerabilityToSystemList"]).
                            OrderBy(m => m.VulnerabilityId).
                            ThenBy(m => m.SortOrder).ToList();
                    }
                    return GetVulnerabilities().OrderBy(m => m.VulnerabilityId).ThenBy(m => m.SortOrder).ToList();
                }
                set
                {
                    ViewState["VulnerabilityToSystemList"] = value;
                }
            }
    
    
            private void BindGrid()
            {
                GridView1.DataSource = Vulnerabilities;
                GridView1.DataBind();
            }
    
            protected void UpdateGridDataSource()
            {
                foreach (GridViewRow row in GridView1.Rows)
                {
                    HiddenField uid = (HiddenField)row.FindControl("VulnerabilityToSystemId");
                    HiddenField gid = (HiddenField)row.FindControl("TempId");
                    CheckBox cb = row.FindControl("cbVulnName") as CheckBox;
                    DropDownList ddl = (DropDownList)row.FindControl("ddlType");
                    TextBox txt = (TextBox)row.FindControl("txtCount");
    
                    //Handle the new guid records
                    if (gid != null && gid.Value.ToString() != "00000000-0000-0000-0000-000000000000")
                    {
                        List<VulnerabilityToSystem> repo = Vulnerabilities;
                        VulnerabilityToSystem item;
                        item = (VulnerabilityToSystem)(from d in repo
                                                       where d.TempId == Guid.Parse(gid.Value)
                                                       select d).First();
                        int x = Int32.Parse(ddl.SelectedValue);
                        item.type = Convert.ToString(x);
                        item.count = txt.Text.ToString();
                        item.cbVulnName = cb.Checked;
                    }
    
                    //handle the existing records
                    if (uid != null && uid.Value.ToString() != "0")
                    {
                        List<VulnerabilityToSystem> repo = Vulnerabilities;
                        VulnerabilityToSystem item;
                        item = (VulnerabilityToSystem)(from d in repo
                                                       where d.VulnerabilityToSystemId == int.Parse(uid.Value)
                                                       select d).First();
                        int x = Int32.Parse(ddl.SelectedValue);
                        item.type = Convert.ToString(x);
                        item.count = txt.Text.ToString();
                        item.cbVulnName = cb.Checked;
                    }
                }
            }
    
            //Command button click handler
            protected void addCmd_Command(object sender, CommandEventArgs e)
            {
                int id = 0;
                if (!string.IsNullOrEmpty(e.CommandArgument.ToString()))
                {
                    if (int.TryParse(e.CommandArgument.ToString(), out id))
                    {
                        AddDuplicateRecordById(id);
                        BindGrid();
                    }
                }
            }
    
            protected void removeCmd_Command(object sender, CommandEventArgs e)
            {
                Guid id;
                if (!string.IsNullOrEmpty(e.CommandArgument.ToString()))
                {
                    if (Guid.TryParse(e.CommandArgument.ToString(), out id))
                    {
                        RemoveRecordByGuid(id);
                        BindGrid();
                    }
                }
            }
    
            private void RemoveRecordByGuid(Guid id)
            {
                List<VulnerabilityToSystem> repo = Vulnerabilities;
                VulnerabilityToSystem dup = new VulnerabilityToSystem();
                dup = (VulnerabilityToSystem)(from d in repo
                                              where d.TempId == id
                                              select d).First();
                repo.Remove(dup);
                Vulnerabilities = repo;
            }
    
            // Update Repository
            private void AddDuplicateRecordById(int id)
            {
                // suspicious and exploitable
                List<VulnerabilityToSystem> repo = Vulnerabilities;
                VulnerabilityToSystem dup = new VulnerabilityToSystem();
    
                //Create a copy
                dup = (VulnerabilityToSystem)(from d in repo
                                              where d.VulnerabilityToSystemId == id
                                              select new VulnerabilityToSystem()
                                              {
                                                  cbVulnName = d.cbVulnName,
                                                  VulnerabilityToSystemId = 0,
                                                  VulnerabilityId = d.VulnerabilityId,
                                                  TempId = Guid.NewGuid(),
                                                  Vuln_Name=d.Vuln_Name,
                                                  type = d.type,
                                                  count = d.count,
                                                  Vuln_Reference = d.Vuln_Reference,
                                                  SortOrder = d.SortOrder + 1,
                                              }).FirstOrDefault();
                //Persist the Update
                repo.Add(dup);
                Vulnerabilities = repo;
            }
    
            //Mock data retrieval 
            protected List<VulnerabilityToSystem> GetVulnerabilities()
            {
                // get complete list of vulnerabilities for Grid View
                List<VulnerabilityToSystem> results = new List<VulnerabilityToSystem>();
                int i = 1;
                string SQLQuery = "SELECT Vuln_Name, Vuln_Reference FROM Vulnerabilities";
                SqlCommand cmd = new SqlCommand(SQLQuery, conn);
                conn.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    results.Add(new VulnerabilityToSystem() { VulnerabilityToSystemId = i, VulnerabilityId = i, Vuln_Name = rdr.GetValue(0).ToString(), count = "", type = "0", Vuln_Reference = rdr.GetValue(1).ToString() });
                    i++;
                }
                return results;
            }
    
            protected void btnPrepare_Click(object sender, EventArgs e)
            {    
                // Prepare the formatted message to display on screen        
                Label1.Text = string.Empty;
                CheckBox tempC;
                Label tempCName;
                DropDownList ddlC;
                TextBox txtC;
                Label t1;
                int index = 1;
    
                Label1.Text += String.Format("<table style='border-spacing:2px; spacing:10px; padding:10px; border:solid;' width='100%'><tr style='background-color:#3399FF; text-transform: uppercase; color: white; font-weight: bolder;'><td>Name</td><td>Risk Level</td><td>Count</td><td>Reference</td></tr>");
                string vulnBoth = string.Empty, vulncount = string.Empty, vulntype = string.Empty;
                try
                {
                    foreach (GridViewRow row in GridView1.Rows)
                    {
                        tempC = (CheckBox)row.FindControl("cbVulnName");
                        if ((row.RowType == DataControlRowType.DataRow) && (tempC.Checked))
                        {
                            tempCName = (Label)row.FindControl("lblVulnName");
                            ddlC = (DropDownList)row.FindControl("ddlType");
                            t1 = (Label)row.FindControl("lblComments");
                            txtC = (TextBox)row.FindControl("txtCount");
                            if (t1.Text == null)
                            {
                                t1.Text = string.Empty;
                            }
                            if (index % 2 == 0)
                            {
                                Label1.Text += String.Format("<tr style='background-color:#CCEEFF;'>");
                                Label1.Text += String.Format("<td>{0}</td>", tempCName.Text);
                                Label1.Text += String.Format("<td>{0}</td>", ddlC.SelectedItem.Text.ToString());
                                Label1.Text += String.Format("<td>{0}</td>", txtC.Text);
                                Label1.Text += String.Format("<td><a href={0} target='_blank'>Reference</a></td></tr>", t1.Text);
                                index++;
                            }
                            else
                            {
                                Label1.Text += String.Format("<tr style='background-color:#FFFFFF;'>");
                                Label1.Text += String.Format("<td>{0}</td>", tempCName.Text);
                                Label1.Text += String.Format("<td>{0}</td>", ddlC.SelectedItem.Text.ToString());
                                Label1.Text += String.Format("<td>{0}</td>", txtC.Text);
                                Label1.Text += String.Format("<td><a href={0} target='_blank'>Reference</a></td></tr>", t1.Text);
                                index++;
                            }
                            if (tempC.Checked)
                            {
                                if ((txtC.Text.ToString() == string.Empty) & (ddlC.SelectedIndex == 0))
                                {
                                    vulnBoth = vulnBoth + "<br />You selected " + tempCName.Text.ToString() + " however you forgot to enter the count and select the vulnerability.";
                                }
                                else if (txtC.Text.ToString() == string.Empty)
                                {
                                    vulncount = vulncount + "<br />You selected " + tempCName.Text.ToString() + " however you forgot to enter the count.";
                                }
                                else if (ddlC.SelectedIndex == 0)
                                {
                                    vulntype = vulntype + "<br />You selected " + tempCName.Text.ToString() + " however you forgot to select the vulnerability type.";
                                }
                                lblBothErr.Text = vulnBoth.ToString();
                                lblCountErr.Text = vulncount.ToString();
                                lblTypeErr.Text = vulntype.ToString();
                            }
                        }
                        //ScriptManager.RegisterStartupScript(Page, typeof(Page), "GVErrors", "setTimeout(GVErrors, 1);", true);
                    }
                    try
                    {
                        if (rfAuditor.IsValid == true)
                        {
                            rfAuditor.Visible = false;
                        }
                        else
                        {
                            rfAuditor.Visible = true;
                            SetFocus(rfAuditor);
                        }
                        if (rfvAppName.IsValid == true)
                        {
                            rfvAppName.Visible = false;
                        }
                        else
                        {
                            rfvAppName.Visible = true;
                            SetFocus(rfvAppName);
                        }
                        if (rfvDevEmail.IsValid == true)
                        {
                            rfvDevEmail.Visible = false;
                        }
                        else
                        {
                            rfvDevEmail.Visible = true;
                            SetFocus(rfvDevEmail);
                        }
                    }
                    catch (Exception ex)
                    {
    
                    }
                    if (txtAuditorComments.Text != "")
                    {
                        Label1.Text += String.Format("</table><br><br><table border='1' width='100%'><tr><td colspan='4'>Auditor Comments:" + txtAuditorComments.Text.ToString() + "</td></tr>");
                    }
                    Label1.Text += String.Format("</table>");
                    btnSubmit.Visible = true;
                    if ((rfvDevEmail.IsValid = false) | (rfvAppName.IsValid = false) | (rfAuditor.IsValid = false) | (lblBothErr.Text != string.Empty) | (lblTypeErr.Text != string.Empty) | (lblCountErr.Text != string.Empty))
                    {
                        btnSubmit.Visible = false;
                    }
                    else
                    {
                        btnSubmit.Visible = true;
                    }
                }
                catch (Exception ex)
                {
    
                }
            }
    
            protected void btnSubmit_Click(object sender, EventArgs e)
            {
                // write the email and try to send it
                string htmlBody = "Hello, I have audited " + txtAppName.Text.ToString() + ".<br><br>We have found the following issues. Please reply in SSC with your comments/fixes.<br><br>" + Label1.Text.ToString();
                htmlBody = htmlBody + "<br><br>Please let me know if you have any questions.<br><br>Respectfully,<br><br>" + ddlAuditor.SelectedItem.ToString() + "";
                string fromAddr = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
                string ccAddr = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
                string ccMulti = txtCCDevs.Text.ToString();
                string toAddr = txtDevEmail.Text.ToString();
                string Subject = "Application Evaluation: " + txtAppName.Text.ToString();
    
                try
                {
                    //attempt to send
                    smtpHelper.SendMailMessage(fromAddr.ToString(), toAddr.ToString(), ccAddr.ToString(), txtCCDevs.Text.ToString(), Subject.ToString(), htmlBody.ToString());
                    ClearTextBoxes(Page);
                    Label1.Text = string.Empty;
                    btnSubmit.Visible = false;
                    GridView1.DataSource = Vulnerabilities;
                    GridView1.DataBind();
                    Response.Write("Successfully Sent.");
                }
                catch (Exception ex)
                {
                    // sending failed - show error on web page
                    Response.Write("Error: " + ex.ToString());
                }
    
            }
    
            protected void ClearTextBoxes(Control p1)
            {
                // Wipes out values in form to completely reset
                foreach (Control ctrl in p1.Controls)
                {
    
                    if (ctrl is TextBox)
                    {
                        TextBox t = ctrl as TextBox;
    
                        if (t != null)
                        {
                            t.Text = String.Empty;
                        }
                    }
                    if (ctrl is CheckBox)
                    {
                        CheckBox cb = ctrl as CheckBox;
    
                        if (cb != null)
                        {
                            cb.Checked = false;
                        }
                    }
                    else
                    {
                        if (ctrl.Controls.Count > 0)
                        {
                            ClearTextBoxes(ctrl);
                        }
                    }
                }
            }
    
            protected void btnSaveChanges_Click(object sender, EventArgs e)
            {
                // Write into SessionState to maintain values entered into form
            }
        }
        //Model
        [Serializable()]
        public class VulnerabilityToSystem
        {
            public int VulnerabilityToSystemId { get; set; }
            public int VulnerabilityId { get; set; }
            public Guid TempId { get; set; }
            public bool cbVulnName { get; set; }
            public string Vuln_Name { get; set; }
            public string type { get; set; }
            public string count { get; set; }
            public string Vuln_Reference { get; set; }
            public int SortOrder { get; set; }
        }
    }

    .aspx

    <%@ Page Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="ReturnEval.aspx.cs" Inherits="WASAuditSystem.GridViewDemo" %>
    <%@ Register assembly="WebControls" namespace="WebControls" tagprefix="fwc" %>
    <asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
        <div>
                <style type="text/css">
            .auto-style1 {
                width: 90%;
            }
            .auto-style2 {
                width: 87px;
            }
            .auto-style3 {
                width: 89px;
            }
            .auto-style4 {
                width: 91px;
            }
            .auto-style5 {
                width: 93px;
            }
            .auto-style6 {
                width: 355px;
            }
            .auto-style7 {
                width: 114%;
            }
            .item {white-space: nowrap;display:inline  }
        </style>
    </asp:Content>
    <asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
        <table class="auto-style7">
            <tr>
                <td class="auto-style6" style="vertical-align: top; text-wrap:none;">*Auditor</td><td class="auto-style4" style="vertical-align: top;">
                <asp:DropDownList ID="ddlAuditor" runat="server">
                    <asp:ListItem Selected="True">-- SELECT --</asp:ListItem>
                    <asp:ListItem Value="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx">Doe, John</asp:ListItem>
                    <asp:ListItem Value="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx">Doe, Jane</asp:ListItem>
                    <asp:ListItem Value="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx">Doe, Jimmy</asp:ListItem>
                </asp:DropDownList>
                <br />
                </td>
                <td class="auto-style2" rowspan="3" style="vertical-align: top;">
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="auto-style6" style="vertical-align: top; text-wrap:none;">*App Name:</td><td class="auto-style4" style="vertical-align: top;"><asp:TextBox ID="txtAppName" runat="server" Width="490px"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td class="auto-style6" style="vertical-align: top; text-wrap:none;">*Dev Email:</td><td class="auto-style5" style="vertical-align: top;"><asp:TextBox ID="txtDevEmail" runat="server" Width="490px"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td class="auto-style6" style="vertical-align:top; text-wrap:none;">CC:<br />
                    (use ; for addresses)</td><td class="auto-style5" style="vertical-align: top;">
                    <asp:TextBox ID="txtCCDevs" runat="server" TextMode="MultiLine" Rows="4" Width="490px"></asp:TextBox></td>
            </tr>
            <tr>
                <td class="auto-style6" style="vertical-align:top; text-wrap:none;">Auditor Comments:</td><td class="auto-style5" style="vertical-align: top;">
                    <asp:TextBox ID="txtAuditorComments" runat="server" Rows="5" TextMode="MultiLine" Width="490px"></asp:TextBox>
                </td>
            </tr>
                <tr>
                <td class="auto-style6" style="vertical-align: top; text-wrap:none; color: red;"><strong>* Required </strong></td><td class="auto-style5" style="vertical-align: top;">
                <asp:RequiredFieldValidator ID="rfAuditor" runat="server" ControlToValidate="ddlAuditor" ErrorMessage="&lt;br /&gt;Choose the auditor name" Font-Bold="True" ForeColor="Red" InitialValue="-- SELECT --"></asp:RequiredFieldValidator>
                <asp:RequiredFieldValidator ID="rfvAppName" runat="server" ControlToValidate="txtAppName" ErrorMessage="<br />Application name is required" Font-Bold="True" ForeColor="Red" Display="Dynamic"></asp:RequiredFieldValidator>            
                <asp:RequiredFieldValidator ID="rfvDevEmail" runat="server" ControlToValidate="txtDevEmail" ErrorMessage="<br />Developer Email Address required" Font-Bold="True" ForeColor="Red" Display="Dynamic"></asp:RequiredFieldValidator>
                <div id="GVErrors" />
                <asp:Label ID="lblBothErr" runat="server" Font-Bold="True" ForeColor="Red"></asp:Label>            
                <asp:Label ID="lblCountErr" runat="server" Font-Bold="True" ForeColor="Red"></asp:Label>            
                <asp:Label ID="lblTypeErr" runat="server" Font-Bold="True" ForeColor="Red"></asp:Label>
                </td>
                <td class="auto-style2" style="vertical-align: top;"></td>
            </tr>
            <tr>
                <td class="auto-style3" style="vertical-align: top; text-wrap:none;" colspan="3">&nbsp;
                <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
                </td>
            </tr>
            <tr>
                <td class="auto-style3" style="vertical-align: top; text-wrap:none;" colspan="3">
                <asp:Button ID="btnSubmit" runat="server" OnClick="btnSubmit_Click" Text="Send Message" Visible="false" />
                </td>
            </tr>
        </table>
            <asp:Panel runat="server" ID="pnlPlaceHolder">
                <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
                <asp:UpdateProgress runat="server" AssociatedUpdatePanelID="updGridView1"></asp:UpdateProgress>            
                <asp:UpdatePanel runat="server" UpdateMode="Conditional" RenderMode="Inline" ID="updGridView1">                
                    <Triggers>
                        <asp:PostBackTrigger ControlID="GridView1" />
                    </Triggers>
                    <ContentTemplate>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Width="100%">
                <Columns>
                    <asp:TemplateField ShowHeader="False">
                        <ItemTemplate>
                            <asp:LinkButton ID="addCmd" runat="server" CausesValidation="false" CommandName="add" Text="Add" CommandArgument='<%# Bind("VulnerabilityToSystemId") %>' Visible='<%# Eval("TempId").ToString() == "00000000-0000-0000-0000-000000000000" ? true : false %>' OnCommand="addCmd_Command"><img src="images/insert.jpg" width="25px" height="25px" border="0" /></asp:LinkButton>
                            <asp:LinkButton ID="remCmd" runat="server" CausesValidation="false" CommandName="delete" Text="Remove" CommandArgument='<%# Bind("TempId") %>' Visible ='<%# Eval("TempId").ToString() == "00000000-0000-0000-0000-000000000000" ? false : true  %>' OnCommand="removeCmd_Command"><img src="images/delete.jpg" width="25px" height="25px" border="0" /></asp:LinkButton>
                            <asp:HiddenField ID="VulnerabilityToSystemId" runat="server" Value='<%# Bind("VulnerabilityToSystemId") %>' />
                            <asp:HiddenField ID="VulnerabilityId" runat="server" Value='<%# Bind("VulnerabilityId") %>'  />
                            <asp:HiddenField ID="TempId" runat="server" Value='<%# Bind("TempId") %>'  />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Name" ItemStyle-CssClass="label">
                        <ItemTemplate>
                            <div class="item">
                                <asp:CheckBox ID="cbVulnName" runat="server" Checked='<%# Eval("cbVulnName") %>' /> 
                                &nbsp;&nbsp;
                                <asp:Label ID="lblVulnName" runat="server" Text='<%# Eval("Vuln_Name") %>' />
                            </div>
                        </ItemTemplate>
                        <ItemStyle Font-Size="X-Small" />
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Type">
                        <ItemTemplate>
                            <asp:DropDownList ID="ddlType" runat="server" Width="140px" SelectedValue='<%# Eval("type") %>'>
                                <asp:ListItem Selected="True" Value="0" Text="--SELECT--" />
                                <asp:ListItem Value="1" Text="Suspicious" />
                                <asp:ListItem Value="2" Text="Exploitable" />
                            </asp:DropDownList>
                        </ItemTemplate>
                        <ControlStyle Font-Size="X-Small" />
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Count">
                        <ItemTemplate>
                            <asp:TextBox ID="txtCount" runat="server" Width="50px" Text='<%# Eval("count") %>' />
                        </ItemTemplate>
                        <ItemStyle Font-Size="X-Small" />
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Reference">
                        <ItemTemplate>
                            <asp:HyperLink ID="hlComments" runat="server" Height="25px" ImageHeight="25px" ImageUrl="~/images/www.png" ImageWidth="25px" NavigateUrl='<%# Eval("Vuln_Reference") %>' Width="25px" Target="_blank" />
                            <asp:Label Visible="false" ID="lblComments" runat="server" Text='<%# Eval("Vuln_Reference") %>' />
                        </ItemTemplate>
                        <ItemStyle Font-Size="X-Small" />
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
                        </ContentTemplate>
                    </asp:UpdatePanel>
                <asp:Button ID="btnPrepare" runat="server" OnClick="btnPrepare_Click" Text="Prepare Message" />
                </asp:Panel>
    </asp:Content>

    Tuesday, May 31, 2016 1:12 PM

Answers

  • User-271186128 posted

    Hi bbcompent1,

    What I would like to know is how to I store and retrieve the current viewstate of the GridView in the database table? I have everything else pretty much set up for this; I just need details on how to get/set these variables from the database table. Thanks!

    According to your code, you are using ViewState to store the "List<VulnerabilityToSystem>". If you want to retrieve the list and insert them into DataBase, I suggest you could use for or foreach statement to loop through the list, code as below:

    List<VulnerabilityToSystem> repo = Vulnerabilities;
    
    foreach(VulnerabilityToSystem item in repo)
    {
        //insert into database.
    }

    Then, you can refer to the following code to insert data into database:

                  string constr = ConfigurationManager.ConnectionStrings["MyTestDBConnStr"].ToString();
    
                  using (SqlConnection con = new SqlConnection(constr))
                  {
                      string cmdtext = "Insert into TestTable values(@description)";
                      using (SqlCommand cmd = new SqlCommand(cmdtext, con))
                      {
                          con.Open();
                          cmd.Parameters.AddWithValue("@description", "value ");
                          cmd.ExecuteNonQuery();
                      }
                  }

    Here are some relevant articles, you can refer to them:

    http://www.aspsnippets.com/Articles/GridView-CRUD-Select-Insert-Edit-Update-Delete-using-Single-Stored-Procedure-in-ASPNet.aspx

    http://www.c-sharpcorner.com/uploadfile/raj1979/select-add-update-and-delete-data-in-a-Asp-Net-gridview-control/

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 2, 2016 8:29 AM

All replies

  • User475983607 posted

    IMHO, Session is evil and it lulls you by making it so darn easy to persist data on the server.  Later you find maintenance and extensibility issues and you'll want to kick yourself.  I recommend using a database to persist data especially if the user needs the data hours or days later.

    I would create a table in your DB that hold the data.  Add a completed timestamp and whatever else you need to uniquely identify the user and task.  If the user walks away from the machine for an extended period of time, the data remains stored in the DB.  When they resume working simply retrieve the record and continue.  Set the timestamp on the record when the user completes the task. 

    Tuesday, May 31, 2016 3:33 PM
  • User-718146471 posted

    Hm, ok that sounds a lot more logical and safer. Not that I am too concerned about the security since this app is only intranet based. I was considering using a DB table to hold the records. Do you have any good coding examples of how this is done?

    Tuesday, May 31, 2016 5:56 PM
  • User-718146471 posted

    Ok, as recommended, I built the table focused around what I think I need.

    RecID (UniqueIdentifier, PK, Not Null, Auto)
    UserID (varchar(50), not null) - Pulled from AD
    AuditorName (Varchar(100), null) From Dropdown list
    AppName (VarChar(250), null) from textbox
    DevEmail (VarChar(250), null) From TextBox
    CCEmails (Varchar(MAX), null) From TextBox
    AuditorComments(varchar(MAX), null) From textbox
    GridViewSession(varchar(MAX), null) from GridView

    Does this look like a sensible schema for that table? What should be my next move?

    Tuesday, May 31, 2016 6:11 PM
  • User475983607 posted

    Does this look like a sensible schema for that table? What should be my next move?

    I would think you need a timestamp or bit field that indicates the record (process) is completed.  Otherwise, it might be difficult to select a unique record.  That brings up another point.  Can the user have more than one process going at one time?  If yes, then you'll need a way for the user to select which record to work on. 

    Tuesday, May 31, 2016 6:46 PM
  • User-718146471 posted

    Ok, I can add the timestamp field, no biggie. For that multiple form situation, I will have the system list the uncompleted forms for them and they can click on them to finish them.

    Tuesday, May 31, 2016 7:10 PM
  • User-718146471 posted

    Ok, the timestamp complete field has been added. Good to go. This will also take care of another request I had to show forms that have been completed in a listing.

    Tuesday, May 31, 2016 7:14 PM
  • User-718146471 posted

    So, exactly how do I store and retrieve these values from the database as suggested? I'll continue to search as well. I figure this will help others who are trying to accomplish this same thing.

    Tuesday, May 31, 2016 7:17 PM
  • User475983607 posted

    So, exactly how do I store and retrieve these values from the database as suggested?

    I'm not sure, I assume you would filter by the userId and timestamp is null or completed (bit field) = 0.  Or maybe you have the records ID?  This is more of a design issue.

    Tuesday, May 31, 2016 7:26 PM
  • User-718146471 posted

    I figured that part out, I'm going to use a GUID as the record ID and the forms that have no timestamp will be available for modification. What I would like to know is how to I store and retrieve the current viewstate of the GridView in the database table? I have everything else pretty much set up for this; I just need details on how to get/set these variables from the database table. Thanks!

    Wednesday, June 1, 2016 1:24 PM
  • User-271186128 posted

    Hi bbcompent1,

    What I would like to know is how to I store and retrieve the current viewstate of the GridView in the database table? I have everything else pretty much set up for this; I just need details on how to get/set these variables from the database table. Thanks!

    According to your code, you are using ViewState to store the "List<VulnerabilityToSystem>". If you want to retrieve the list and insert them into DataBase, I suggest you could use for or foreach statement to loop through the list, code as below:

    List<VulnerabilityToSystem> repo = Vulnerabilities;
    
    foreach(VulnerabilityToSystem item in repo)
    {
        //insert into database.
    }

    Then, you can refer to the following code to insert data into database:

                  string constr = ConfigurationManager.ConnectionStrings["MyTestDBConnStr"].ToString();
    
                  using (SqlConnection con = new SqlConnection(constr))
                  {
                      string cmdtext = "Insert into TestTable values(@description)";
                      using (SqlCommand cmd = new SqlCommand(cmdtext, con))
                      {
                          con.Open();
                          cmd.Parameters.AddWithValue("@description", "value ");
                          cmd.ExecuteNonQuery();
                      }
                  }

    Here are some relevant articles, you can refer to them:

    http://www.aspsnippets.com/Articles/GridView-CRUD-Select-Insert-Edit-Update-Delete-using-Single-Stored-Procedure-in-ASPNet.aspx

    http://www.c-sharpcorner.com/uploadfile/raj1979/select-add-update-and-delete-data-in-a-Asp-Net-gridview-control/

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 2, 2016 8:29 AM