Filling ASP.NET GridView from DataTable

Recently I have been working on a legacy Web Forms app. It was decided that we should stick to the existing patterns as close as possible. I came across a situation where I was using a business object that consisted mostly of ID properties. When it came time to display those properties. Initially I thought of just adding all the properties I needed to the object, but it really cluttered the object up. This was older code so it was using private fields and full blown getter and setters with the properties. I couldn't be sure that the properties I needed would be used elsewhere so I opted for a data table.

When I was building out this demo I started to use VS 2012 with .NET 4.5. However when the GridView rendered, I could not figure out how to change the widths of the columns. The grid was really ugly looking. After spending too much time, I fell back to using VS 2010 with .NET 4.0. If anyone knows how to deal with this, please email csharpavacado@gmail.com so I can update this.

Here is a screenshot of the database tables, Account and Contacts:

 

 



I just spun up a new WebForms project and ripped out the initial Default.aspx code. Here is the code for the GridView in Default.aspx:

<%@ Page Title="Home Page"e; Language="C#&quot MasterPageFile=" ~/Site.Master" AutoEventWireUp="true" CodeBehind="Default.aspx.cs" Inherits="GVDemo2010._Defautlt&quot %>


<asp:Content ID="HeaderContent" runat="server"
   ContentPlaceHolderID="HeadContent">
</asp:Content>


<asp:ContentID="BodyContent" runat="server"
   ContentPlaceHolderID="MainContent">

<asp:GridView runat="server" AutoGenerateColumns="False" OnRowCancelingEdit="gvAccount_RowCancelingEdit" OnRowDataBound="gvAccount_RowDataBound"OnRowEditing="gvAccount_RowEditing"OnRowUpdating="gvAccount_RowUpdating"OnRowCommand="gvAccount_RowCommand"ShowFooter="True"EnableViewState="False"Width="900px">


  <Columns>
    <asp:TemplateField HeaderText="Account Name" HeaderStyle-HorizontalAlign="Left">
      <EditItemTemplate>
        <asp:Label runat="server"ID="lblAccountName"Text='<%# Bind("AccountName") %>'></asp:Label>
      </EditItemTemplate>
      <ItemTemplate>
        <asp:Label runat="server"ID="lblAccountName"Text='<%# Bind("AccountName") %>'></asp:Label>
      </ItemTemplate>
    </asp:TemplateField&gt

    <asp:TemplateField HeaderText="Primary First" HeaderStyle-HorizontalAlign="Left">
      <ItemTemplate>
        <asp:HiddenField runat="server" ID="primaryContactId" Value='<%# Bind("PrimaryContactId") %>'/>
      </ItemTemplate>
      <EditItemTemplate>
        <asp:TextBox runat="server" ID="txtPrimaryContactFirst" Text='<%# Bind("PrimaryContactFirstName") %>'></asp:TextBox>
      </EditItemTemplate>
      <FooterTemplate>
        <asp:TextBox runat="server" ID="txtNewPrimaryContactFirst"></asp:TextBox>
      </FooterTemplate>
      <ItemTemplate>
        <asp:Label runat="server" ID="lblPrimaryContactFirst" Text='<%# Bind("PrimaryContactFirstName") %>'></asp:Label>
      </ItemTemplate>
    </asp:TemplateField>

    <asp:TemplateField HeaderText="Primary Last" HeaderStyle-HorizontalAlign="Left">
      <EditItemTemplate>
        <asp:TextBox runat="server" ID="txtPrimaryContactLast" Text='<%# Bind("PrimaryContactLastName") %>'></asp:TextBox>
      </EditItemTemplate>
      <FooterTemplate>
        <asp:TextBox runat="server" ID="txtNewPrimaryContactLast"></asp:TextBox&gt
      </FooterTemplate>
      <ItemTemplate>
        <asp:Label runat="server" ID="lblPrimaryContactLast" Text='<%# Bind("PrimaryContactLastName") %>'></asp:Label>
    </asp:TemplateField>

    <asp:TemplateField HeaderText="Secondary First" HeaderStyle-HorizontalAlign="Left">
      <ItemTemplate>
        <asp:HiddenField runat="server" ID="secondaryContactId&quot Value='<%# Bind("SecondaryContactId") %>'/>
      </ItemTemplate>
      <EditItemTemplate>
        <asp:TextBox runat="server" ID="txtSecondaryContactFirst" Text='<%# Bind("SecondaryContactFirstName") %>'></asp:TextBox>
      </EditItemTemplate>
      <FooterTemplate>
        <asp:TextBox runat="server" ID="txtNewSecondaryContactFirst"></asp:TextBox>
      </FooterTemplate>
      <ItemTemplate>
        <asp:Label runat="server" ID="lblSecondaryContactFirst" Text='<%# Bind("SecondaryContactFirstName") %>'></asp:Label>
      &lt/ItemTemplate>

    <asp:TemplateField HeaderText="Secondary Last" HeaderStyle-HorizontalAlign="Left">
      <EditItemTemplate>
        <asp:TextBox runat="server" ID="txtSecondaryContactLast" Text='<%# Bind("SecondaryContactLastName") %>'></asp:TextBox<
      </EditItemTemplate>
      <FooterTemplate>
        <asp:TextBox runat="server" ID="txtNewSecondaryContactLast"></asp:TextBox>
      </FooterTemplate>
      <ItemTemplate>
        <asp:Label runat="server" ID="lblSecondaryContactLast" Text='<%# Bind("SecondaryContactLastName") %>'></asp:Label>
      </ItemTemplate>
    </asp:TemplateField>

    <asp:TemplateField HeaderText="Deleted" HeaderStyle-HorizontalAlign="Left">
      <EditItemTemplate>
        <asp:TextBox runat="server" ID="txtDeleted" Text='<%# Bind("Deleted") %>'></asp:TextBox&<
      </EditItemTemplate>
      <FooterTemplate>
        <asp:TextBox runat="server" ID="txtNewDeleted"></asp:TextBox>
      </FooterTemplate>
      <ItemTemplate>
        <asp:Label runat="server" ID="lblDeleted" Text='<%# Bind("Deleted") %>'></asp:Label>
      </ItemTemplate>
    </asp:TemplateField>

    <asp:TemplateField HeaderText="Edit" HeaderStyle-HorizontalAlign="Left">
      <EditItemTemplate>
        <asp:LinkButton runat="server" ID="lnkUpdate" CausesValidation="True" CommandName="Update" Text="Update"></asp:LinkButton>
        <asp:LinkButton runat="server" ID="lnkCancel" CausesValidation="False" CommandName="Cancel" Text="Cancel"></asp:LinkButton
      </EditItemTemplate>
      <FooterTemplate>
        <asp:LinkButton runat="server" ID="lnkAdd" CausesValidation="False" CommandName="Insert" Text="Insert"></asp:LinkButton>
      </FooterTemplate>
      <ItemTemplate>
        <asp:LinkButton runat="server" ID="lnkEdit" CausesValidation="False" CommandName="Edit" Text="Edit"></asp:LinkButton>
      </ItemTemplate>
    </asp:TemplateField>
  </Columns>


</asp:GridView>

</asp:Content>

 

The markup in the aspx file contains markup for in-line editing. I'm not going to go all the way on that, but we will do an edit. Now the code behind, Default.aspx.cs:

public partial class _Default : System.Web.UI.Page
{
   protected void Page_Load(object sender, EventArgs e)
   {
      FillGrid();
   }

   private void FillGrid()
   {
      // 1 is client Id, hardcode for demo purpose
      // db layout will be shown later
      DataTable dt = GetAccountDataTable(1);
      gvAccount.DataSource = dt;
      gvAccount.DataBind();
   } 

   protected void gvAccount_RowDataBound(object sender, GridViewRowEventArgs e)
   {
   }

   protected void gvAccount_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
   {
      gvAccount.EditIndex = -1;
      FillGrid();
   }

   protected void gvAccount_RowCommand(object sender, GridViewCommandEventArgs e)
   {
   }

   protected void gvAccount_RowEditing(object sender, GridViewEditEventArgs e)
   {
      gvAccount.EditIndex = e.NewEditIndex;
      FillGrid();
   }

   protected void gvAccount_RowUpdating(object sender, GridViewUpdateEventArgs e)
   {
      int index = e.RowIndex;
      DataTable dt = (DataTable) gvAccount.DataSource;
      int accountId = (int) dt.Rows[index].ItemArray.GetValue(0);
      int primaryContactId = (int) dt.Rows[index].ItemArray.GetValue(2);
      int secondaryContactId = (int) dt.Rows[index].ItemArray.GetValue(5);

      TextBox primaryContactFirst = 
         (TextBox) gvAccount.Rows[e.RowIndex].FindControl("txtPrimaryContactFirst");
      TextBox primaryContactLast = 
         (TextBox) gvAccount.Rows[e.RowIndex].FindControl("txtPrimaryContactLast");
      TextBox secondaryContactFirst = 
         (TextBox) gvAccount.Rows[e.RowIndex].FindControl("txtSecondaryContactFirst");
      TextBox secondaryContactLast = 
         (TextBox) gvAccount.Rows[e.RowIndex].FindControl("txtSecondaryContactLast");
      TextBox deleted = (TextBox) gvAccount.Rows[e.RowIndex].FindControl("txtDeleted");

      UpdateRecord(accountId, primaryContactId, primaryContactFirst.Text, primaryContactLast.Text, 
                   secondaryContactId, secondaryContactFirst.Text, secondaryContactLast.Text, deleted.Text)

      gvAccount.EditIndex = -1;
      FillGrid();
   }

   private static DataTable GetAccountDataTable(int clientId)
   {
      DataTable dt = new DataTable();
      dt.Columns.Add(new DataColumn("AccountId", typeof(Int32)));
      dt.Columns.Add(new DataColumn("AccountName", typeof(String)));
      dt.Columns.Add(new DataColumn("PrimaryContactId", typeof(Int32)));
      dt.Columns.Add(new DataColumn("PrimaryContactFirstName", typeof(String)));
      dt.Columns.Add(new DataColumn("PrimaryContactLastName", typeof(String)));
      dt.Columns.Add(new DataColumn("SecondaryContactId", typeof(Int32)));
      dt.Columns.Add(new DataColumn("SecondaryContactFirstName", typeof(String)));
      dt.Columns.Add(new DataColumn("SecondaryContactLastName", typeof(String)));
      dt.Columns.Add(new DataColumn("Deleted", typeof(Boolean)));

      string connectionString = 
         ConfigurationManager.ConnectionStrings["YourDbName"].ConnectionString;
      SqlConnection sqlConnection = new SqlConnection(connectionString);
      SqlCommand sqlCommand = 
         new SqlCommand("GetContactsByClientId", sqlConnection) { CommandType = CommandType.StoredProcedure };
      sqlCommand.Parameters.Add(new SqlParameter("@ClientId", clientId));
      SqlDataAdapter adapter = new SqlDataAdapter { SelectCommand = sqlCommand };
      adapter.Fill(dt);

      return dt;
   }

   private static void UpdateRecord(int accountId, int primaryContactId, string primaryFirst,
                                    string primaryLast, int secondaryContactId, string secondaryFirst,
                                    string secondaryLast, string deleted)
   {
      string connectionString = 
         ConfigurationManager.ConnectionStrings["YourDbName"].ConnectionString;
      SqlConnection sqlConnection = new SqlConnection(connectionString);

      SqlParameter[] parameters = new SqlParameter[8];
      parameters[0] = new SqlParameter("@AccountId", SqlDbType.Int, 0) { Value = accountId };
      parameters[1] = new SqlParameter("@PrimaryContactId", SqlDbType.Int, 0) { Value = primaryContactId };
      parameters[2] = new SqlParameter("@PrimaryContactFirst", SqlDbType.NVarChar, 50) {Value = primaryFirst};
      parameters[3] = new SqlParameter("@PrimaryContactLast", SqlDbType.NVarChar, 50) {Value = primaryLast};
      parameters[4] = new SqlParameter("@SecondaryContactId", SqlDbType.Int, 0) {Value = SecondaryContactId};
      parameters[5] = new SqlParameter("@SecondaryContactFirst", SqlDbType.NVarChar, 50) {Value = secondaryFirst};
      parameters[6] = new SqlParameter("@SecondaryContactLast", SqlDbType.NVarChar, 50) {Value = secondaryLast};
      parameters[7] = new SqlParameter("@Deleted", SqlDbType.Bit, 0) {Value = Convert.ToBoolean(deleted)};

      SqlCommand sqlCommand = 
         new SqlCommand ("UpdateContactInfo", sqlConnection) {CommandType = CommandType.StoredProcedure};
      sqlCommand.Parameters.AddRange(parameters);
      sqlConnection.Open();
      sqlCommand.ExecuteNonQuery();
      sqlConnection.Close();
   }
}

 

We have two stored procedures to write, first, dbo.GetContactsByClientId:

USE [YourDbName]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetContactsByClientId]
   @ClientId INT
AS
BEGIN
   SELECT account.AccountId, account.AccountName,
          c1.ContactId AS "PrimaryContactId', c1.FirstName AS "PrimaryContactFirstName",
          c1.LastName AS "PrimaryContactLastName", c2.ContactId AS "SecondaryContactId",
          c2.FirstName AS "SecondaryContactFirstName", c2.LastName AS "SecondaryContactLastName",
          account.ClientId, account.Deleted
   FROM Account account
   LEFT OUTER JOIN Contacts c1
   ON account.PrimaryContactId = c1.ContactId
   LEFT OUTER JOIN contacts c2
   ON account.SecondaryContactId = c2.ContactId
   WHERE account.ClientId = @ClientId
END

 

And the second proc, dbo.UpdateContactInfo

USE [YourDbName]
GO

SET ANSI_NULLS ON
GO
SET QUOTED IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UpdateContactInfo]
   @AccountId INT,
   @PrimaryContactId INT,
   @PrimaryContactFirst NVARCHAR(50),
   @PrimaryContactLast NVARCHAR(50),
   @SecondaryContactId INT,
   @SecondaryContactFirst NVARCHAR(50),
   @SecondaryContactLast NVARCHAR(50),
   @Deleted BIT

AS 
BEGIN
   SET NOCOUNT ON:

   UPDATE dbo.Contacts
   SET FirstName = @PrimaryContactFirst,
       LastName = @PrimaryContactLast
   FROM dbo.Contacts AS contact
   LEFT OUTER JOIN dbo.Account AS account
   ON contact.ContactId = account.PrimaryContactId
   WHERE account.ClientId = 1
   AND contact.ContactId = @PrimaryContactId

   UPDATE dbo.Contacts
   SET FirstName = @SecondaryContactFirst,
       LastName = @SecondaryContactLast
   FROM dbo.Contacts AS contact
   LEFT OUTER JOIN dbo.Account AS account
   ON contact.ContactId = account.SecondaryContactId
   WHERE account.ClientId = 1
   AND contact.ContactId = @SecondaryContactId

   UPDATE dbo.Account
   SET Deleted = @Deleted
   WHERE ClientId = 1
END

 

Below we see the grid upon page load, in edit mode, and edit complete:

GridView

Grid View Editing

Edit Complete

Entity Framework & StructureMap

Entity Framework and StructureMap, weird combo, yeah I know. But here is a "cute" little trick if you use Code First and roll your own entity config classes. I started with an empty solution, added a data project and a console project.

In the data project, a simple POCO:

public class AboutUs
{
   public virtual long DealerId { get; set; }
   public virtual short ImageOrder { get; set; }
   public virtual string Url { get; set; }
   public virtual string Alt { get; set; }
   public virtual string Size { get; set; }
}

 

Now let's set up the DbContext...

using System.Data.Entity;
using EF5Demo.Data.Entities;

public class EF5DemoContext : DbContext
{
   public EF5DemoContext()
   {
      this.Configuration.AutoDetectChangesEnabled = true;
      this.Configuration.ProxyCreationEnabled = true;
   }

   public DbSet AboutUs { get; set; }

   protected override void OnModelCreating(DbModelBuilder modelBuilder)
   {

   }
}

 

We'll fill out the OnModelCreating after we drop in StructureMap. For this little setup, StructureMap will be needed in both projects. In each project, right click on References, select Manage NuGet Packages. We just need the regular version of StructureMap.

 

 

 

Now to set up an interface and a concrete implementation that will be consumed by the entity config classes. First, the IEntityConfiguration interface:

using System.Data.Entity.ModelConfiguration.Configuration;

public interface IEntityConfiguration
{
   void AddConfiguration(ConfigurationRegistrar registrar);
}

 

The class that uses this interface is the link between StructureMap and the entities, the ContextConfiguration class:

 

using System.Collections.Generic;

public class ContextConfiguration
{
   public IEnumerable Configurations
   {
      get { return ObjectFactory.GetAllInstances(); }
   }
}

 

Let's roll the entity config class for the AboutUs POCO.This config class will implement the IEntityConfiguration interface:

 

using System.Data.Entity.ModelConfiguration;
using System.Data.Entity.ModelConfiguration.Configuration;
using System.ComponentModel.DataAnnotations.Schema;
using EF5Demo.Data.Entities;

public class AboutUsConfig : EntityTypeConfiguration, IEntityConfiguration
{
   public AboutUsConfig()
   {
      // composite PK
      HasKey(aui => aui.DealerId, aui.ImageOrder });
      // Identity column
      Property(aui => aui.DealerId).HasColumnName("DealerId").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
      Property(aui => aui.ImageOrder).HasColumnName("ImageOrder");
      Property(aui => aui.Url).HasColumnName("Url").IsRequired();
      Property(aui => aui.Alt).HasColumnName("Alt").IsRequired();
      Property(aui => aui.Size).HasColumnName("Size").IsRequired();
      ToTable("AboutUs");
   }

   public void AddConfiguration(ConfigurationRegistrar registrar)
   {
      registrar.Add(this);
   }
}

 

Let's return to the DbContext class and fill out the OnModelCreating method...

 

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
   ContextConfiguration ctxConfig = new ContextConfiguration();

   foreach (var config in ctxConfig.Configurations)
   {
      config.AddConfiguration(modelBuilder.Configurations);
   }
}

 

When the model gets created, the ContextConfiguration class gets created. The Configurations property looks for all instances of the IEntityConfiguration interface (which would be all the entity config classes) and adds them to the model builder, and thus, provide structure and validation to your db tables.

 

Let's see if it works, in the console project...

using StructureMap;
using EF5Demo.Data;

static void Main(string[] args)
{
   ObjectFactory.Initialize(x =>
   {
      {
         scan.TheCallingAssembly();
         scan.WithDefaultConventions();
         scan.Assembly("EF5Demo.Data");
         scan.AddAllTypesOf();
      };
   });

   ContextConfiguration configurations = 
      ObjectFactory.GetInstance();

   foreach (var configuration in configurations.Configurations)
   {
      Console.WriteLine(configuration);
   }
}

 

There we go, entity config classes have been picked up. Like I said, this is just a cute little trick. The benefit is that for every entity config class you add, you don't have to go back and add it to the model builder. Conversely, whenever you remove an entity you won't have to worry about removing the config from the model builder.

 

The reason for this blog is rather simple: how many times have you been experimenting in Visual Studio, only to look for a code snippet and you cannot remember where it is? I have too many projects and solutions that are just named something like "Test1", "EntityFrameworkTest". I figured firing up a blog would be a good place where I could store code snippets and find them easily.

I expect some people may email and "hey you could do it this way or that way". I'm not perfect and seeing things from another perspective is always a good thing. Plus if there is a better solution, I'm all ears. Who knows, maybe some of the articles posted may help someone else. As developers we spend countless hours combing the web and we all need help from time to time. Afterall, being a developer is a journey of constant learning.

A new year, time for some new experiences, new adventures, right?