Using Temporary Data in ASP.NET GridView
Walkthrough: how to build a test page with a GridView that uses only temporary data in an ASP.NET application
If you want to run through this walkthrough, please follow the guide on setting up our test environment.
Experience Level - Intermediate
Author
So you may have come across the need to use a DataGrid, but not wanted to continually update your database with every change made.
Luckily you can store all of this data in session add/remove rows, and pass it between the Client and Server without touching the database.
I'm not going to delve to much into the science, there are articles on MSDN for things like that.First of all, load a new webform and you will need to add a DropDownList, GridView, and two buttons, one as a pretend upload to DB and another to add the user to our GridView.
If you are using the ClaytabaseAcademy App from the link above, add a new folder called Pages by right clicking the ClaytabaseAcademy item in the Solution Explorer on Visual Studio, and then add a page called TemporaryData.aspx
HTML
<div> <asp:DropDownList ID="UserAvailable" runat="server" Width="200px"> <asp:ListItem Text="Gavin Clayton" Value="1"></asp:ListItem> <asp:ListItem Text="Sai Gangu" Value="2"></asp:ListItem> <asp:ListItem Text="Chester Copperpot" Value="3"></asp:ListItem> </asp:DropDownList> <asp:Button ID="AddUser" runat="server" Text="Add User" /></div><div> <asp:GridView ID="UsersForSignOffList" runat="server" AutoGenerateColumns="False" DataKeyNames="ID" GridLines="None" BorderStyle="None" CssClass="DocsGrid"> <AlternatingRowStyle CssClass="alt" /> <Columns> <asp:BoundField DataField="UserName" HeaderText="Name" SortExpression="UserName" /> <asp:CommandField ShowDeleteButton="True"> <ItemStyle Width="125px" /> </asp:CommandField> </Columns> <HeaderStyle BackColor="#CCCCCC" /> <RowStyle CssClass="Grid" /> </asp:GridView></div><div> <asp:Button ID="UploadTable" runat="server" Text="Upload" /></div>Author
First we declare the table, we will call this on page load
VB
Private Function CreateTable() As DataTable 'Add a user column Dim dt As DataTable = New DataTable Dim column As DataColumn column = New DataColumn() column.DataType = System.Type.GetType("System.Int32") column.ColumnName = "UserID" column.ReadOnly = False column.Unique = True dt.Columns.Add(column) 'add a user name column column = New DataColumn() column.DataType = System.Type.GetType("System.String") column.ColumnName = "UserName" column.ReadOnly = False column.Unique = False dt.Columns.Add(column) 'Add a unique column with its own unique id (for delete function) column = New DataColumn() column.DataType = System.Type.GetType("System.Int32") column.ColumnName = "ID" column.ReadOnly = False column.Unique = True column.AutoIncrement = True column.AutoIncrementSeed = 1 dt.Columns.Add(column) 'add primary key (first key) on column ID Dim PrimaryKeyColumns(0) As DataColumn PrimaryKeyColumns(0) = dt.Columns("ID") dt.PrimaryKey = PrimaryKeyColumns Return dt End FunctionAuthor
Now we add a function to add new values to our temporary table and return it to us
VB
Private Function AddDataToTable(ByVal UserID As Int32, ByVal UserName As String, ByVal myTable As DataTable) As DataTable Try Dim row As DataRow row = myTable.NewRow() row("UserID") = UserID row("UserName") = UserName myTable.Rows.Add(row) Return myTable Catch Return myTable End Try End FunctionAuthor
Next we have a sub to handle the AddUser click, which will call the function above and return the temporary table to session and bing the data to our table again.
VB
Protected Sub Add_Click(sender As Object, e As System.EventArgs) Handles AddUser.Click AddDataToTable(UserAvailable.Items.FindByValue(UserAvailable.SelectedValue).Value, UserAvailable.Items.FindByValue(UserAvailable.SelectedValue).Text.ToString, CType(Session("myDatatable"), DataTable)) UsersForSignOffList.DataSource = (CType(Session("myDatatable"), DataTable)).DefaultView UsersForSignOffList.DataBind() End SubAuthor
We have added the ability for users to delete rows, so we need to add this function which will remove the required row and return the table.
VB
Private Function DelDataFromTable(ByVal RowID As Int32, ByVal myTable As DataTable) As DataTable Dim r As DataRow = myTable.Rows.Find(RowID) myTable.Rows.Remove(r) Return myTable End FunctionAuthor
Now we need a Sub bound to the RowDeleting command on the UserTable, which will call the Delete function and re-bind our data.
VB
Protected Sub UsersForSignOffList_RowDeleting(sender As Object, e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles UsersForSignOffList.RowDeleting Dim Id As Integer = e.Keys(0).ToString DelDataFromTable(Id, CType(Session("myDatatable"), DataTable)) UsersForSignOffList.DataSource = (CType(Session("myDatatable"), DataTable)).DefaultView UsersForSignOffList.DataBind() End SubAuthor
So we have put in place the functions and processes to update the table from user input and return them, so now we need to handle the final input and output from our table. To do this we are going to bind a sub to the Pre-Render event of the page and call some of the functions above, and another that will process the users into in this case a dummy table.
VB
Private Sub Page_Load() Handles Me.PreRender If Not IsPostBack Then GetUsers() Dim mydt = New DataTable() mydt = CreateTable() Session("myDatatable") = mydt 'AddDataToTable(UserID, UserName, CType(Session("myDatatable"), DataTable)) ' If adding a default user UsersForSignOffList.DataSource = (CType(Session("myDatatable"), DataTable)).DefaultView UsersForSignOffList.DataBind() End If End Sub Private Sub AddUsersToTable() Handles UploadTable.Click Dim AddUser As New SqlCommand 'AddUser.Connection = con 'You will also need to open and close your connection in here Dim dt As DataTable = CType(Session("myDatatable"), DataTable) Dim dr As DataRow For i = 0 To dt.Rows.Count - 1 dr = dt.Rows(i) 'AddUser.CommandText = "INSERT INTO LinkedUsers(UserID) SELECT " + dr.Item(0).ToString() 'AddUser.ExecuteNonQuery() Next End SubAuthor
The only part missing from above is the facility to bind the UsersAvailable dropdown to a database (we have done this manually), using a dummy SQL command I have shown this below, however for the page commented it out.
VB
Private Sub GetUsers() 'Dim com As New SqlCommand("SELECT * FROM Users", con) 'Dim tr = com.ExecuteReader 'UserAvailable.DataSource = tr 'UserAvailable.DataTextField = "UserName" 'UserAvailable.DataValueField = "UserID" 'UserAvailable.DataBind() 'tr.Close() End SubAuthor
I've always found it easiest to learn by simply having a play, so try it out on your machines and see how far you can take it. The only thing to remember here is that if there is going to be a huge amount of data it may be easier to just bind to the database, as this should keep the size of your page smaller and use less memory on the server.
Website design by Claytabase
This is a section of code that has been modified from Ousia Content Management System code, one of the quickest and most optimised systems on the market, part of our website design services.
