Tuesday, December 13, 2011

Correct way to populate a Gridview using ADO.Net 4.0

There is absolutely nothing worse than having to rewrite code that another developer has written.  If you use a SQLDataSource control to populate data in your application, you seriously need to read this article.

By using ADO.Net (correctly) you are dividing your programming layers which makes your application not only modular but easier to read.  This is not including the security considerations that go along with using ADO.Net.  I do realize that the SQLDataSource control is apart of ADO.Net, however think of it like the AOL of the internet or the iPad/iPhone of the smartphones – It’s simply for beginners, the Kindergarten version of the real thing.

So to start out, we need to add a GridView to our application (this article assumes you know how to start VS2010, and create a Web Project).  You can either type the code (for familiarity) or you can go to the toolbox and drag it onto the web form.

This is the gridview that I came up with:

Code Snippet

  1. <asp:GridView ID="gdvMessageBoard" runat="server" AutoGenerateColumns="false" CssClass="GridTable"
  2. DataKeyNames="MessageID" EmptyDataText="There are no messages currently in the system."
  3. AllowPaging="true" PageSize="10">
  4. <PagerStyle ForeColor="Black" />
  5. <Columns>
  6. <asp:TemplateField HeaderText="Edit Delete">
  7. <ItemTemplate>
  8. <asp:LinkButton ID="btnGridEdit" Text="Edit" runat="server" CommandName="Edit" ForeColor="Black" />&nbsp;&nbsp;
  9. <asp:LinkButton ID="btnDelete" Text="Delete" runat="server" CommandName="Delete"
  10. ForeColor="Black" />
  11. <asp:ConfirmButtonExtender ID="cbeGridDelete" runat="server" TargetControlID="btnDelete"
  12. ConfirmText="Are you sure you wish to delete this message?">
  13. </asp:ConfirmButtonExtender>
  14. </ItemTemplate>
  15. </asp:TemplateField>
  16. <asp:BoundField DataField="MessageID" HeaderText="Message ID" />
  17. <asp:BoundField DataField="CreateDate" DataFormatString="{0:M/d/yyyy}" HeaderText="Creation Date" />
  18. <asp:BoundField DataField="StartDate" DataFormatString="{0:M/d/yyyy}" HeaderText="Start Date" />
  19. <asp:BoundField DataField="MessageSubject" HeaderText="Subject" />
  20. </Columns>
  21. </asp:GridView>


So here you can see some bound fields.  I added a ConfirmButtonExtender which is apart of the AjaxToolkit.  Really nothing mysterious here, just tell it (TargetControlID) who you want to confirm.

You will also notice that I am using the MessageID as my DataKey.  This is the Primary Key that I setup in SQL.  So obviously I would use it as the key here.  You can also use multiple fields as a datakey to make a record unique, but that is beyond the scope of this article.

So now that we have that done, we need to work to get data to populate the gridview.  You will need to start another instance of VS2010.  File | New Project | Class Library

You can call this Library whatever you want but it is important to note WHERE you put the project.  Also to note is whatever you name the project becomes the base for the namespace of the project.  Therefore: if I name my project TheDotNetters.GridViewTest and I have a class of PopulateGridView, I will have to use TheDotNetters.GridViewTest.PopulateGridView in order to gain access to the methods and properties of the class.

Since we are going to need two Libraries (one for the data layer, the other for the business layer I will name this Library “TheDotNetters.Data” Press the Ok button.

You are given a default class (Microsoft in its infiinite wisdom thought this would be a good idea – NOPE!)  So just right mouse click it in the Solution Explorer and delete it.  Now right click your solution name (at the top of the solution explorer) and click Add New Item.  You want to add a class in the dialog that pops up.  Name your class something that has to do with the GridView.  In my case since it is a GridView for the Message Board, I will name it MessageBoard.  Click Ok and then we are now at the nitty gritty.  You should have your class open with the class declaration and an End Class statement.

First order of business is what do we need in order to connect to a database.  We need to import the namespaces that allow us to connect right (Just shake your head)?  Ok.. So there are thousands of namespaces that make up the framework.  Which ones do we need:

First things first, we need to make a reference to the System.Web namespace so that we can import the classes.  This will allow us to connect to the Web.Config of the site from the Library.  So right click your solution name and then click Add Reference.  From there, scroll down to System.Web and click Add.  Once it’s added you can close the dialog.

Code Snippet

  1. Imports System.Data
  2. Imports System.Data.SqlClient
  3. Imports System.Web.Configuration.WebConfigurationManager

Let’s work bottom-2-top.  The System.Web.Configuration.WebConfigurationManager gets us connected to the Web.Config.  This is where we will get our connection string (very important).
SqlClient gives us access to our SqlDataReader (If we so choose to use one).
Data gives us access to DataSets (WHOOP!! WHOOP!!) – This is how we will bind to the GridView.

So now we need to declare a public property of our class so that the business layer can retrieve our dataset (or reader).  Right under our class declaration, add the dataset property:

Code Snippet

  1. Public Class MessageBoard
  2. Public Property ds As DataSet = New DataSet()

We now have a property of the class that can be referenced to the outside.  Now we simply make our method public so the business layer can access it.

Code Snippet

  1. Public Sub getAllMessages()
  2. Dim cn As SqlConnection = New SqlConnection(ConnectionStrings("intranet").ToString())
  3. 'Dim cmd As New SqlCommand("dbo.getAllMessageBoard", cn)
  4. Dim adapt As SqlDataAdapter = New SqlDataAdapter("dbo.getAllMessageBoard", cn)
  5. 'cmd.CommandType = CommandType.StoredProcedure
  6. Try
  7.             cn.Open()
  8.             adapt.Fill(ds, "Messages")
  9. Catch ex As Exception
  10. Finally
  11.             cn.Close()
  12. End Try
  13. End Sub

Ok, the first line is our method declaration.  A simple Sub.
The second line we are defining which connectionString to use in the web.Config.  In this case it is a connectionString named “intranet”.
Then we declare a sqlCommand and execute a SP (Stored Procedure) from SQL. 
This article assumes you know how to write SPs in SQL. 
We define the commandtype as a SP

We open our Try block, then open the connection, and then fill our adapter.
The adapt.Fill is the variable for the adapt and a fill command.  (ds, “Messages”) is our dataset that we declared as a property and then we are naming the first table in the set (in this case the only table in the set).
We complete the method with a Catch/Finally block. 
NOTE: It is not best practices to use a Try/Catch/Finally block on every single access to the database as this creates enormous amounts of overhead.  It is best practice to let the Exception percolate up to the calling methods.

Save the class and then Right Click your solution name and Build (This makes the .dll file located in your bin/debug folder).  Now open another instance of VS2010 (so we can build the business layer).

Once there create another project as a Class Library type and call it TheDotNetters.Business.
Delete the (completely annoying) default Class1 and add a class (see above for steps).  Call the class the same name that you called it in the Data Layer.  Now we need to reference the Data Layer (.dll) that we just created.  So right click your solution name and then click Add Reference.  From there click the tab Browse.  Browse to the folder where you put your project and then bin/debug and you should find your .dll.

From here all we need is to create a single method to access the data:

Code Snippet

  1. Public Function getAllMessages() As DataSet
  2. Dim MessageData As TheDotNetters.Data.MessageBoard = New TheDotNetters.Data.MessageBoard
  3.         MessageData.getAllMessages()
  4.         getAllMessages = MessageData.ds
  5.         MessageData.ds.Dispose()
  6. Return getAllMessages
  7. End Function


Ok, here on the first line we declare our method and give a DataSet type.  Then we create a variable that represents our Data Layer and then instantiate it with the New statement.  Once we have our variable we make a call to our method on the Data Layer to retrieve the data from the database (The Sub we created).  We set our Business Layer method to the dataset at the Data Layer and then dispose of the Data Layer dataset (for memory leakage purposes).  Finally we return our method (and thus the DataSet to the calling method on the presentation layer (or in our website)).

So now right click your solution name and build the business layer.  Click back to your website and now we have to add our reference to our Business Library.  Right click your solution name and then click Add Reference.  Click the Browse tab and browse to your BUSINESS layer .dll.
If done correctly you should now see BOTH your Business and Data references in your bin folder in your site.  Now in the code-behind of your Presentation Layer you need to bind the DataSet to your GridView (In this case it is on the event Page_Load):

Code Snippet

  1. Dim Messages As TheDotNetters.Business.MessageBoardDetails = New TheDotNetters.Business.MessageBoardDetails
  2.         gdvMessages.DataSource = Messages.getAllMessages()
  3.         gdvMessages.DataBind()


Notice that my messages variable has the complete namespace to my Business Layer.  This is called Strongly-Typed Referencing or Strongly-Typed Namespacing.  Then we set the DataSource property of our GridView to our DataSet and then we issue the Bind command.

NOTE: It should be noted that if you try to change the code in your library after you have built it, you must do it in the following order: Update the code, Build the solution, then in the website solution right click the reference and then click Update Reference. 

Also, notice the order in which we built the application.  If your application uses SQL you should ALWAYS start at the database.  ALWAYS, ALWAYS, ALWAYS!

The correct order would be to create your table/view, then your stored procedure, set applicable access, create your data layer, business layer, connect it to your presentation layer.

Finally, to close the example we need to add a connectionString to our web.config (remember?). 


Code Snippet

  1. <configuration>
  2. <appSettings>
  3. ………. Removed for clarity ………………..
  4. </appSettings>
  5. <connectionStrings>
  6. <add name="intranet" connectionString="Server=ServerNameHere;DataBase=Intranet;user id=UserIDHere;password=PasswordHere" providerName="System.Data.SqlClient"/>
  7. </connectionStrings>
  8. <system.web>


Thus, we can now connect to the database and retrieve values. 

Happy .Netting – Saleh…

No comments:

Post a Comment