Seven things you should know about ASP.Net GridView

Read this and it’ll save you tons of grief later

… Where I attempt to define best practice for using ASP.Net GridView control

1. Use nested SELECT statements instead of JOIN statements so that UPDATE, INSERT queries get generated automatically

   SELECT
   ProductID, SupplierID,
     (SELECT Name
      FROM Supplier
      WHERE
      Supplier.SupplierID = Product.SupplierID)
     AS SUPPLIERNAME
   FROM PRODUCT

instead of

   SELECT
   ProductID, SupplierID,
   SUPPLIER.NAME AS SUPPLIERNAME
   FROM PRODUCT
   INNER JOIN Supplier
   ON Supplier.SupplierID = Product.SupplierID

2. BigInt columns in SQL Server are evil. Especially if you can’t avoid JOINs, and when you have to create your own UPDATE query in TableAdapter. Be aware Visual Studio incorrectly types your parameter as Int32 when your column is BigInt (Int64). You need to change this in your Update query’s Parameters property. Parameter names should be in the following form: @ORIGINAL_SUPPLIERID (If it is a primary key, or in the DataKeys list), or @SUPPLIERNAME. By the way, this parameter gets clobbered every time you touch the query.

3. When creating your own UPDATE statements, additional join column has to be included in the parameter list. In the #1 example, you should add a criteria

   WHERE (@SUPPLIERNAME Is Null
    OR @SUPPLIERNAME Is Not Null)

   You should also set the parameter to allow DBNull if the column could be null. Visual Studio isn’t smart enough to infer the parameter type from your table schema.

   If you do not wish to clutter your SQL Query using the approach above, then you can manually add @SUPPLIERNAME to the UPDATE query’s parameter list. However, this risks getting clobbered if you ever reconfigure your query using the XSD designer

4. If Foreign Key columns are set to invisible in the GridView then set DataKeysFields property to include their names. Otherwise, your update queries will fail. Hidden columns no longer post back data to the server because these invisible columns aren’t sent to the browser client ViewState at all – as a security measure.
5. If you set DataFormatString, you must set HtmlEncode=’False’. Otherwise, GridView will use yourObject.toString(), apply HtmlEncoding , then attempt to apply your format specification (Which wouldn’t work if you are dealing with dates or numbers).
6. If you set DataFormatString, you probably wish to set ApplyFormatInEditMode="True"

References

• Raj Kaimal – DataFormatString
• GridViewGuy – Accessing Invisible Columns