28 noviembre 2009

Using a GridView only with code in a SharePoint (2007) WebPart, or in asp.net

(Without using SmartPart)

Using a GridView binded to a (Sql)DataSource is pretty easy in a asp.net application where we can drag and drop the components onto the designer, and follow the related wizards. However, this is not that easy in a SharePoint WebPart, as we have to do everything programmatically.

My objective was creating a grid in a WebPart in which one of the columns was editable, with a drop down list. I started playing with the WebPart, but as the problem was how to use the components by code I switched to a asp.net application, to make it work. What I am presenting here is how to create a GridView linked to a SqlDataSource by code. Moving this to a WebPart is trivial.

First of all the scenario:

  • We are going to create a GridView.
  • Linked to a SqlDataSource
  • We are going to use Northwind database.
  • We will present the Products table, only two fields for simplicity:
    • ProductId: readonly
    • SupplierId: editable with a combo

To better illustrate the example below you have two screenshots. The first one is the GridView showing all the values. The second one is the GridView in editing mode, with the drop down list on the supplier column.

image image

As I told you to make it easy I started creating an asp.net application. As we want to do everything by code I just added a simple Panel to dinamically add the GridView inside afterwards.

    1 <%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>


    3 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


    5 <html xmlns="http://www.w3.org/1999/xhtml">

    6 <head runat="server">

    7     <title></title>

    8 </head>

    9 <body>

   10     <form id="form1" runat="server">

   11         <asp:Panel ID="Panel1" runat="server"/>

   12     </form>

   13 </body>

   14 </html>

In the code behind is where we find the interesting part. Here you have the using statements I needed.

    1 using System;

    2 using System.Collections.Generic;

    3 using System.Linq;

    4 using System.Web;

    5 using System.Web.UI;

    6 using System.Web.UI.WebControls;

    7 using System.Data;

    8 using System.Data.SqlClient;

    9 using System.Collections.Specialized;



This is the Page definition. In case of a WebPart this would be a child class of the asp.net WebPart class. We will need to use both a SqlDataSource and a GridView in many events, so we declare them globally and we create them.

To bind data to a GridView there are many other ways, but SqlDataSource just streamlines all the operations of selecting and modifying data to the database.

Please do not take into account I didn’t follow any naming conventions and some good practices were just ignored –I just created it to discover how to make it work.



   11 public partial class _Default : System.Web.UI.Page

   12 {

   13     SqlDataSource sqlSource = new SqlDataSource();

   14     GridView datagrid = new GridView();




According to MSDN documentation the event to create controls is PreInit, so we will configure the GridView and SqlDataSource properties here. I was not sure if the Init method would be suitable to do this too; if someone wants to post his/her opinion it will be appreciated. In a WebPart the equivalent to this event is OnPreInit –just write down override inside the class definition and Intellisense will show you the event signature.

First of all we create the SqlDataSource. Some tips to avoid weird errors, and annotations:

  • Remember to add the SqlDataSource control to the Controls matrix.
  • Obviously, substitute the connection string with a suitable one for you, as well as the select and update commands. Remember to add the parameters if you change them.
  • UpdateCommand, DeleteCommand and InsertCommand are actually optional. In this example I just implemented UpdateCommand, because it was the only operation (appart from selecting) that I needed.
  • Notice that we set an ID of the control.


   17     protected void Page_PreInit(object sender, EventArgs e)

   18     {

   19         sqlSource.ConnectionString = "Data Source=MUNTANER\\sqlexpress;Initial Catalog=Northwind;Integrated Security=True";

   20         sqlSource.SelectCommand = "SELECT [ProductID], [SupplierID] FROM [Products]";

   21         sqlSource.UpdateCommand = "UPDATE [Products] SET [SupplierID] = @SupplierID WHERE [ProductID] = @ProductID";

   22         //DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @ProductID"

   23         //InsertCommand="INSERT INTO [Products] ([SupplierID]) VALUES (@SupplierID)"

   24         sqlSource.UpdateParameters.Add(new Parameter("supplierid", System.TypeCode.Int32));

   25         sqlSource.UpdateParameters.Add(new Parameter("productid", System.TypeCode.Int32));

   26         sqlSource.ID = "sqlSource";

   27         Controls.Add(sqlSource);



After configuring the SqlDataSource we configure the GridView. A GridView is composed of a sequence of fields (columns). We could make the GridView to create them automatically, but then we could not use a dropdownlist for the Supplier field (instead I think it always present a Textbox). Therefore we set the attribute AutoGenerateColumns to false and we create:

  • One CommandField, the column with the edit button.
  • One BoundField, the column with the ID. A boundField is the default field type. It just displays information and it presents a textbox to edit it. In this case we set it readonly, as we do not want to edit it.
  • One TemplateField. If we want to use custom controls in a column we have to use this kind of field. A template field has templates for some usages of the field, for example a template when it is presented without editing (Item template) and a template when it is in edit mode (EditItemTemplate). There are more templates to be customized, such as Header and Footer templates. Here we find one of the tricky aspects we we use GridView in code: we have to create a template field, which means we have to create a class following a special interface. We will see it after.
  • Some configurations of the GridView I used:
    • As I told you: AutoGenerateColumns must be false.
    • DataKeyNames: this is the primary key of the table. I think it should be mandatory, but I didn’t try to run the example without setting this property.
    • AllowSorting: optional; it creates a link on the headers to sort the columns.
    • Be very careful on this attribute: DataSourceID. We use it to bind with the datasource. There is something very weird on this: I tried to set the property DataSource  with the variable sqlSource and do a DataBind (which is for me the normal way) but it didn’t work. But setting it with the ID it does work. I do not understand why, if anyone has the solution it will be appreciated.

As I told you before remember to add the GridView to the Controls matrix.


   29         // Columna 0

   30         CommandField buttons = new CommandField();

   31         buttons.ShowEditButton = true;

   32         datagrid.Columns.Add(buttons);


   34         // Columna 1

   35         BoundField productIdField = new BoundField();

   36         productIdField.HeaderText = "Product";

   37         productIdField.DataField = "productid";

   38         productIdField.SortExpression = "productid";

   39         productIdField.InsertVisible = false;

   40         productIdField.ReadOnly = true;

   41         datagrid.Columns.Add(productIdField);


   43         // Columna 2   

   44         TemplateField comboField = new TemplateField();

   45         comboField.HeaderText = "Supplier";

   46         comboField.SortExpression = "supplierid";

   47         comboField.ItemTemplate = new ComboTemplate(ListItemType.Item, "supplierid");

   48         comboField.EditItemTemplate = new ComboTemplate(ListItemType.EditItem, "supplierid");        

   49         datagrid.Columns.Add(comboField);


   51         datagrid.AutoGenerateColumns = false;

   52         datagrid.DataKeyNames = new string[] { "productid" };

   53         datagrid.AllowSorting = true;

   54         datagrid.DataSourceID = "sqlSource";    

   55         Panel1.Controls.Add(datagrid);

   56     }


This is the template class. One of the tricky things I had to discover is that it has to implement IBindableTemplate. Be careful as in MSDN there is an example with ITemplate, which works fine to show information but it is not suitable to return back information when the user changes it in editing mode.

Most important to understand about this class is:

  • There is a method called InstantiateIn (automatically created by Visual Studio when the interface is created). Here is where we are going to create the control in Item mode, Edit mode or others. In our example in Item mode we will create a simple literal, and in edit mode a dropdownlist.
  • ExtractValues method is where we push back the user changes in edit mode.
  • We facilitate the usage of the templates by storing the template type (Item, Edit, other) and the column name which relates the information (in the database, in our case). This information is initialized in the constructor of the class.

Here you can see the class definition, the class members and the constructor. The DropDownList must be a class member as it is used in some events.



   58     public class ComboTemplate : IBindableTemplate

   59     {

   60         ListItemType templateType;

   61         string columnName;

   62         DropDownList ddl = new DropDownList();



   65         public ComboTemplate(ListItemType type, string colname)

   66         {

   67             templateType = type;

   68             columnName = colname;

   69         }



Maybe you should read this part after you have understood instantiateIn. Here is where we create the data binding between the literal, in item mode, and the database information.

To do this we use the DataBinding event. This event occurs when the binding should be done. To be sincere I copied and pasted this code from a MSDN example of DataBinding, and it worked. I guess what the code does: it stores the database value by using a DataBinder object and evaluating the data of the container, and taken the given column… but I do not understand fully the details, so I can not really explain you (again if someone knows…).

What you should care in this method is the type of control you use for assigning the value. If you use a Literal control its ok; if you instead use another control (such a Label) you should cast the sender to a Label type (or the appropriate one for you). Maybe you should change as well the lc.Text line accordingly.


   71         void lc_DataBinding(object sender, EventArgs e)

   72         {

   73             Literal lc = (Literal)sender;

   74             GridViewRow container = (GridViewRow)lc.NamingContainer;

   75             object dataValue = DataBinder.Eval(container.DataItem, columnName);

   76             if (dataValue != DBNull.Value)

   77             {

   78                 lc.Text = dataValue.ToString();

   79             }

   80         }       



ExtractValues is the method to push back the changed information to the SqlDataSource. It gets the value and assigns to a newly created dictionary (the method expects a hashtable to be returned).


Be careful if you do not use a DropDownList to change the 88th line. In this line you should assign the data that the user has edited.


   82         #region IBindableTemplate Members


   84         System.Collections.Specialized.IOrderedDictionary IBindableTemplate.ExtractValues(Control container)

   85         {

   86             OrderedDictionary dict = new OrderedDictionary();


   88             string value = ddl.SelectedValue.ToString();

   89             dict.Add(columnName, value);


   91             return dict;

   92         }


   94         #endregion


   96         #region ITemplate Members



InstantiateIn is the method that creates the controls. As we designed the class to allow many templates to be created, it checks which template should be created and it creates a control according to it.


The only interesting ones for this example are:

  • Item template: it creates a Literal control. To databind this literal control DataBinding event must be implemented (see upwards). Remember to add it to the container Controls array.
  • EditItem: it creates a DropDownList control. When we edit our combo should show us a list of suppliers, which actually is not the same datasource as our GridView (products). Here a new SqlDataSource with a select query for suppliers should be done, but with the aim of reducing the amount of code the same datasource as before is used. If you try this example you will see that the dropdown show a lot of suppliers, and repetitions of them, as it actually queries the column suppliers of the product table. But as I told you the aim of this was just learning about how to use it.


   98         public void InstantiateIn(System.Web.UI.Control container)

   99         {

  100             Literal lc = new Literal();

  101             switch (templateType)

  102             {

  103                 case ListItemType.Header:

  104                     lc.Text = "<B>" + columnName + "</B>";

  105                     container.Controls.Add(lc);

  106                     break;

  107                 case ListItemType.Item:

  108                     lc.DataBinding += new EventHandler(lc_DataBinding);

  109                     container.Controls.Add(lc);

  110                     break;

  111                 case ListItemType.EditItem:

  112                     ddl.DataSourceID = "sqlSource";

  113                     ddl.DataTextField = "supplierid";

  114                     ddl.DataValueField = "supplierid";

  115                     container.Controls.Add(ddl);

  116                     break;

  117                 case ListItemType.Footer:

  118                     lc.Text = "<I>" + columnName + "</I>";

  119                     container.Controls.Add(lc);

  120                     break;

  121             }

  122         }       


  124         #endregion

  125     }



That’s all, what it comes now is not necessary for the example.


I also did a trial with a TextBox template; I just posted it just in case it is useful for someone.


  127     public class TexBoxTemplate : IBindableTemplate

  128     {

  129         ListItemType templateType;

  130         string columnName;

  131         TextBox tb = new TextBox();



  134         public TexBoxTemplate(ListItemType type, string colname)

  135         {

  136             templateType = type;

  137             columnName = colname;

  138         }


  140         void lc_DataBinding(object sender, EventArgs e)

  141         {

  142             Literal lc = (Literal)sender;

  143             GridViewRow container = (GridViewRow)lc.NamingContainer;

  144             object dataValue = DataBinder.Eval(container.DataItem, columnName);

  145             if (dataValue != DBNull.Value)

  146             {

  147                 lc.Text = dataValue.ToString();

  148             }

  149         }


  151         void tb_DataBinding(object sender, EventArgs e)

  152         {

  153             TextBox lc = (TextBox)sender;

  154             GridViewRow container = (GridViewRow)lc.NamingContainer;

  155             object dataValue = DataBinder.Eval(container.DataItem, columnName);

  156             if (dataValue != DBNull.Value)

  157             {

  158                 lc.Text = dataValue.ToString();

  159             }

  160         }


  162         #region IBindableTemplate Members


  164         System.Collections.Specialized.IOrderedDictionary IBindableTemplate.ExtractValues(Control container)

  165         {

  166             OrderedDictionary dict = new OrderedDictionary();


  168             string value = tb.Text;

  169             dict.Add(columnName, value);


  171             return dict;

  172         }


  174         #endregion


  176         #region ITemplate Members


  178         public void InstantiateIn(System.Web.UI.Control container)

  179         {

  180             Literal lc = new Literal();

  181             switch (templateType)

  182             {

  183                 case ListItemType.Header:

  184                     lc.Text = "<B>" + columnName + "</B>";

  185                     container.Controls.Add(lc);

  186                     break;

  187                 case ListItemType.Item:

  188                     lc.DataBinding += new EventHandler(lc_DataBinding);

  189                     container.Controls.Add(lc);

  190                     break;

  191                 case ListItemType.EditItem:                   

  192                     tb.DataBinding += new EventHandler(tb_DataBinding);

  193                     container.Controls.Add(tb);

  194                     break;

  195                 case ListItemType.Footer:

  196                     lc.Text = "<I>" + columnName + "</I>";

  197                     container.Controls.Add(lc);

  198                     break;

  199             }

  200         }


  202         #endregion

  203     }


  205 }

I hope this helps to anyone that needs to use GridView programmatically in a WebPart or asp.net application.