DataSet's DataTable's and TableAdapter's Oh My!


Here is my situation.  I am using VB as my programming language.
I am creating a part of a website that will be the admin section.  I want the admin to be able to add/edit data in a set of tables that normal users can't.  There are 20 lookup tables that I use in the website that I want the admin to be able to manage (i.e. add, edit, delete, insert).  I don't want to create 20 separate web pages (one for each table).  I have one page that has links to all the tables.  Each link has a "table" querystring variable associated with it.  When the admin clicks on the link it will send the user to the GridView page.  The GridView page will get the "table" querystring variable, then load a GridView based on that variable.  I have created the Data Access Layer with DataTables and TableAdapters setup for the 20 Lookup Tables.
Here is the code that I have so far.  The main part that I cannot seem to figure out is how to choose the appropriate DataTable based on the "table" querystring variable.  I would rather not have to create a huge Case statement.
Imports dsPART_CONVTableAdapters   'Imports the Table Adapters class I created in the DLL
Imports System.Data
Partial Class managing_lookup
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim strTableName As String
        'Sets strTableName equal to the passed in querystring variable table 
        strTableName = Request.QueryString("table")
        LoadGrid(strTableName)
        If Not IsPostBack Then
            AddColumnsToGrid(strTableName)
        End If
       
    End Sub
    Protected Sub LoadGrid(ByVal strTableName As String)
        With Me.ObjectDataSource1
            .SelectMethod = "GetData"           'All of my TableAdapters Select Statements are called GetData
            .DeleteMethod = "Delete"
            .InsertMethod = "Insert"
            .UpdateMethod = "Update"
            .OldValuesParameterFormatString = "Original_{0}"
        End With
        'Set Type Name
        Me.ObjectDataSource1.TypeName = "dsPart_CONVTableAdapters." & strTableName & "TableAdapter"
        'Update Parameters
        SetUpdateParameters(strTableName)
        'Insert Parameters
        SetInsertParameters(strTableName)
        'Delete Parameters
        Me.ObjectDataSource1.DeleteParameters.Add("Original_{0}", TypeCode.Int32)
    End Sub
    Protected Sub SetUpdateParameters(ByVal strTableName As String)
        Dim strDataTableName As String = "dsPart_CONV." & strTableName & "DataTable"
        Dim datTable As New DataTable
        'Would Like to set datTable equal to what is in strDataTableName
        
        'Need to find a way to set datTable to the DataTable's set in the DLL
        For Each col As DataColumn In datTable.Columns
            Me.ObjectDataSource1.InsertParameters.Add(col.ColumnName, col.DataType.ToString) 'Do I Need Data Type
        Next
        Me.ObjectDataSource1.InsertParameters.Add("Original_{0}", TypeCode.Int32)
    End Sub
    Protected Sub SetInsertParameters(ByVal strTableName As String)
        'same as set update parameters find way to dynamically set datTable based on strTableName
        Dim strDataTableName As String
        strDataTableName = "dsPart_CONV." & strTableName & "DataTable"
        'Sets DataTable equal to passed strTableName
        Dim datTable As New DataTable
        datTable.TableName = strTableName
        For Each col As DataColumn In datTable.Columns
            Me.ObjectDataSource1.InsertParameters.Add(col.Caption, col.DataType.ToString)
        Next
    End Sub
    Protected Sub AddColumnsToGrid(ByVal strTableName As String)
        'same as set update parameters find way to dynamically set datTable based on strTableName
        Dim strDataTableName As String
        strDataTableName = "dsPart_CONV." & strTableName & "DataTable"
        'Sets DataTable equal to passed strTableName
        Dim datTable As New DataTable
        datTable.TableName = strTableName
        With GridView1
            .DataSourceID = "ObjectDataSource1"
            .AutoGenerateColumns = False
            .DataKeyNames = New String() {"ID"}
            .AllowPaging = True
            .AllowSorting = True
            .PageSize = 10
        End With
        Dim bf As New BoundField
        For Each col As DataColumn In datTable.Columns
            With bf
                .HeaderText = col.ColumnName
                .DataField = col.ColumnName
                .SortExpression = col.ColumnName
            End With
            Me.GridView1.Columns.Add(bf)
        Next
       
        Dim cmdField As New CommandField
        With cmdField
            .ButtonType = ButtonType.Button
            .ShowCancelButton = True
            .ShowEditButton = True
            .ShowDeleteButton = True
        End With
    End Sub
End Class
 
0
shepujw
5/30/2007 2:35:32 PM
๐Ÿ“ asp.net.object-datasource
๐Ÿ“ƒ 16182 articles.
โญ 0 followers.

๐Ÿ’ฌ 2 Replies
๐Ÿ‘๏ธโ€๐Ÿ—จ๏ธ 2007 Views



Hi,
To query table according to the table name and bind, I suggest your TableAdapter take the query string as the table name, just as you do it in AddColumnsToGrid() method.
You can pass the table name to the lower level to make it query from the desired table and then bind it to the grid. However, the TableAdapter wizard might not allow you to do this, so you have to customize it. Add some custom methods to the TableAdapter to wrap the GetData() method.
Here are some articles for your reference.
http://msdn2.microsoft.com/en-us/library/ms233697(VS.80).aspx
HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Sincerely,
Kevin Yu
Microsoft Online Community Support

Please remember to click โ€œMark as Answerโ€ on the post that helps you, and to click โ€œMark as Not Answerโ€ if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
0
Kevin
6/4/2007 3:18:48 AM

I have modified the code and everything works now.  I do not understand your recommendation:  "I suggest your TableAdapter take the query string as the table name, just as you do it in the AddColumnsToGrid() Method".  Could your provide me with a very short working example?
Here is the code that works.  I had to modify some of the bound field code and add a CASE statement to pull in the correct DataTable.  I wish I wouldn't have to use the table adapter and I could just select the correct one based on the "table" querystring variable.
Imports dsPART_CONVTableAdapters
Imports System.Data
Partial Class managing_lookup
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim strTableName As String
        strTableName = Request.QueryString("table")
        LoadGrid(strTableName)
        If Not IsPostBack Then
            AddColumnsToGrid(strTableName)
        End If
       
    End Sub
    Protected Sub LoadGrid(ByVal strTableName As String)
        With Me.ObjectDataSource1
            .SelectMethod = "GetData"  'Need to change all table adapters to GetData
            .DeleteMethod = "Delete"
            .InsertMethod = "Insert"
            .UpdateMethod = "Update"
            .OldValuesParameterFormatString = "Original_{0}"
        End With
        'Set Type Name
        Me.ObjectDataSource1.TypeName = "dsPart_CONVTableAdapters." & strTableName & "TableAdapter"
        'Update Parameters
        SetUpdateParameters(strTableName)
        'Insert Parameters
        SetInsertParameters(strTableName)
        'Delete Parameters
        Me.ObjectDataSource1.DeleteParameters.Add("Original_{0}", TypeCode.Int32)
       
    End Sub
    Protected Sub SetUpdateParameters(ByVal strTableName As String)
        'Dim strDataTableName As String
        'strDataTableName = "dsPart_CONV." & strTableName & "DataTable"
        'Sets DataTable equal to passed strTableName
        Dim datTable As DataTable = GetDataTable(strTableName)
        For Each col As DataColumn In datTable.Columns
            Me.ObjectDataSource1.InsertParameters.Add(col.ColumnName, col.DataType.ToString) 'Do I Need Data Type
        Next
        Me.ObjectDataSource1.InsertParameters.Add("Original_{0}", TypeCode.Int32)
    End Sub
    Protected Sub SetInsertParameters(ByVal strTableName As String)
        'same as set update parameters find way to dynamically set datTable based on strTableName
        'Dim strDataTableName As String
        'strDataTableName = "dsPart_CONV." & strTableName & "DataTable"
        'Sets DataTable equal to passed strTableName
        Dim datTable As DataTable = GetDataTable(strTableName)
        For Each col As DataColumn In datTable.Columns
            Me.ObjectDataSource1.InsertParameters.Add(col.Caption, col.DataType.ToString)
        Next
    End Sub
    Protected Sub AddColumnsToGrid(ByVal strTableName As String)
        'same as set update parameters find way to dynamically set datTable based on strTableName
        'Dim strDataTableName As String
        'strDataTableName = "dsPart_CONV." & strTableName & "DataTable"
        'Sets DataTable equal to passed strTableName
        Dim datTable As DataTable = GetDataTable(strTableName)
        With GridView1
            .DataSourceID = "ObjectDataSource1"
            .AutoGenerateColumns = False
            .DataKeyNames = New String() {"ID"}
            .AllowPaging = True
            .AllowSorting = True
            .PageSize = 10
        End With
        For Each col As DataColumn In datTable.Columns
            Dim bf As New BoundField
            With bf
                .HeaderText = col.ColumnName
                .DataField = col.ColumnName
                .SortExpression = col.ColumnName
            End With
            Me.GridView1.Columns.Add(bf)
            bf = Nothing
        Next
       
        Dim cmdField As New CommandField
        With cmdField
            .ButtonType = ButtonType.Button
            .ShowCancelButton = True
            .ShowEditButton = True
            .ShowDeleteButton = True
        End With
        Me.GridView1.Columns.Add(cmdField)
    End Sub
    Public Function GetDataTable(ByVal strTableName As String) As DataTable
        Select Case strTableName
            Case "tblLookup_BsktSlsCat"
                GetDataTable = New dsPART_CONV.tblLookup_BsktSlsCatDataTable
            Case "tblLookup_BsktState"
                GetDataTable = New dsPART_CONV.tblLookup_BsktStateDataTable          
            '(around 15 more case statments showing all datatables continue on.  I deleted them because the code is so long.)
        End Select
    End Function
End Class
 
0
shepujw
6/4/2007 4:59:20 PM
Reply: