.NET, Using Temporary Data in Gridview

Build a test Page with a Gridview using only temporary data in ASP.NET

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.

I have run this all on my system, so the code should copy and paste straight in, try adding each section so you can see how you build it up.

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="Mervin Pereira" 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>
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 Function
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 Int32ByVal UserName As StringByVal myTable As DataTableAs 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 Function
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.EventArgsHandles AddUser.Click
AddDataToTable(UserAvailable.Items.FindByValue(UserAvailable.SelectedValue).Value, UserAvailable.Items.FindByValue(UserAvailable.SelectedValue).Text.ToString, CType(Session("myDatatable"),DataTable))
UserTable.DataSource = (CType(Session("myDatatable"), DataTable)).DefaultView
UserTable.DataBind()
End
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 Int32ByVal myTable As DataTableAs DataTable
Dim r As DataRow = myTable.Rows.Find(RowID)
myTable.Rows.Remove(r)
Return myTable
End Function
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 AsSystem.Web.UI.WebControls.GridViewDeleteEventArgsHandles UserTable.RowDeleting
Dim Id As Integer = e.Keys(0).ToString
DelDataFromTable(Id, CType(Session("myDatatable"), DataTable))
UserTable.DataSource = (CType(Session("myDatatable"), DataTable)).DefaultView
UserTable.DataBind()
End Sub
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

'Dim UserID As String 'If adding a default user
'Dim UserName As String 'If adding a default user
'Dim con As New SqlConnection({yourconnection}) 'Handle our SQL Connection here
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 Sub
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 Sub
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.
Ousia Logo