Editable GridView without ObjectDataSource Concepts

• Download source code - 9.49 KB

Introduction

This is my first article so please bear with me.

Sometimes, binding a GridView directly to an ObjectDataSource is not a viable option. This article will demonstrate how to bind a GridView control to a DataTable and allow for Inserts, Updates, Deletes, Sorting by Columns, Paging, and Filtering by the alphabet. I also have included a bulk import to import a CSV file that checks columns restraints and imports into a SQL Server database. The results appear in the refreshed GridView.

Background

Coding best practices advise you to use Stored Procedures to interact with a database. This helps avoid SQL injection attacks which can compromise security. This solution offers samples as to how to bind a GridView to the results of a Stored Procedure while still being able to interact with the grid data in various ways.

The Physician table is required for the sample code and contains the following structure:

CREATE TABLE [dbo].[tPhysician](
    [PhysicianID] [int] IDENTITY(1,1) NOT NULL,
    [HospitalID] [int] NOT NULL,
    [HospitalPhysicianID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MiddleName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Suffix] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [IsActive] [bit] NOT NULL CONSTRAINT [DF_tPhysician_Active]  DEFAULT ((1)),
 CONSTRAINT [PK_tPhysician] PRIMARY KEY CLUSTERED 
(
    [PhysicianID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Using the Code

Keep in mind that while this code was written to import and maintain a list of physicians, it can be tweaked to accommodate a variety of different scenarios that need to be stored in the database and maintained by the user(s). I'd also like to mention that although I'd love to take credit for writing all of this from scratch, I'm also not one to waste time "reinventing the wheel"; therefore, much of this solution has come from various sites throughout the interwebz, and not entirely from one in particular.

With that said, here's what you really want:

There are two classes used to access the database. The db class is the foundation for the SQL calls. It contains the connection to the database server and therefore, all other database classes are inherited from it. The dbPhysician class contains the calls to SELECT the physicians, UPDATE the physicians, DELETE the physicians, and import the physician CSV file. The PhysicianSetup.aspx page and its .cs page contain the GUI for the physician gridlist and import. This page contains a link to a master page, which is not included, so you can remove the reference or create your own ("~/DataEntry/DataEntry.master"). Also included is a .csv file containing a sample of physicians for the import and specify the layout. The last file included is a .sql file to create the necessary SQL table and the various Stored Procedures referenced in the dbPhysician class.

• PhysicianSetup.aspx - Main application GUI
• PhysicianSetup.aspx.cs - Main application code-behind
• db.cs - db class used for handing database calls
• dbPhysician.cs - dbPhysician physician DB class
• LotsOfPhysicians.csv - A sample physician import file in CSV format
• GridViewWithoutDataSource.sql - T-SQL of Physician table and Stored Procedures

Starting off with the PhysicianSetup.aspx page, the GridView is defined below. You'll notice there are actually three definitions for each field: one is a label (used for Select), another is a textbox (used for Update), and the third is a textbox used for Insert. We have also set sorting and paging on, and defined our own methods for handling these events.

<asp:GridView ID="gvPhysician" runat="server" 
  AutoGenerateColumns="False" DataKeyNames="PhysicianID" 
  OnRowCancelingEdit="gvPhysician_RowCancelingEdit" 
  OnRowEditing="gvPhysician_RowEditing" 
  OnRowUpdating="gvPhysician_RowUpdating" 
  OnRowCommand="gvPhysician_RowCommand" ShowFooter="True" 
  AllowSorting="true" AllowPaging ="true"                 
  OnRowDeleting="gvPhysician_RowDeleting" 
  OnSorting="gvPhysician_Sorting" 
  OnPageIndexChanging="gvPhysician_PageIndexChanging" 
  PageSize="15"> 
    <columns>
       <asp:TemplateField HeaderText="Last Name" SortExpression="LastName">
       <edititemtemplate>
             <asp:TextBox ID="txtLastName" runat="server" 
                          Text='<%# Eval("LastName") %>'> 
       </edititemtemplate> 
       <footertemplate> 
          <asp:TextBox ID="txtNewLastName" runat="server">
       </footertemplate> 
       <itemtemplate> 
          <asp:Label ID="Label2" runat="server" 
                     Text='<%# Bind("LastName") %>'> 
       </itemtemplate> 
        
       <asp:TemplateField HeaderText="First Name" 
                SortExpression="FirstName">
       <edititemtemplate> 
          <asp:TextBox ID="txtFirstName" runat="server" 
                Text='<%# Eval("FirstName") %>'> 
       </edititemtemplate> 
       <footertemplate> 
          <asp:TextBox ID="txtNewFirstName" runat="server" >
       </footertemplate> 
       <itemtemplate> 
          <asp:Label ID="Label1" runat="server" 
                   Text='<%# Bind("FirstName") %>'> 
       </itemtemplate> 
                
       <asp:TemplateField HeaderText="Middle Name" 
                   SortExpression="MiddleName">
       <edititemtemplate> 
          <asp:TextBox ID="txtMiddleName" runat="server" 
                     Text='<%# Eval("MiddleName") %>'> 
       </edititemtemplate> 
       <footertemplate> 
          <asp:TextBox ID="txtNewMiddleName" runat="server" >
       </footertemplate> 
       <itemtemplate> 
          <asp:Label ID="Label4" runat="server" 
                     Text='<%# Bind("MiddleName") %>'> 
       </itemtemplate> 
        
       <asp:TemplateField HeaderText="Suffix" SortExpression="Suffix">
       <edititemtemplate> 
          <asp:TextBox ID="txtSuffix" runat="server" 
                      Text='<%# Eval("Suffix") %>'> 
       </edititemtemplate> 
       <footertemplate> 
          <asp:TextBox ID="txtNewSuffix" runat="server" >
       </footertemplate> 
       <itemtemplate> 
          <asp:Label ID="Label5" runat="server" 
                      Text='<%# Bind("Suffix") %>'> 
       </itemtemplate> 
                     
       <asp:TemplateField HeaderText="Physician ID" 
                  SortExpression="HospitalPhysicianID">
       <edititemtemplate> 
          <asp:TextBox ID="txtHospitalPhysicianID" 
            runat="server" Text='<%# Eval("HospitalPhysicianID") %>'> 
       </edititemtemplate> 
       <footertemplate> 
          <asp:TextBox ID="txtNewHospitalPhysicianID" runat="server">
       </footertemplate> 
       <itemtemplate> 
          <asp:Label ID="Label3" runat="server" 
                   Text='<%# Bind("HospitalPhysicianID") %>'> 
       </itemtemplate> 
               
       <asp:TemplateField HeaderText="Edit" ShowHeader="False"> 
       <edititemtemplate> 
          <asp:LinkButton ID="lbtnUpdate" runat="server" 
               CausesValidation="True" CommandName="Update" 
               Text="Update"> 
          <asp:LinkButton ID="lbtnCancel" runat="server" 
               CausesValidation="False" CommandName="Cancel" 
               Text="Cancel">
       </edititemtemplate>
       <footertemplate> 
          <asp:LinkButton ID="lbtnInsert" runat="server" 
               CausesValidation="False" CommandName="AddNew" 
               Text="Add New">
       </footertemplate>
       <itemtemplate> 
          <asp:LinkButton ID="lbtEdit" runat="server" 
             CausesValidation="False" CommandName="Edit" 
             Text="Edit"> 
       </itemtemplate>
        
       <asp:CommandField HeaderText="Delete" 
              ShowDeleteButton="True" ShowHeader="True" /> 
    </columns>

The second object on this page is the Repeater control that is used for displaying the alphabet for filtering/searching the grid.

<asp:Repeater id="rptLetters" runat="server" 
     OnItemCommand="rptLetters_ItemCommand"   >
   <itemtemplate>
       <asp:linkbutton id="lnkLetter" runat="server" 
             commandname="Filter" 
             commandargument='<%# DataBinder.Eval(Container, "DataItem.Letter")%>'>
             <%# DataBinder.Eval(Container, "DataItem.Letter")%>
   </itemtemplate>

The last item on the page is an HTML file input box for selecting the CSV file containing the physicians to upload.

<td id="Td2"  runat="server">
  <input type="file"  runat="server" 
          id="PhysicianFile" size="40" />
</td>
<td id="Td3" align="Right" runat="server">
      <asp:Button ID="btnImport" runat="server" 
             Text="Import" OnClick="btnImport_OnClick" />
</td>

In the code-behind for this page, PhysicianSetup.aspx.cs, the only variable defined is:

protected string _letterFilter;

The Page_Load initiates our sort session variables, calls the procedure to load the physician grid, and calls the procedure to bind the alphabet to the Repeater.

object oFilter = ViewState[this.ToString() + "_LetterFilter"];
if (oFilter != null) _letterFilter = (string)oFilter;
else _letterFilter = "All";


if (!IsPostBack)
{
    ViewState[this.ToString() + "_SortExpression"] = "LastName";
    ViewState[this.ToString() + "_SortDirection"] = "ASC";
    FillPhysicianInGrid(true);

    letters_Bind();
}

Here is the procedure that binds the GridView to the DataTable:

private void FillPhysicianInGrid(bool Reload)
{
    DataTable dtPhysician = null;

    if (Reload)
    {
        dbPhysician physician = new dbPhysician();
        dtPhysician = physician.Fetch();

        dtPhysician.DefaultView.Sort = ViewState[this.ToString() + 
          "_SortExpression"].ToString() + " " + 
          ViewState[this.ToString() + "_SortDirection"].ToString();                    
    }
    else
    {
        //retrieve the session object datatable
        dtPhysician = Session["tblPhysician"] as DataTable;

        dtPhysician.DefaultView.Sort = ViewState[this.ToString() + 
          "_SortExpression"].ToString() + " " + 
          ViewState[this.ToString() + "_SortDirection"].ToString();            
    }

    if (dtPhysician != null)
    {

        if (dtPhysician.Rows.Count > 0)
        {
            Session["tblPhysician"] = dtPhysician;

            if (_letterFilter == "All")
                dtPhysician.DefaultView.RowFilter = string.Empty;
            else
                dtPhysician.DefaultView.RowFilter = 
                  "LastName LIKE '" + _letterFilter + "%'";

            gvPhysician.DataSource = Session["tblPhysician"];
            gvPhysician.DataBind();
        }
        else
        {
            dtPhysician.Rows.Add(dtPhysician.NewRow());

            Session["tblPhysician"] = dtPhysician;

            if (_letterFilter == "All")
                dtPhysician.DefaultView.RowFilter = string.Empty;
            else
                dtPhysician.DefaultView.RowFilter = 
                   "LastName LIKE '" + _letterFilter + "%'";

            gvPhysician.DataSource = Session["tblPhysician"];
            gvPhysician.DataBind();

            int TotalColumns = gvPhysician.Rows[0].Cells.Count;
            gvPhysician.Rows[0].Cells.Clear();
            gvPhysician.Rows[0].Cells.Add(new TableCell());
            gvPhysician.Rows[0].Cells[0].ColumnSpan = TotalColumns;
            gvPhysician.Rows[0].Cells[0].Text = "No Record Found";
        }
    }
}

Here's the code that handles the Insert/Add of the GridView:

protected void gvPhysician_RowCommand(object sender, GridViewCommandEventArgs e)
{
    if (e.CommandName.Equals("AddNew"))
    {
        TextBox txtNewFirstName = 
          (TextBox)gvPhysician.FooterRow.FindControl("txtNewFirstName");
        TextBox txtNewMiddleName = 
          (TextBox)gvPhysician.FooterRow.FindControl("txtNewMiddleName");
        TextBox txtNewLastName = 
          (TextBox)gvPhysician.FooterRow.FindControl("txtNewLastName");
        TextBox txtNewHospitalPhysicianID = 
          (TextBox)gvPhysician.FooterRow.FindControl("txtNewHospitalPhysicianID");
        TextBox txtNewSuffix = 
          (TextBox)gvPhysician.FooterRow.FindControl("txtNewSuffix");

        if (txtNewLastName.Text == "")
        {
            lblEntryStatus.Text = "*Last Name is a Required Field.";
        }
        else if (txtNewHospitalPhysicianID.Text == "")
        {
            lblEntryStatus.Text = "Physician ID is a Required Field.";
        }
        else
        {
            dbPhysician physician = new dbPhysician();
            physician.Insert(txtNewFirstName.Text, txtNewMiddleName.Text, 
              txtNewLastName.Text, txtNewHospitalPhysicianID.Text, txtNewSuffix.Text);
            lblEntryStatus.Text = "Physician Added.";
        }

        FillPhysicianInGrid(true);
    }
}

Here's the code that prepares the GridView for the Update/Cancel:

protected void gvPhysician_RowEditing(object sender, GridViewEditEventArgs e)
{
    gvPhysician.EditIndex = e.NewEditIndex;
    FillPhysicianInGrid(false);
}

protected void gvPhysician_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
    gvPhysician.EditIndex = -1;
    FillPhysicianInGrid(false);
}

Here's the code that handles the Delete:

protected void gvPhysician_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
    dbPhysician physician = new dbPhysician();
    physician.Delete(Convert.ToInt32(gvPhysician.DataKeys[e.RowIndex].Values[0].ToString()));

    lblEntryStatus.Text = "Physician Deleted.";
    FillPhysicianInGrid(true);
}

This is the code that handles the paging of the GridView:

protected void gvPhysician_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    gvPhysician.PageIndex = e.NewPageIndex;
    FillPhysicianInGrid(false);
}

This is the code that handles the sorting:

protected void gvPhysician_Sorting(object sender, GridViewSortEventArgs e)
{

    GetSortDirection(e.SortExpression);
    FillPhysicianInGrid(false);
}
private void GetSortDirection(string sColumn)
{
    //set sort direction to asc
    string sSortDirection = "ASC";
    string sSortExpression = ViewState[this.ToString() + 
            "_SortExpression"] as string;

    if (sSortExpression != null)
    {
        //check same column is being sorted

        if (sSortExpression == sColumn)
        {
            string sLastDirection = ViewState[this.ToString() + 
                      "_SortDirection"] as string;
            if ((sLastDirection != null) && (sLastDirection == "ASC"))
            {
                sSortDirection = "DESC";
            }
        }
    }

    //save new values in view
    ViewState[this.ToString() + "_SortDirection"] = sSortDirection;
    ViewState[this.ToString() + "_SortExpression"] = sColumn;

    //return sSortDirection;
}

This is the code that populates the alphabet Repeater for filtering the GridView results by last name:

private void letters_Bind()
{
    DataTable dtL;

    if (Session[this.ToString() + "_LettersData"] == null)
    {
        string[] letters = { "A", "B", "C", "D", 
             "E", "F", "G", "H", 
             "I", "J", "K",
             "L", "M", "N", "O", 
             "P", "Q", "R", "S", 
             "T", "U", "V",
             "W", "X", "Y", "Z", "All"};

        //create a new data table
        dtL = new DataTable();

        //create the scheme of the table
        dtL.Columns.Add(new DataColumn("Letter", typeof(string)));
        
        // Populate the data table with the letter data
        for (int i = 0; i < letters.Length; i++) 
        {
          DataRow dr = dtL.NewRow();
          dr[0] = letters[i];
          dtL.Rows.Add(dr);
        }

        //store a reference to the alphabet table for use on postback
        Session[this.ToString() + "_LettersData"] = dtL;

    }
    else
    {
        dtL = (DataTable)Session[this.ToString() + "_LettersData"];
    }


    //bind to the grid
    rptLetters.DataSource = dtL.DefaultView;
    rptLetters.DataBind();      

}


protected void rptLetters_ItemCommand(Object Sender, RepeaterCommandEventArgs e)
{
    lblEntryStatus.Text = "Filtered by Last Name '" + 
               e.CommandArgument.ToString() + "' .";

    _letterFilter = e.CommandArgument.ToString();
    ViewState[this.ToString() + "_LetterFilter"] = _letterFilter;

    FillPhysicianInGrid(false);
}

This is the code for the file import:

protected void btnImport_OnClick(object obj, System.EventArgs args)
{
    lblFileName.Text = "";
    
    string sInput = PhysicianFile.PostedFile.FileName;
    sInput.Trim();

    if (sInput != "")
    {

        try
        {

            DateTime dt = new DateTime();
            dt = DateTime.Now;

            string sExt = PhysicianFile.PostedFile.FileName.Substring(
                   PhysicianFile.PostedFile.FileName.LastIndexOf("."));
            string sFileName = "PhysicianFile" + 
                   sessionUtil.sessionGet("hospitalID").ToString() + sExt;
            sFileName = sFileName.Replace("/", "-");
            sFileName = sFileName.Replace(":", "-");

            string sPath = "\\\\" + 
              System.Configuration.ConfigurationManager.AppSettings["DBSERVER"] + 
              "\\import\\hospData\\" + sFileName;
            int nLength = PhysicianFile.PostedFile.ContentLength;
            sExt = sExt.ToLower();

            if (nLength < 999999999)
            {
                PhysicianFile.PostedFile.SaveAs(sPath);

                dbPhysician physician = new dbPhysician();
                string message = physician.ImportPhysician(sFileName, sPath);

                if (message != "")
                {
                    lblEntryStatus.Text = 
                      "Error during file Load: " + message;
                }
                else
                {
                    lblEntryStatus.Text = "File loaded successfully";
                }


                FillPhysicianInGrid(true);

                lblFileName.Text = PhysicianFile.PostedFile.FileName;
            }

        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
    }
}

Here is the dbPhysician.cs code that handles the database calls for Inserting, Updating, and Deleting the physicians:

public void Insert(string FName, string MName, string LName, 
                   string HPhysicianID, string Suffix)
{
    // Write your own Insert statement blocks 
    SqlParameter[] parameters = 
    { 
        new SqlParameter("@HospitalID", SqlDbType.Int),
        new SqlParameter("@HospitalPhysicianID", SqlDbType.VarChar, 50),
        new SqlParameter("@FirstName", SqlDbType.VarChar, 50),
        new SqlParameter("@MiddleName", SqlDbType.VarChar, 50),
        new SqlParameter("@LastName", SqlDbType.VarChar, 50),
        new SqlParameter("@Suffix", SqlDbType.VarChar, 10),
    };

    int i = 0;
    parameters[i++].Value = sessionUtil.sessionGet("hospitalID");
    parameters[i++].Value = HPhysicianID;
    parameters[i++].Value = FName;
    parameters[i++].Value = MName;
    parameters[i++].Value = LName;
    parameters[i++].Value = Suffix;

    int n;
    RunProcedure("InsertPhysician", parameters, out n, false);
}

public DataTable Fetch()
{
    SqlParameter[] parameters = 
            { 
                new SqlParameter("@HospitalID", SqlDbType.Int)
            };

    parameters[0].Value = sessionUtil.sessionGet("HospitalID");

    DataTable dt = ((DataSet)(RunProcedure("getPhysicianList", 
                              parameters, "temp"))).Tables[0];

    return dt;
}

public void Update(int PhysicianID, string FName, string MName, 
            string LName, string HPhysicianID, string Suffix)
{
    // Write your own Update statement blocks. 
    // Write your own Insert statement blocks 
    SqlParameter[] parameters = 
    { 
        new SqlParameter("@PhysicianID", SqlDbType.Int),
        new SqlParameter("@HospitalPhysicianID", SqlDbType.VarChar, 50),
        new SqlParameter("@FirstName", SqlDbType.VarChar, 50),
        new SqlParameter("@MiddleName", SqlDbType.VarChar, 50),
        new SqlParameter("@LastName", SqlDbType.VarChar, 50),
        new SqlParameter("@Suffix", SqlDbType.VarChar, 10),
    };

    int i = 0;
    parameters[i++].Value = PhysicianID;
    parameters[i++].Value = HPhysicianID;
    parameters[i++].Value = FName;
    parameters[i++].Value = MName;
    parameters[i++].Value = LName;
    parameters[i++].Value = Suffix;

    int n;
    RunProcedure("UpdatePhysician", parameters, out n, false);


}

public void Delete(int PhysicianID)
{
    // Write your own Insert statement blocks 
    SqlParameter[] parameters = 
    { 
        new SqlParameter("@PhysicianID", SqlDbType.Int),
    };

    int i = 0;
    parameters[i++].Value = PhysicianID;

    int n;
    RunProcedure("DeletePhysician", parameters, out n, false); ;
}

The following code handles the file import by checking for required fields in the file and displaying an error if anything is null. I've also added a check to see if any of the user entered physician IDs already exist in the table. (We need this field to be unique across hospitals.)

public int IsHospitalPhysicianID(string HospitalPhysicianID)
{

    SqlParameter[] parameters = 
            { 
                new SqlParameter("@HospitalPhysicianID", SqlDbType.Int, 4),
                new SqlParameter("@HospitalID", SqlDbType.Int, 4),
            };

    int i = 0;
    parameters[i++].Value = HospitalPhysicianID;
    parameters[i++].Value = sessionUtil.sessionGet("hospitalID");

    DataSet ds = RunProcedure("IsHospitalPhysician", parameters, "temp");

    if (ds.Tables[0].Rows.Count > 0)
    {
        return Parsers.intParse(ds.Tables[0].Rows[0]["IsExisting"].ToString(), -1);
    }

    return 0;

}


public string ImportPhysician(string ImportFileName, string ImportPath)
{
    int result = 0;
    string message = "";

    OleDbConnection Importconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
                "Data Source=" + 
                System.IO.Path.GetDirectoryName(ImportPath + ImportFileName) + 
                ";" + "Extended Properties=\"text;HDR=No;FMT=Delimited\"");



    //Read all data into a data table
    OleDbCommand oCmd = new OleDbCommand("SELECT " + 
      sessionUtil.sessionGet("HospitalID") + 
      " as HospitalID, * FROM [" + ImportFileName + "]", Importconn);
        
    Importconn.Open();

    OleDbDataReader oDR = oCmd.ExecuteReader();

    while (oDR.Read())
    {
        if (oDR.IsDBNull(1))
        {
            result = -1;
            message = "Physician ID(s) is/are missing in the file";
            break;
        }
        if (oDR.IsDBNull(4))
        {
            result = -1;
            message = "Last Names(s) is/are missing in the file";
            break;
        }


        if (IsHospitalPhysicianID(oDR[1].ToString()) == 1)
        {
            result = -1;
            message = "Physician ID(s) already exist in the Physician Table. " + 
                      "Duplicates are not allowed.";
            break;
        }

    }

     if (result < 0)
     {
         Importconn.Close();
         Importconn = null;
         return message;
     }

    oDR.Close();
    oDR = oCmd.ExecuteReader();

    if ((oDR.FieldCount == 6))
    {

        
        // create the SqlBulkCopy object
        using (SqlBulkCopy sBC = new SqlBulkCopy(Connection.ConnectionString, 
                                     SqlBulkCopyOptions.CheckConstraints))
        {
            // set the destination table name
            sBC.DestinationTableName = "tPhysician";
            sBC.ColumnMappings.Add(0, 1);
            sBC.ColumnMappings.Add(1, 2);
            sBC.ColumnMappings.Add(2, 3);
            sBC.ColumnMappings.Add(3, 4);
            sBC.ColumnMappings.Add(4, 5);
            sBC.ColumnMappings.Add(5, 6);

            // Write from the source to the destination.
            try
            {
                // write the data
                sBC.WriteToServer(oDR);
            }
            catch (Exception ex)
            {
                result = -1;
                message = ex.Message;
            }
            finally
            {
                // Close the SqlDataReader. The SqlBulkCopy
                // object is automatically closed at the end
                // of the using block.
                oDR.Close();
                oDR = null;
            }
        }
        
        Importconn.Close();
        Importconn = null;
    }
    else
    {
        Importconn.Close();
        Importconn = null;
        return message;
    }
    return message;
}

History

• Compiled 6.8.2009.