Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0

June 22nd 2006 Update: We've now published a whole series of new data tutorials based on this origional post.  You can read all about it here.

 

One of my goals over the next few weeks is to publish a number of quick tutorial postings that walkthrough implementing common data binding design patterns using ASP.NET 2.0 (master details, filtering, sorting, paging, 2-way data-binding, editing, insertion, deletion, hierarchical data browsing, hierarchical drill-down, optimistic concurrency, etc, etc).

 

To help keep these samples shorter, and to help link them together, I’m going to use a common DAL (data access layer) implementation across the samples that is based on SQL Server’s Northwind sample database.

 

To build the DAL layer I decided to use the new DataSet Designer that is built-into Visual Web Developer (which you can download for free) as well as VS 2005, and which provides an easy way to create and encapsulate data access components within an application.  I’ve used it for a few samples lately, and have found it pretty useful and flexible.  I really like the fact that it enables me to avoid having to write tedious data access code, while still preserving full control over my SQL data logic (I also like the fact that it enables me to use both standard SQL statements as well as SPROCs).

 

One of the things that I’ve noticed is that there aren’t a lot of end-to-end tutorials that show off how to build and use a DAL with the designer (one exception is the great write-up by Brian Noyes – who includes samples of how to use it with SPROCs).  What I’ve tried to-do with the below set of tutorials is provide an end-to-end, step-by-step, walkthrough that covers all of the core concepts involved in creating and consuming a DAL built with the data designer. 

 

Using the data designer and ASP.NET 2.0 together, you should be able to create a core DAL implementation and build from scratch a data-driven UI web app on top of an existing database very quickly (~10-15 minutes to build an application from scratch that supports master/details filtering along with data paging, sorting, insertion, and editing).

 

The final result of the below walkthroughs can be downloaded here.  This download includes all of the code samples as well as the DAL built up below.

 

I’ll then be posting lots of (much shorter! <g>) blog posting over the next few weeks that show off common data-UI patterns that use it.

 

Tutorial 1: Creating a Web Project and Connecting To the Database

 

To begin with, create an empty new ASP.NET Web Site Project (File->New Web Site) within Visual Web Developer.  We can then connect and load our Northwinds database in the IDE.  To-do this you can follow one of two approaches:

 

Option 1: Connect to a local or remote SQL 2000 or SQL 2005 database that has the Northwinds sample installed.  To-do this, go to the “Server Explorer” property window in Visual Web Developer (choose View->Server Explorer if it isn’t currently visible), right-click on the “Data Connections” node and choose to add a new database connection.  You can then walkthrough a wizard to pick your SQL database and load the Northwinds database in the IDE:

 

 

Option 2: If you don’t have a SQL 2000 or SQL 2005, you can alternatively use the free SQL 2005 Express Edition.  Download this .zip file containing the Northwinds.mdf database file, and copy it to the “App_Data” directory underneath your web site’s root directory.  Click the “Refresh” button in the solution explorer, and it will appear in the Solution Explorer:

 

 

SQL Express databases within the App_Data directory will automatically show-up as a listed item in the Server Explorer property window (note: I sometimes get a timeout error the very first time I expand a newly copied sql express database here – I think because it is generating the log file the first time it is accessed.  If this happens just click it again and it seems to immediately expands):

 

 

After the Northwinds database has been loaded using either Option #1 or Option #2 above, you can expand, view, edit, and add to any of the tables, stored procedures, views, triggers, etc with the database.  You can also use the query builder to test out and run queries against the database (or right-click on a table and choose “Show Table Data” to see all of it).

 

Tutorial 2: Launching the DataSet Designer

 

To create our Northwind DAL, right-click on the project node in the solution explorer, and select “Add New Item”.  Then select the “DataSet” option and name it “Northwind.xsd”:

 

 

This will prompt me as to whether I want to add it under the “App_Code” directory.  When I click “yes” it will bring up a data-design surface, and (if in a web project) automatically launch the “Create TableAdapter” wizard (if you are in a class library project you need to right click and choose “Add->Table Adapter to launch this wizard):

 

 

Tutorial 3: Creating our First Table Adapter

 

The “Create TableAdapter” wizard will first prompt me for the database to use, and provide a list of all database connections currently registered in Visual Web Developer’s server-explorer window (note: you can also create new connections from this wizard if you want). 

 

After I choose the Northwind database I want to use, it will prompt me for where to store the database connection-string.  By default it will avoid hard-coding it within your code, and will instead save it within the new <connectionStrings> section of your web.config file (or app.config file if you are in a Class Library Project).  Note that with .NET 2.0 you can now optionally encrypt configuration file values to keep them secure, as well as use the new ASP.NET 2.0 Property Page within the IIS GUI Admin Tool to change it later (ideal for administrators).  Pick a name for what you want to call it:

 

 

You can then choose how you want to configure the TableAdapter – you can use either SQL Statements that you embed within your DAL layer, or stored procedures (SPROCs) that you call from it:

 

 

For our first table adapter we’ll use a SQL Statement.  You can either type this in directly within the next window:

 

 

Or alternatively launch the query-builder from that wizard step to graphically construct it (one handy feature with it is the ability to execute test queries to validate results):

 

 

Once we’ve finished building and testing our query, we will return back to the “Enter a SQL Statement” page.  Before moving to the next page, we’ll want to click the “Advanced Options” button to verify what type of operations we want the designer to generate:

 

 

The above dialog shows the default settings that are set when you run the “Create TableAdapter” wizard from within a VS 2005 Web Site Project (these are the most common data patterns we typically see developer’s use with stateless web applications).  When you run the wizard from within a Class Library Project or from a Windows Client Project, the “Use Optimistic Concurrency” checkbox will be selected by default as well. 

 

For the purposes of this DAL walkthrough, we want to turn off optimistic concurrency (note: one of my later blog postings on optimistic concurrency will change that setting – but I will be showing straight updates first).

 

When we click next on the wizard again, we’ll be prompted for the method names we want to create in our DAL to run our Category SQL query.  There are two patterns of data usage that this data wizard can generate – one is the “Fill” pattern used with DataSets, that will generate a method that accepts a DataSet or DataTable parameter that we want to add category information to.  The second pattern of data usage is one where we will generate a method that will simply return a new DataTable containing our category information.  This later approach is the one we’ll be primarily using in all of our later tutorials, and is more typical with stateless web-applications.

 

In the wizard I’m going to name this DataTable method “GetAllSuppliers”.  I will also keep the “GenerateDBDirectMethods” checkbox to automatically create default Insert, Update and Delete methods based off of my Select query on the component:

 

 

The wizard will then list all of the DAL methods it will create as part of this wizard process for, and when I hit finish add them to our data designer:

 

 

What I now have is a strongly-typed DataAdapter class (by default named “NorthwindTableAdapters.SuppliersTableAdapter”) with a “GetAllSuppliers” method that I can use to execute a SQL query and obtain back a strongly-typed DataTable results class (by default named “Northwind.SuppliersDataTable”). 

 

I could use these objects to easily obtain all of the Suppliers information from the Northwind database and output it within a page like so:

Dim suppliersAdapter As New NorthwindTableAdapters.SuppliersTableAdapter

Dim suppliers As Northwind.SuppliersDataTable

Dim supplier As Northwind.SuppliersRow

 

suppliers = suppliersAdapter.GetAllSuppliers()

 

For Each supplier In suppliers

Response.Write("Supplier: " & supplier.CompanyName & "<br>")

Next

Notice that I don’t need to write any manual ADO.NET code, construct a SQL query, or manage connections at all in the above code – all of that is encapsulated by the SuppliersDataTable and SuppliersTableAdapter that we now have in our project.  Notice also how the SuppliersTableAdapter class is strongly typed – meaning I get intellisense and compilation checking on the “suppliersAdapter.GetSuppliers()” method.  I can also access each property returned in the Suppliers result in a strongly typed fashion (for example: supplier.CompanyName or supplier.SupplierID).

 

I could alternatively write the below .aspx page and associated .aspx.vb code-behind file to easily databind and output the results in a grid:

 

SuppliersTest2.aspx:

 

<%@ Page Language="VB" AutoEventWireup="false" EnableViewState="false" CodeFile="SupplierTest2.aspx.vb" Inherits="SuppliersTest2" %>

 

<html>

<head>

    <title>Simple Category Listing</title>

    <link href="StyleSheet.css" rel="stylesheet" type="text/css" />

</head>

<body>

    <form id="form1" runat="server">

 

        <div class="datatable">

         

            <h1>Simple Category Listing</h1>

       

            <asp:GridView ID="GridView1" runat="server">

            </asp:GridView>   

           

        </div>

   

    </form>

</body>

</html>

 

SuppliersTest2.aspx.vb:

Imports NorthwindTableAdapters

 

Partial Class SuppliersTest2

    Inherits System.Web.UI.Page

 

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

 

        Dim suppliersAdapter As New SuppliersTableAdapter

 

        GridView1.DataSource = suppliersAdapter.GetAllSuppliers()

        GridView1.DataBind()

 

    End Sub

 

End Class

Which then generates the below output at runtime:

 

 

Note that when using the new ASP.NET ObjectDataSource control we will be able to eliminate the need to write any code for the scenario above (which even now only has three lines), as well as to automatically enable paging, sorting and editing on the GridView without any page code needed.  I’ll cover how to-do this in a later blog posting on using this DAL.  My goal with the sample above was just to show how you could procedurally databind the GridView using techniques you might already be familiar with in ASP.NET 1.1.

 

Tutorial 4: Adding Parameterized Methods within Table Adapters

 

It is usually pretty rare in web applications to want to request all of the data within a database table.  More typically you’ll want to use SQL queries to retrieve only the subset of data that you need.  The data designer makes it super easy to create multiple strongly-typed data-methods within a DAL to help with the parameterized SQL operations.

 

To add one to our Suppliers object, right-click on it and choose “Add Query”:

 

 

Once again we can choose either a stored procedure, or a SQL statement.  For the SQL statement you can choose to return multiple rows, or just a single value (useful for things like SELECT Count(*) commands, or to retrieve a single value):

 

 

For this parameterized query I’m going to return multiple rows, and will be filtering the data by country value.  I can express a parameter value using a “@parametername” syntax within my SQL statement like so (note: there can be any number of parameters in the SQL statement):

 

 

I can then name this parameterized data method “GetSuppliersByCountry”:

 

 

And now I have two methods I can use within my SuppliersTableAdapter to get Supplier data (GetAllSuppliers and GetSuppliersByCountry).  Note that the designer is fully re-entrant, meaning you can at any point re-configure a data method (for example: to change the SQL statement, add/remove parameters, etc).  Just right click and select the “configure” method to-do so:

 

 

You can also use the designer to run any of the data methods.  Just right-click and select “Preview Data” to execute them and see the results (note how any parameters can be specified as part of this operation):

 

 

And in code I can now easily build a page that uses this new method like so:

 

SuppliersByCountry.aspx:

 

<%@ Page Language="VB" AutoEventWireup="false" EnableViewState="false" CodeFile="SuppliersByCountry.aspx.vb" Inherits="SuppliersByCountry" %>

 

<html>

<head runat="server">

    <title>Suppliers By Country Sample</title>

    <link href="StyleSheet.css" rel="stylesheet" type="text/css" />

</head>

<body>

    <form id="form1" runat="server">

             

        <h1>Suppliers By Country:</h1>

 

        <div>

            Select a country: <asp:TextBox ID="CountryTxt" runat="server" />

                              <asp:Button ID="SearchBtn" Text="Search" runat="Server" />           

        </div>

   

        <div class="datatable">

   

            <asp:GridView ID="GridView1" runat="server">

            </asp:GridView>   

       

        </div>      

       

    </form>

</body>

</html>

 

SuppliersByCountry.aspx.vb:

Imports NorthwindTableAdapters

 

Partial Class SuppliersByCountry

    Inherits System.Web.UI.Page

 

    Protected Sub SearchBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SearchBtn.Click

 

        Dim suppliersAdapter As New SuppliersTableAdapter

 

        GridView1.DataSource = suppliersAdapter.GetSuppliersByCountry(CountryTxt.Text)

        GridView1.DataBind()

 

    End Sub

 

End Class

Which will generate a Suppliers search page:

 

 

Note that I did not have to write any ADO.NET code, manually create parameter collections, manage connection objects etc.  I only had to write 3 lines of code total to build the above sample from scratch using the Visual Web Developer data and page designers.

 

Tutorial 5: Using Insert, Update, and Delete DBDirect Commands on TableAdapters

 

Because we kept the default “GeneratedDBDirect Methods” checkbox selected, the Create Table Adapter wizard automatically added default Insert, Update and Delete methods to the SuppliersTableAdapter.  You can see these, as well as edit/customize them further, by selecting the SuppliersTableAdapter object within the DataSet Designer and then looking at the property-grid (note: you must select the SuppliersTableAdapter heading to have these methods show up in the property grid – they won’t show up if you just select the Suppliers heading):

 

 

Using the “CommandText” property in the property-grid, you can pull up a query designer for each of the default statements and customize them:

 

 

You can also optionally add your own custom Insert/Update/Delete methods to each TableAdapter.  For example, if I wanted to add a custom Insert method that had the additional behavior of returning the new identity column value of a newly created Supplier row (which has an auto-increment property set for the primary key), I could do so by right-clicking on the SuppliersTableAdapter and choosing “New Query”:

 

 

I’ll then pick doing the INSERT command with a SQL statement, and choose to create an Insert:

 

 

The DataSet designer will then automatically suggest the below SQL statement for me (it looks at the SELECT statement you entered earlier to suggest a default INSERT statement):

 

 

Note that the “SELECT @@Identity” statement at the end will return the newly created primary key from the insert operation.  I could further customize the INSERT operation however I want.

 

I can then use the wizard to name the method “InsertSupplier” (or any other name I want).  The wizard will then add this method to the SuppliersAdapter.  The last step I’ll do (since I want to return the @@Identity value from the insert), is to change the type of the InsertSupplier method from “NonQuery” to “Scalar”:

 

 

You could then write the code below to add a new supplier, and then update the supplier values, and then delete the supplier within the Suppliers table:

Dim supplierAdapter As New NorthwindTableAdapters.SuppliersTableAdapter

 

Dim supplierId As Integer

 

supplierId = supplierAdapter.InsertSupplier("Microsoft" _

                                            , "ScottGu" _

                                            , "General Manager" _

                                            ,"One Microsoft Way" _

                                            , "Redmond" _

                                            , "USA" _

                                            , "98004" _

                                            , "425-555-1212")

 

supplierAdapter.Update("Microsoft" _

                       , "Someone Else" _

                       , "New title" _

                       , "New Address" _

                       , "New City" _

                       , "UK" _

                       , "New Zip" _

                       , "New Number" _

                       , supplierId)

 

supplierAdapter.Delete(supplierId)

Note that I did not have to write any ADO.NET code, manually create any parameter collections, or manage connection objects etc.  Because the data designer generates typed methods and DataTables, I’ll get both intellisense/compilation-checking as well as type validation within my DAL (so for example: if I try to pass an integer instead of a DateTime it would give me a compile error).

 

Tutorial 6: Using DataTables to Insert/Update/Delete Suppliers

 

Tutorial 5 above showed how to use data methods directly on the SuppliersTableAdapter to manipulate individual rows within our database.  Alternatively, developers can also work to perform these operations using the SuppliersDataTable and SuppliersDataRow objects.  These are particularly useful when adding/updating/deleting multiple rows at a time (these updates can optionally be batched to the database in one database call using this approach). 

 

The below example demonstrates how to retrieve all of the suppliers in the US with a single database call, then conditionally update some of the supplier’s zip-codes, and then add a new supplier.  We’ll then update the database with all of the additions/changes:

Dim supplierAdapter As New NorthwindTableAdapters.SuppliersTableAdapter

Dim suppliers As Northwind.SuppliersDataTable

Dim supplier As Northwind.SuppliersRow

 

' Obtain all Suppliers in the US

suppliers = supplierAdapter.GetSuppliersByCountry("USA")

 

' Loop through all suppliers and update any 98042 postcodes to 98004

For Each supplier In suppliers

 

If supplier.PostalCode = "98052" Then

            supplier.PostalCode = "98004"

End If

 

Next

 

' Create a New Supplier Just for Fun

supplier = suppliers.NewSuppliersRow()

 

' Set new data properties on supplier2 row

With supplier

 

.CompanyName = "Microsoft"

      .ContactName = "ScottGu"

.Address = "One Microsoft Way"

      .ContactTitle = "General Manager"

      .City = "Redmond"

      .PostalCode = "98052"

      .Country = "USA"

      .Phone = "425-555-1212"

 

End With

 

' Add New Supplier to SuppliersDataTable

suppliers.AddSuppliersRow(supplier)

 

' Update Database with all changes (updates + additions)

supplierAdapter.Update(suppliers)

Note that there are fancier ways to-do filtering (and sorting) operations that I’m not using above, but I wanted to keep this tutorial simple as opposed to overload it with too many concepts.

 

Tutorial 7: Putting the Northwinds DAL Together

 

The above tutorials hopefully provide the basic background needed to create DAL’s using the DataSet designer and then use them from code.

 

Using the above knowledge, you should be able to go off and quickly create the following strongly-typed TableAdapters with associated data methods pretty easily (note that the designer and query builder can be used to avoid having to manually write any of the SQL queries below – and it will auto-suggest subsequent queries for each adapter after you add the first one).

 

SuppliersTableAdapter:

 

GetAllSuppliers:

 

SELECT        SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Country, PostalCode, Phone

FROM           Suppliers

 

GetSuppliersByCountry:

 

SELECT        SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Country, PostalCode, Phone

FROM           Suppliers

WHERE        Country=@Country

 

GetSupplierBySupplierId

 

SELECT        SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Country, PostalCode, Phone

FROM           Suppliers

WHERE        SupplierId=@SupplierID

 

GetUniqueSupplierCountries

 

SELECT        DISTINCT Country

FROM           Suppliers

 

CategoriesTableAdapter:

 

GetAllCategories:

 

SELECT        CategoryID, CategoryName, Description

FROM           Categories

 

GetCategoryByCategoryId

 

SELECT        CategoryID, CategoryName, Description

FROM           Categories

WHERE        CategoryId=@CategoryId 

 

EmployeesTableAdapter:

 

GetAllEmployees:

 

SELECT        EmployeeID, LastName, FirstName, Title, HireDate, Country, ReportsTo

FROM           Employees

 

GetEmployeeByEmployeeID

 

SELECT        EmployeeID, LastName, FirstName, Title, HireDate, Country, ReportsTo

FROM           Employees

WHERE        EmployeeID=@EmployeeID

 

GetEmployeesByManager:

 

SELECT        EmployeeID, LastName, FirstName, Title, HireDate, Country, ReportsTo

FROM           Employees

WHERE        ReportsTo = @ReportsTo

 

ProductsTableAdapter:

 

GetAllProducts:

 

SELECT        ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued,

                    (SELECT Suppliers.CompanyName from Suppliers where Suppliers.SupplierId=Products.ProductId) as SupplierName

FROM           Products

 

GetProductsBySupplierId:

 

SELECT        ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued,

                    (SELECT Suppliers.CompanyName from Suppliers where Suppliers.SupplierId=Products.ProductId) as SupplierName

FROM           Products

WHERE        SupplierID=@SupplierID

 

GetProductsByCategoryId:

 

SELECT        ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued,

                    (SELECT Suppliers.CompanyName from Suppliers where Suppliers.SupplierId=Products.ProductId) as SupplierName

FROM           Products

WHERE        CategoryID=@ CategoryID

 

Most of the above TableAdapters are pretty straight-forward and simple.  The one that is a little more advanced is the ProductsTableAdapter.  Specifically, there in addition to retrieving the Products table columns I’m also retrieving the CompanyName from the Suppliers table that maps to the Product’s SupplierId column (it has a foreign key relationship to the Suppliers table).  The above SQL statement will add this as a read-only “SupplierName” column on our ProductsDataTable.

 

This will allow me to be more efficient from a database perspective when I want to build a list UI on top of my Products table, and save me from having to hit the database an extra time per-row to retrieve this value when displaying a product list on the site.  

 

The good news is that the data designer can still infer enough from the product adapter’s SQL statements to still automatically generate the correct INSERT, UPDATE and DELETE commands for the ProductsTableAdapter.  In cases where you are doing more advanced JOINS across multiple tables and merging results from multiple places, the data designer might not be able to generate these automatically.  The good news is that you can still define these manually within the designer (just click on the table-adapter, and then within its property grid choose to create a new “Insert” (or update or delete) command and define your own logic).

 

When finished, my Northwinds data design-surface looks like this (note how the designer will automatically map and detail foreign-key relationships):

 

 

When I hit save on the Northwinds.xsd file (where all of the adapters, definitions and relations are declaratively stored in an XML file), I’ll be able to program and data-bind against any of the objects defined within it.

 

Tutorial 8: Adding Custom Code to the DAL

 

One of the nice things about the data designer is that the .xsd file that declaratively stores all of the relevant definitions gets translated into either VB or C# code at compile and runtime.  This means that you can easily step through it within the debugger, and always understand “what is it doing for me under the covers”. 

 

To easily see what code has been generated by the data designer, just open the “Class View” property window in Visual Web Developer or VS 2005 (if it isn’t visible select the View->Class View menu item), and navigate to the respective namespaces and types to see the object model of them.  You can then right-click and choose “Browse Definition” to jump to the generated code for any method:

 

 

In addition to viewing the generated code, you can also add to it and extend it in many ways.  You can do this either by sub-classing the generated objects, or alternatively by using the new “Partial” keyword feature now supported by the .NET 2.0 language compilers.  The partial keyword enables you to add any method, property or events you want to the classes generated within the DAL, and they will be compiled together into a single generated type.  Because the partial class code you write lives in a separate file from the files generated by the designer, you do not have to worry about the designer ever clobbering or overwriting your code.

 

To see a simple example of how you could use this feature, I could easily add an additional “GetProducts()” method to each row of Suppliers within my Northwind DAL (allowing me to easily obtain the ProductDataTable for the specific Supplier I have a reference to).  Enabling this is as easy as adding a “SuppliersRow.vb” file in my app_code directory and adding this code to it:

Partial Public Class NorthWind

 

    Partial Public Class SuppliersRow

 

        Public Function GetProducts() As ProductsDataTable

 

            Dim productsAdapter As New NorthwindTableAdapters.ProductsTableAdapter

            Return productsAdapter.GetProductsBySupplier(Me.SupplierID)

 

        End Function

 

    End Class

 

End Class

This tells the VB compiler that I want to add a “GetProducts()” method to the SuppliersRow class (I’m using the “SupplierID” property on the supplier’s row to automatically figure out the subset of products by Supplier – note that I can write “Me.SupplierId” – since this method is part of the SuppliersRow class).

 

Once I add this I can then write this code to easily hierarchically list each product produced by each Supplier:

Dim suppliersAdapter As New NorthwindTableAdapters.SuppliersTableAdapter

Dim suppliers As Northwind.SuppliersDataTable

Dim supplier As Northwind.SuppliersRow

 

suppliers = suppliersAdapter.GetAllSuppliers()

 

For Each supplier In suppliers

 

Response.Write("Supplier: " & supplier.CompanyName & "<br>")

 

      Dim products As Northwind.ProductsDataTable

      Dim product As Northwind.ProductsRow

 

      products = supplier.GetProducts()

 

      For Each product In products

            Response.Write("------- Product: " & product.ProductName & "<br>")

      Next

 

Next

And I could easily build this page using a <asp:DataList> and <asp:Repeater> control to databind a list of suppliers, and then allow a user to click any of the suppliers to list a hierarchical drill-down of their products:

 

 

I’d do this by databinding the suppliers against the DataList:

 

Hierarchy.aspx.vb:

Protected Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender

 

Dim suppliersAdapter As New NorthwindTableAdapters.SuppliersTableAdapter

 

      DataList1.DataSource = suppliersAdapter.GetAllSuppliers()

      DataList1.DataBind()

 

End Sub

And then using the “Select” feature of DataList to have the selected item template hierarchically retrieve and display the products (this way only the selected supplier’s products are retrieved):

 

Hierarchy.aspx:

 

<h1>Supplier/Product Drilldown:</h1>

   

<p>Click a Supplier to List Its Products</p>

     

<asp:DataList ID="DataList1" runat="server">

       

<ItemTemplate>

            <asp:linkbutton ID="Details" CommandName="Select" runat="server"><%#Eval("CompanyName") %></asp:linkbutton>

</ItemTemplate>

       

      <SelectedItemTemplate>

           

            <div class="selectedsupplier">

                <strong><%#Eval("CompanyName") %></strong>

                <ul>

                <asp:Repeater ID="ProductsList" DataSource='<%# Container.DataItem.Row.GetProducts() %>' runat="server">

                     <ItemTemplate>

                         <li><%#Eval("ProductName")%></li>

                     </ItemTemplate>

                </asp:Repeater>

                </ul>

            </div>

           

</SelectedItemTemplate>

</asp:DataList>

 

Summary

 

Hopefully the above set of tutorials provides a useful (and somewhat exhaustive -- sorry for the length) walkthrough of the core features and capabilities you can easily take advantage of with the DataSet designer in Visual Web Developer and VS 2005 to build easy data access layers. 

 

Over the next few weeks I’m going to be posting many (short) blog posting that walkthrough using the DAL I built above to implement common web data patterns using ASP.NET 2.0.

 

Hope this helps,

 

Scott

 

170 Comments

  • Hi Scott



    Great sample. Something to the wish-list for your upcomming samples:



    The same DAL exposed by WebServices including reallife errorhandling.... and an ASP.NET application consuming it.... would that be something???

  • Scott,



    Excellent as usual!

  • Hi Henrik,



    I'll add that one to the list! :-)



    Thanks,



    Scott

  • another great tutorial thanks Scott, your tutorial are never long enough ;).



    I was wondering something, generating DAL methods inside the designer and inside the Dataset is convinient to build quick application, however I would love to see something where you could also choose to generate a separate DAL that takes in as a parameter the dataset structure instead of n parameters and where you could just map the different parameters to the dataset columns. I believe that this would generate a much more versatile architecture with passing &quot;messages&quot; to the DAL instead of params. What do you think ?

  • I'd recommend SCOPE_IDENTITY() rather than @@IDENTITY, unless something has changed with @@IDENTITY in SQL 2005.

  • Hi Scott great article.

    It would be nice to have the same example using ObjectDataSource. I know you showed it in other posts, but having a step-by-step article is really useful.

    A question, I tried the procedure above, but the dataset wizard generates only Insert ans Select Commands and not the Delete and Update. Any idea why ?

    Thanks

  • Thanks for this one!



    Still, I get to wonder what's the recommended way to deal

    with table joins. Always create SQL views? Use 'virtual'

    datatables that map to the query result instead of an underlying

    table?





  • Nevermind, I found the problem with Update and Delete. It was because I forgot to add a primary key to the table I'm using. The wizard didn't give any warning though. I think it is worth mentioning it in the article.

    Thanks again.

    Mike

  • Hi Tom,



    The TableAdapters generated actually generate 5 Update methods by default -- one that takes the single parameters (like the DBDirect approach I showed first), and then one that takes a DataSet, one that takes the DataTable, and then two that tae DataRows. So in terms of your question about passing DataSets as parameter types back to the DAL, the good news is that you are all set on that regard (my sample showing the second round of updates is actually using this approach -- where it is passing back the SupplierDataTable as an argument to the SupplierTableAdapter's Update() method).



    One nice thing is that because the TableAdapters are maked as partial classes as well, you can add additional methods to them if you want. For example, you could define your own class &quot;Supplier&quot; that was completely independent of any Datable/DataSet/DataRow classes (and just used fields or properties to store supplier data) and then define an Update method on the TableAdapter that took it as an argument.



    Hope this helps,



    Scott

  • Hi Mike,



    Pretty much all of the data samples I'll be doing next will show using the above DAL with the ObjectDataSource. I didn't want to introduce it in this article primarily to make clear that the above approach is not only appropriate for UI specific databinding scenarios.



    I think sometimes people assume when there are lots of designers and wizards that you can't write code too. I wanted to show some procedural examples so people felt comfortable that you can use all of these features in standalone code scenarios as well.



    Thanks,



    Scott

  • Great stuff - thanks Scott



    I will need to archive this one - very valuable.



    I didn't realize the typed dataset created a &lt;nameofdataset&gt;TableAdapters



  • Scott, I'm a fan, but I continue to think the web team outputs too much of this stuff versus more difficult material on n-tier and domain driven design (granted, you didn't say &quot;SqlDataSource&quot; anywhere).



    I'm not saying one is better than the other, and certainly in this simplified example this approach has a lot going for it, but overall the education hasn't been very balanced.

  • Great article! I have one issue when converting example to c#.



    in Hierarchy.aspx:

    Container.DataItem.Row.GetProducts()



    I get error saying object does not contain a definition for 'Row'



    any idea? thanks!

  • Hi Scott!



    Great content in your blog!!



    There are two things I'd really like to have as features for data in VS.Net:



    1. Is it possible to drag&amp;drop an existing stored procedure to the designer, so that it creates a strongly typed method? I mean a SP that does not SELECT a table, but just takes some nvarchars and ints and counts something and writes that back?



    This functionality seems to be much more basic than what the designer provides, yet it is missing!



    2. The datarows would be great business objects, e.g. if I want to edit a customer I could take the strongly typed data row and pass it around through remoting.

    But unfortunately, I don't think that this works: Datarows can only live withhin Datatables.



    I always wondered why this functionality is not existing in .NET! (Or is it?)

  • hello scott,



    isn't it just bad design to have typed datacontainer (dataset) and dataaccess (tableadapters) glued together?



    what about complex query-parameters instead of simple types?



    it seems, this designer-stuff is limited, or do i miss something?



    why there is no datasetdatasource anymore?



    regards

    pat

  • Worked like a charm !

    After reading this, I re-wrote an app I did a few weeks ago.

    This is an excellent tutorial. Please keep it coming.

    Thanks

    Logic

  • www.dotnetkicks.com trackback

  • Scott,



    Not putting you on the spot or anything, but how about doing at least one of these walkthroughs via screencast?



    By the way, great job on the walkthrough. The new dataset designer makes things alot cleaner and elegant than using previous versions. I'm probably going to start using them a little now.

  • Hi Karl,



    My goal with the upcoming data tutorials is going to be to show how to-do things using the ObjectDataSource and binding to a middle-tier layer. Although I'm going to use the DAL I created in this tutorial as the one I interact with, the concepts I'll walkthrough are pretty applicable to any data object model.



    In terms of what the best data model is to use, that is a bit of a hard one. My personal belief is that there isn't one &quot;perfect&quot; data story out there for every application. Instead, I think there *is* a perfect data story for each particular project and the development team building it -- and these vary depending on the exact scenario. Some projects/teams have very complex requirements, and so need a lot of depth and abstraction. Some projects/teams are smaller/simpler and a simpler/smaller abstraction might be better.



    What we are trying to-do in ASP.NET is make sure that we have a story that works for all data access models and implementations -- and let teams choose amongst themselves as to what exact implementation works best for their needs.



    Hope this helps,



    Scott

  • Hi Tyrone,



    Webcasts and videos are definitely something we have planned for the next month. Stay tuned! :-)



    Thanks,



    Scott

  • Hi Scott, great article.



    But I do have the similar question as Patrick does. Does it have to glue the typed dataset and table adapter together? It seems any table adapter select will create a typed dataset automatically?



    Any possible to extract the table adapter classes as seperate DAL class? And can I simply return a general dataset for select and binding to the datagridview? I think typed dataset is great use in update/insert, but sometime I only want to select and view, isn't it dataset is more general and easier and lighter?



    Thanks.







  • Hi Scott,



    Nice walkthru. Now all we need are some more advanced features in VS.NET that let me control the namespace for the TableAdapters and the ability to design DataSets and TableAdapters in seperate files... ;-)



    (I know you can delete the TableAdapter compartment in the designer but you cannot delete the DataSet compartment without the TableAdapter being deleted too.)



    Grtx,

    Marc

  • It's strange that if we bind ObjectDataSource to a gridview / detailview with column READONLY = TRUE or VISIBLE = FALSE, the data will not be passed to InputParameters in ObjectDataSource during updating event, which causes error during update. Is it a bug or I need to add a custom Update function to my DataAdapter?

  • Hi Scott!

    Even now i am see that it is made special for disigners who don't like to do the tedious job. You tried to show how it is easy to do new datatable provider and new methods for retrieving data by DataAdapter, but when i started to do the same your VS and WVD show me more fast way then i expected. I repeated all your examples with NorthWind version of database, but i did it by configuration datasource directly from disigner view. ALL WAS BEEN DONE without ANY MANUAL PROGRAMMING and FINGERS SYMPHONY. ALL WAS DONE BY MOUSE!!!!

    GREAT!

    Thank you for this article!

    Sincerely, LukCAD

  • Hi Obiwan,



    You can actually control the namespace of the adapters in a couple of ways. One is to use a Class Library project, and just set the namespace at the project level.



    Another trick with web projects is to just prefix a namespace on the Northwinds.xsd file itself. For example, if I named it &quot;Scotgu.Northwinds.xsd&quot; it would generate everything underneath the &quot;Scottgu&quot; namespace.



    Hope this helps,



    Scott

  • Hi Leo To,



    With regard to your question about the read-only Gridview column fields -- I'll be covering this in one of the later tutorials. For scenarios like this you can do one of two things:



    1) Add a custom update method that only updates the columns you want/need.



    2) Use the DataKeyNames property on GridView to add the non-visible/read-only column names (as a comma separate list -- so you can add any you want). This will cause the GridView to send these parameters to the ObjectDataSource as input params -- and let you use the default Update method (that has all the params listed).



    Hope this helps,



    Scott

  • Hi Van,



    You can extend the typed dataset pretty easily by adding a partial class to the project. This will then automatically get compiled with the code generated by the designer -- and avoid you having to worry about the designer stomping on your code. My last example above with the hierarchy shows a simple example of using a Partial class for this (it was adding a custom method to the SupplierRow). To add a custom property/method on the Typed DataSet itself just add a class like:



    Partial Public Class NorthWind



    Public Function SomeCustomFunction()



    End Function



    End Class



    This will then automatically be compiled with the Northwind dataset into a single type.



    Hope this helps,



    Scott

  • Hi Gozh2002,



    The nice thing about the approach I showed above is that I'm not actually using the DataSet itself -- instead I'm primarily just using the Typed TableAdapters, and the Typed DataTables they return (I don't acually ever put them into the DataSet).



    If you want an untyped DataTable, you can cast the returned DataTable as a generic DataTable. It has all the same semantics as an untyped datatable or dataset -- so you can use the untyped properties/methods/collections to manipulate it as well.



    Hope this helps,



    Scott

  • Scott,



    Is it possible to add new custom attributes directly to the .XSD source specifically for each column? As I indicated previously, I would like to store some additional information about each column in the table such as “category, description, additional caption, etc.” I would then like to be able to add the partial class extensions that you mentioned above that would expose these custom column attributes as properties of each table column in the typed dataset.



    Thanks,



    Van

  • Scott,



    I like the idea of being able to add my own code in the partial class. I tried adding a property there and it seemed to work well. However, I was not able to easily bind my property to the grid. I imagine that I could do it the hard way (using gridview eventhandlers) but I'd rather not. Is it possible to do this using a boundfield with an object datasource or something like that?



    Thanks,

    Andy

  • Thanks for the detailed example.



    I tried it in C#.



    I had to cast the supplierAdapter.InsertSupplier method with an (int) to avoid a compile error :-



    int supplierID = (int)supplierAdapter.InsertSupplier(...)





    However, when i ran it , the InsertSupplier query returns 0 as the SupplierID, though it seems to add the row to the table. Maybe i messed up a step somewhere.



    Great example.



    Chak.

  • Scott!

    I had the same error(i just read from Chakravarthy) when i tried to use InsertSupplier method by C#.

    Sincerely, LukCAD

  • Hi Luk/Charkravarthy,



    Did you change the return type of the InsertSupplier from NonQuery to Scalar? That could be one source of the problem (otherwise I think it will just return 0).



    If that doesn't fix it, can you tell me whether you are using the Northwinds database or another DB?



    Thanks,



    Scott

  • Hi Scott,



    I did change the return type to Scalar. Actually what happens is that when i run the C# example, i get a InvalidCastException (where i had used an (int)). When i opened the xsd, i found the relevant query's DBObjectType and DBObjectName were null and the return type was Object (not int).



    I downloaded the Northwind zip file you had uploaded.



    In the Add Query, I had some trouble adding the ';' to the end of the previous insert, and then adding the SELECT @@IDENTITY, and so tinkered with that - maybe that was the cause of some problem.



    In any case, appreciate the detailed, step by step approach to explaining.



    Chak.

  • Thank you for a great walkthrough!



    In all your table adapter methods you have fixed parameters, like GetSuppliersByCountry(@CountryID). Is it also possible to use them for user-defined queries (the user picks a number of criterias from a advaced query page)? The difference is that the WHERE clause must be generated dynamically.



    I have tried to extend the table adapter class, but I can't get hold of the original SelectCommand (and connection) without executing Fill first. And that's not very efficient.



    I have also tried to create a custom Fill method that would accept a SQL expression as the parameter, but the query fails because the adapter tries to convert it into a number.



    Thanks,

    Jens Erik

  • Scott,



    Excellent tutorial - thanks!



    On the subject of future tutorials - something that would be very helpful to me is a tutorial on doing a custom membership and role provider into the Northwind database schema. It is something I am wrestling with with in my own legacy schema now.





  • Scott,



    Still no joy on adding custom attributes to the .XSD. Nothing new appears in the generated code. Perhaps you could direct us/me to a reference sample I could download where this is possibly demonstrated.



    Sorry for being a pest!



    Van



  • Hi Van,



    Can you send me email directly (scottgu@microsoft.com) with a simple example of what you are trying to-do? I can then loop you in with someone on the data team to help.



    Thanks,



    Scott

  • Hi Chakravarthy,



    Did you get it working in the end with C#? If not, I can try and put together a sample showing how to make it work.



    Thanks,



    Scott

  • Hi Jens,



    You could add a custom method to the Adapter class (via the partial class mechanism), and then use the &quot;Adapter&quot; property within the method to get access to the raw data-adapter. This would then let you write arbitrary SQL statements.



    As an alternative approach, if the data size isn't too big, you could retrieve the data into a datatable, and then use a dataview class to add filter statements that filtered the data within the web-server. This works for small amounts of data (no more than a few hundred rows) -- although wouldn't scale well with thousands of rows.



    Hope this helps,



    Scott

  • Hi Scott!



    I tried putting the databinding of the suppliers against the DataList in the Page_Load-event instead of the Page_Prerender-event.



    This makes my page show the products of my first supplier, but when I press the second supplier I get an error: &quot;Failed to load viewstate&quot;.



    Could you please clarify why you use Page_Prerender and not Page_Load in this case? And also is there a rule of thumb that I should learn?



    Thanks,



    Lukas

  • Hi Lukas,



    I put the databinding code in the Page_PreRender method primarily because of the post-back that is happening when you select an item. If you always do the bind in the Pre_Load(), then you will run into a viewstate issue like you encountered above -- because the select event is firing *after* the page_load event, and so the previous strucure of the DataList won't be the same (since you just changed).



    One alternative approach would be to do the initial databind in page_load -- but to wrap it in a Page.IsPostBack == false check. That way you'll databind on the initial load, but not on post-backs. You could then handle the select event of the datalist -- and do a rebind when it it fires (this will then automaticlaly highlighted the selected row).



    Hope this helps,



    Scott

  • Dear Scott,

    Thanks for the great article, for the first time I was able to understand how build a DAL.



    However I have a question: Is it possible to do this using multiple parameters like the one in tutorial 3?



    As an example what I want to do is something like this: I have a SqlDatasource without any select command and in the code behind I construct the selectcommand based on the user choices, like:



    srtsql = “SELECT * FROM table1 where active = true ”

    If dd1.selectedvalue &lt;&gt; &quot;&quot; then

    srtsql = srtsql &amp; “ AND Field1 = dd1.selectedvalue “

    End if

    If dd2.selectedvalue &lt;&gt; &quot;&quot; then

    srtsql = srtsql &amp; “ AND Field2 = dd2.selectedvalue “

    End if

    If strSearch &lt;&gt; “” then

    srtsql = srtsql &amp; “ AND memoField LIKE '%&quot; &amp; strSearch &amp; &quot;%'&quot;

    end if

    etc……

    srtsql = srtsql &amp; “ ORDER BY FieldSort “



    SqlDataSource1.SelectCommand = srtsql



    Is it possible to Build this DAL using multiple parameters like above? How?



    Thanks in advance

    FNunes

  • Dear Mr. Scott,



    Thanks for Great post, I am wondering how this technique can be integrated with Enterprise Library Jan'06. Any guidance on this will be highly appreciated.

  • Hi FNunes,



    You can build ADO.NET code like that -- either using the partial class approach as described in the sample above (just add a method to the TableAdapter class and then write the ADO.NET code directly).



    Hope this helps,



    Scott

  • Hi Khuzema,



    To be honest with you -- I'm not entirely sure how this integrates with the Enterprise Library. I believe that library has a slightly different data factory pattern.



    Hope this helps,



    Scott

  • Hi Scott,



    Great article and wealth of information on your blogs.



    Would be great in your next set of articles if you could use a guid instead of auto increment id, or show how to handle it.

  • Scott,



    The Browse Definition takes you to the object browser not the code behind, for the browser you can then right click and go to definition.





    2005 Team Suite.



    Stephen

  • Awesome info Scott,



    I'm using this approach, along with the ObjectDataStore, but don't know where I should put my business rules. In my case I need to write the same row from my DAL to an audit table all within a transaction. Should I do a partial class and add that code to the DAL? Or can I put it somewhere in between teh data store and the DAL?



    Thanks,

    Tyler

  • I really like this walkthrough, I have done similar things and they work fine. Oracle support though is somewhat limited. But when the database changes and regeneration of the typed DataSets is needed, is there a way to keep the customisation of the TableAdapters? I know that the extensions in the partial classes are kept. But what about for example if I add a query with paramters, can it be preserved?

  • Hi Finnur,



    I believe when you change the schema, any custom queries you have will be maintained (assuming the columns they reference are still there).



    Hope this helps,



    Scott

  • Hi Steve,



    Are you referring to inserting custom parameters when using the ObjectDataSource? If so, there is an inerting and updating method on that control that fires immediately before the method gets called on the configured databound class. You could use this event along with the EventArgs argument to it to modify the inputparameters collection -- this is a name/value pair that controls what arguments are passed to the configured method.



    Hope this helps,



    Scott

  • I also get this error in my c# version. Can you please explain? Thanks.



    in Hierarchy.aspx:

    Container.DataItem.Row.GetProducts()



    I get error saying object does not contain a definition for 'Row'

  • Scott,



    Great post; this has helped me on more than one occasion!



    I'm having some problems at the moment with the insert statements that you covered in your tutorial.



    When I include &quot;select @@identity&quot;, I get a warning saying &quot;Unable to parse query text&quot;. I get this even after figuring out that I had to separate the statements with a semi colon.



    The text does get saved most of the time, however after editing the text afterwards I've seen it drop both the select statement and even lose all of the insert parameters on several occasions.



    I'm wondering if everyone has seen issues like this or if it's just my installation of VS2005.



    I also noticed in your example that you unchecked the &quot;Refresh the data table&quot; option during your initial configuration, which automatically adds a select statement after the built-in insert and update statements.



    In my experience with using that option, again my select statement is usually lost after I attempt to edit the query it generated, and I haven't found out a way to actually retreive the contents of that select statement.



    The insert method always returns an int, which I believe is the number of rows affected, and I haven't seen an option for it to return a scalar or reader, etc.



    I have to admit I'm baffled as to when you would actually use that checkbox, and whether or not anyone else has had issues with editing their insert/update queries afterwards.



    Thanks for any insight on this,

    Roger

  • Scott,



    This example is so much better than anything I seen in any of the 2.0 books! When can we expect your book? :))



    Do you know if it is possible to use a wizard and a formview in your example? I keep getting a record with null values added to the database. Remove the wizard and everything works fine....but I really need the wizard or multiview on the form.



    Steve


  • Hi Steve,



    You should be able to use a FormView and Wizard control. If you want to send me a simple sample of what isn't working (scottgu@microsoft.com) I can take a look.



    Thanks,



    Scott

  • Hi Graeme,



    In C# you'll need to cast that statement to something like this:



    ((Northwind.SupplierRow)Container.DataItem.Row).GetProducts()



    Hope this helps,



    Scott

  • How do I use this approach when I need transaction, for example I need to insert data in 3 tables, so I need to do this in transaction, how do i achieve same using above approach ?



  • Graeme,



    I found I needed this:



    ((Northwind.SuppliersRow)(((DataRowView)Container.DataItem).Row)).GetProducts()



  • (Northwind.SupplierRow)((System.Data.DataRowView)Container.DataItem).Row).GetProducts()



    did it for me in c#



    Great sample btw, I've learned a lot from it : )

  • Hi Nilesh,



    You can use the new &quot;TransactionScope&quot; feature in V2.0 to scope all of the inserts into a single atomic transaction. Basically you'd create the Transaction scope in code and then procedurally call the three inserts, and then indicate that the transaction is complete.



    Hope this helps,



    Scott



  • Does anyone know how to make the DataSet designer create Nullable types for certain columns?



    at the moment it creates (when get{} the value):



    return ((decimal)(this[this.tableMyTable.bookValueColumn]));}



    But I would like it to crate:



    return ((decimal?)(this[this.tableMyTable.bookValueColumn]));}



    So that I can assign it to a local nullable variable.



    Thanks,

    Nick

  • Hi Roger,

    Im having exactly the same issue with the inserts.

    it seems that it just ignores more than one SQL comand and just returns the rows affected.

    Damien.

  • Hello,

    In above example we take connection string at design time and it is saved in app.config file, what if my application gets connection string at runtime, i.e. i take connection details from user, form a connection string and then use same to connect to database, how can this functionality be accomplished.

    Please let me know.

  • Scott -- excellent writing. Any reason why I wouldn't be able to use an 'imports' statement in a class file? I have a class file in app_code that holds most of my common functions and was trying to add a few based on the new DAL... &nbsp;I can get to them from a typical web form but not from the the class file. &nbsp; Says that it can't find the import...

  • Hi Dan,

    You should be able to use an imports statement just fine within your class file. I'm not sure why that isn't working...

    Thanks,

    Scott

  • Still having issues using a ; in sqlCommandText when creating queries. I'm using sqlMobile, vb.net. Any suggestions?

  • Hi Scott! I'm impressed by your work.. I was wondering: If i want to build a web application that integrates with an external application that has its own database (but no SDK), and I want to build a DAL for this - how should I proceed to get a nice project architechture? I cannot (of course) move the physical location of the database, but could I still get it to appear in the App_Data folder? Or maybe I don't need it to appear in the App_Data folder?

  • Hi Allister,

    In the above article I'm using a SQL Express database, which stores the database file in app_data. But for a remote database the same technique works fine as well -- just configure the connection string to point at the remote system and the programming model and techniques stay the same.

    Hope this helps,

    Scott

  • Why the examples are on VB !? :)

  • Hi Cesar,

    I alternate writing samples in VB and C#. We are posting an updated data tutorial series (of which this is the first one) on the web tomorrow that has both VB and C# versions. You'll be able to find it on www.asp.net.

    Hope this helps,

    Scott

  • Hi Etienne,

    Nice blog! :-)

    You could in theory generate the .xsd files directly from your database -- although if you are doing this you might just find it easier to have your build provider (or custom build action) go so far as to generate the code for your DAL directly (that would give you total control).

    Hope this helps,

    Scott

  • There seems to be a problem using the contains keyword (full-text searching) with the TableAdapters. &nbsp;It works if you use stored procedures, but otherwise it throws an error saying my parameter is not defined. Also, if I create a query which returns more columns than what exists in the DataTable for that TableAdapter, it throws a data constraint exception. My last complaint is that the TableAdapter objects do not implement an interface or a base class (other than "Component"); so it's impossible for me to create interfaces which accept a generic TableAdapter. &nbsp;I would like to use these typed datatables, etc., for the foundation of my business objects. Does anyone else have these problems?

  • It would be great if someone could explain how to make dataset designer use Nullable types when declaring properties to access fields that can be null in the database. there are articles in MSDN that state that tableadapters support nullable types but no explanation of how.

  • Hi Jay,

    This tutorial series shows how to handle nullable types: http://www.asp.net/learn/dataaccess/default.aspx?tabid=63

    I believe Scott demonstrates how to-do this in the second tutorial.

    Hope this helps,

    Scott

  • Hi
    when you drag a table from the the server explorer to the dataset designer the .config file is created which is populated with the connection string. How can I change the connection string using code in my program.
    thank you very much.

  • Hi Hadi,

    The easiest way to change the connection string is to just edit it within the web.config file.

    If you want to programmatically change it, you can use the System.Management API. You can learn more about this here: http://www.asp.net/QuickStart/aspnet/doc/management/default.aspx

    Hope this helps,

    Scott

  • Hi Scott
    Good effort first if all.
    I am relatively new to VS used to work in java earlier, I wanted to know taht how much is the performance effecte dby using data sets as it will make multiple connections with each dataset and with each user logged in so how to enable connection pooling here?

  • Hi Mohsin,

    Database connection pooling is automatically enbled for both DataReaders and DataSets -- so you don't need to worry about manually managing connection lifetime.

    In general for most scenarios I don't think you'll see a significant difference between DataSets and DataReaders (note: DataSets use DataReaders to populate the data).

    The only cases where you will probably see any measurably difference is with large result-sets. In cases like those DataReaders can be more efficient if you are only accessing the data once (since they implement a forward-only read-once model), whereas a DataSet preserves all the data.

    Hope this helps,

    Scott

  • Excellent job Ben. I may use some of this for a project I am working on with a short timeline. Thanks for sending me the link.

  • I tried to use an existing stored procedure but did not work. Any idea?

  • Hi Scott,

    My name is Joseph Southron and I am Director of Clinical/Research Information Systems at Drexel University in Philadelphia, PA.

    I am currently building a custom build provider which generates entity classes, custom collection classes, and a DAL for a set of defined SQL statements in an XML file called .map. If you have ever read Dino'a "ASP.NET 2.0 Advanced Topics" it is basically an extension of that same custom provider but with some more functionality.

    I am looking to expose this project to the public for feedback and suggestions, so that I can ultimately give it up to the MS programmers of the world, however I do not have time to write blogs due to my busy director position. I was wondering if you would be interested in publishing my work in one of your blogs as a collaborater and co-developer of the project.

    Send me and email if you are interested and would like a copy of the source and a more in-depth description of what I am working on.

    Thanks!

    Joe Southron
    jfs32@drexel.edu

  • Hi Sajad,

    It depends to some extent on how much sales data you have. If you can grab it all in one database request then that is good from a DB perspective. However, if the total amount of data is huge, then retrieving it separately might actually make the most sense.

    Hope this helps,

    Scott

  • Hi Joe,

    Want to send me email (scottgu@microsoft.com) with more details about the project? It sounds very cool!

    Thanks,

    Scott

  • Hi Scott, my name is prashanthKRama. I am working as a software engineer for MNC. I was tried Tyeped Dataset as you mention in your article with an example for an employee details.
    I was created Mytypeddataste.xsd in emp is a data table and empTableAdapter. up to this everything is fine but in codebehind when i treid create an object of the class empTableAdapter it is giving an error saying the Type or namesapce is could not found. Kindly in this regard.

    Thanks,

    Prashanth

  • Thanks alot! It really helped me!

  • Hi,
    I have found this article very useful. But I am facing a wierd problem. I have a Table adapter which have a method that returns a single row based on the ID provided as the parameter. It works fine but strangely the datatable does not contain the values for one column. All other columns have proper value, but that single column has value of null. Has anyone faced this kind of probelm before?

    I would really appreciate if nyone help me out.

    Thanks

  • I got what ever you are saying about a strongly tped data adapter..But Just clearly mention how wil we connect DAL to Bussiness Logic layer.. And these with Presentation Layer...Just show this architecture at the same place only.....What ever code u provide it must show the Architectures(like 3-tier, 2 -Tier..)
    Please consider my request....
    Just giv a small project as a example and the code at the same place...This wil Give readers very easy to digest..
    Thank you

  • Hi Scott,

    Great info for someone who's learning about datasets; wanting to use them over the 'bound' type but I have a few questions please. The first is why is my created namespace hidden code being created in C# as opposed to vb when I create the dataset? Second is can I bind my inset/update/delete methods to the typical related actions in a datagrid?

    Thanks very much.

    Adam

  • Hi Adam,

    The generated code will be in the language of your app_code directory. So if you have VB files it will generate VB, and if you have C# files it will generate C#.

    The data tutorials on http://www.asp.net/learn are actually the best way to learn more about binding to methods. There are a lot of great articles there I'd recommend reading through.

    Hope this helps,

    Scott

  • I have tried this tutorial with a different database file and vs cannot pick up thhe imports statement? Do you know if i can view the name of the imports file? is it based on the name of the database or or dataset? help?

  • Hi Padmavathi,

    I believe the name of the TableAdapter can be controlled in the design surface of the data designer. Just click on the table adapter and you can rename it to be whatever you want.

    Hope this helps,

    Scott

  • Scott

    If you have a DAL as a separate project it will create a connectionString setting like
    "DAL.My.MySettings.SMSConnectionString". Is it possible for the DAL to use the connection string in the web.config?

    Thanks
    Will C

  • Hi Will,

    Yep -- definitely. Just copy/paste the connection string name from the app.config file of the class library project into the web.config file. At runtime your DAL will then use that as the connection-string.

    Hope this helps,

    Scott

  • That sounds easy. Thanks a lot, Scott.

  • Hi Adam,

    I believe the connection drop-down in the TableAdapter designer should automatically populate using those connection strings already in your web.config file. Are you not seeing this?

    On the dynamic connection string loading, there is a way to-do this -- although I need to ask someone to remember it . If you want to email me directly (scottgu@microsoft.com) I can connect you with them to help.

    thanks,

    Scott

  • Hi Scott -

    I have a dataset that has a parent table called "Parent", and a child table called "Child", these tables are related through a foreign key relationship, with cascading updates and deletes, configured in the dataset.

    My application marks a parent row as deleted, and the dataset does the cascading deletes for me. However, when I call the "Parent" table's, update method of the TableAdapter, I get a foreign key constraint error out of the database.

    Was I naive to think that the dataset would be smart enough to know to delete the child rows out of the database before it deleted the parent's?

    Also whats the best way to make sure you always get the children deleted first in terms of using your TableAdapters.Update method, since you call it for Insert and Update also. Do I need to call update in a certain order for deletes, and a different order for Insert?

  • Scott,

    Thanks for getting back to me about the dynamic connection string.

    With regards to refreshing the database schema, is there any way?

  • Hi Adam,

    Can you send me mail on the database schema refreshing question? I'll then loop you in with a few folks who can best answer it.

    Thx!

    Scott

  • Hi Jason,

    Any chance you could send me email (scottgu@microsoft.com) on that question? I can then loop you in with a few folks who are DataSet experts who can help.

    Thx!

    Scott

  • Outstanding article. The only problem I see is updating refreshing dataset when underlying table was changed (added/removed fields). Is there an easy way to do it (I know I can delete typed dataset and create it again, but that will kill all my "custom" methods).

  • Excellent tutorial!!!

    Just what I was looking for.

    How bout implementing this as a web service.

  • when I try to create a new query which try to get distinct country.

    SELECT DISTINCT Country FROM Suppliers, the application gave this message:
    "The new command text returns data with schema different from the scheam of the main query. Check your query's comand text if this is not desired.

    I click ok.

    Now I try to bind it with my dropdownlist box
    Dim suppliersAdapter As New ppliersTableAdapter
    mydropdownbox.DataSource = suppliersAdapter.GetUniqueCountry()
    mydropdownbox.DataBind()
    it complie ok, but when I run it it gave me this error:

    "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

    Why? Please help Thanks








  • Hi Ted,

    The problem you are running into is because you have two methods on the same TableAdapter that return different schema shapes. What I'd recommend is creating a new TableAdapter on your Dataset designer surface that uses the DISTINCT query.

    Hope this helps,

    Scott

  • Good tutorial, gave me the idea to use partial class for the generated table adapter. By the way: it is possible to set the base class of the table adapter in the design view. Is it as well possible to set an interface to implement?

  • I blew hours on the same problem Ted Lee had... thanks to Scott (and Google) for getting me back on track.

    One happy day, this DB plumbing will Just Work. Till then, thanks again.

  • Hello Scott.

    Great reading.

    I am trying to load this to a Dataset. Do you think I can do this. Your code show only to a datagrid. MS site says you just do the following line

    myAdapter.fill(mydataset.Tables("mytabel"))
    But this does not work. What am I doing wrong. I have tried all sorts of different ways but do you think I can work this out. Help if you have a moment.


    Dim ds As New DataSet
    Dim workTable As DataTable = New DataTable("ds_tabel")
    ds.Tables.Add(workTable)
    Dim infoAdapter As New bookitTableAdapters.infoTableAdapter
    Dim infoDataAdapter As bookit.infoDataTable
    Dim info As bookit.infoRow
    infoAdapter.Fill(ds, "ds_table")

    hawkinsw@xtra.co.nz

  • Hi Wayne,

    Can you send me an email describing the error you are seeing more (along with the code above)? I'll then loop somone in to the email thread who can help.

    Thanks,

    Scott

  • Good tutorial. Can anyone explain how you would use transactions with the typed-datasets? I have a set of records in a Gridview that I want to insert in batch to the database. If I were coding this directly, I could just use a SqlTransaction object. How would you do this using typed-datasets?

  • Hi John,

    This article should help you with transactions using Typed Datasets: http://blah.winsmarts.com/2006/06/18/the-definitive-tableadapters--transactions-blog-post.aspx

    Hope this helps,

    Scott

  • Most docs discussing table adapters only use one table for their examples. I have two tables that are related (PF/FK) and, of course, the wizard does not autogenerate the INSERT code when I create the tableAdapter containing two or more tables. Can you provide a step-by-step procedure on how to write the INSERT code using (1) stored procedures and (2)SQL statements. I am a newbie, so can you also show the actual code for the _sp and the SQL statements.

    My problem also is complicated by the fact that I must ensure that the first table (parentTable) must be successfully populated first before the second table (childTable) can be populated next.

  • Hello Steve,

    I am new to development and I am doing the tutorial right now. I am finishing Tutorial 5: Using Insert, Update, and Delete DBDirect Commands on TableAdapters.

    I copy the code to "Add a New Supplier" (please see it below) and I am getting the error:
    Compiler Error Message: BC30455: Argument not specified for parameter 'Address' of 'Public Overridable Function InsertSupplier(SUPPLIERID As Integer, CompanyName As String, ContactName As String, ContactTitle As String, Address As String, City As String, PostalCode As String, Telephone As String) As Object'.
    I am using my own Database and not the Northwind.
    Could you please help. I've read about this error and it seems that I need to specify two parameters.

    Thanks in Advance
    Pati

    CODE
    Dim supplierAdapter As New NorthwindTableAdapters.SuppliersTableAdapter



    Dim supplierId As Integer



    supplierId = supplierAdapter.InsertSupplier("Microsoft" _

    , "ScottGu" _

    , "General Manager" _

    , "One Microsoft Way" _

    , "Redmond" _

    , "USA" _

    , "98004" _

    , "425-555-1212")



    supplierAdapter.Update("Microsoft" _

    , "Someone Else" _

    , "New title" _

    , "New Address" _

    , "New City" _

    , "UK" _

    , "New Zip" _

    , "New Number" _

    , supplierId)



    supplierAdapter.Delete(supplierId)

  • How can I get the scope_identity using only he DAL?

    I do not want to use a SPROC, just the INSERT....; SELECT...scope_identity() that is generated by VS.

    I thought I would use the detailsview itemsinserted event, but I can't figure out how to use the tableadapter objects to expose that value returned.

    Thanks for any help.

  • I have alos experinced that connection drop-down in the TableAdapter designer does not populate using those connection strings already in web.config file. It uses connection strings stored in app.config file. Wondering how DAL in a seperate class library can use connectionstring in web.config file. I have tried manually changing the code befind file to use web.config string. But then desginer can not connect to database anymore in design mode.

  • Hi Nat,

    I believe there is a property on the TableAdapter that you can use to change the query.

    Hope this helps,

    Scott

  • How can you extract the data as XML from a strongly typed tableadapter? If you could fill a dataset, then the GetXml method could be used, but I can only manage to fill a datatable, even though the wizard states that either a dataset or datatable can be passed to the Fill function.

    (my email is robert.clancy@hp.com)

  • Hi Morten,

    You should be able to right-click on one of the DataSets and choose the "Configure..." option. This will then bring up the designer for the default query for the DataSet, which allows you to modify it and requery the database for the schema.

    Hope this helps,

    Scott

  • I don't think DataSet is good technology for ASP.NET. Applications in the real world go easily beyond what the intuitive DataSet can handle.

  • I need to find a resource to explain what the following code in your bll is doing:


    I have searched books such as asp unleashed etc. and found nothing to explain this. Please direct me.

  • Scott,

    When I use the vs2k5 Preview Data tool of the type dataTable adapter and try to put a Guid into the value field, I get an error about converting a string to a Guid.

    What is the proper format for a Guid in the preview data tool? I tried {...} among other things, but to no avail??

    Thanks,
    Greg

  • Hey Scott. Great Tutorial. For my current project, this is perfect. I have an issue that I can't figure out when I create the data adapters.
    For some reason, the creation process does not create the update method for about 60-70% of my tables. Could this be a permissions issue? It allows me to create my own update but that's not as convenient because the autogenerated one, I can just stick a datatable in as a parameter and I'm done. When I define my own methods, I have to specify each parameter when I use it don't I?

    Any thoughts on why it's not creating the update method would be greatly appreciated!

    again thx!

    Dave

  • Hi Dave,

    Good question -- I'm not entirely sure why it isn't generating the update methods. Maybe you are using some complex JOIN?

    Thanks,

    Scott

  • Scott,
    I discovered that if you don't have a primary key (I know, I know!) defined for a table, it won't generate the insert and update methods.

    I added the keys (I was lazy) and voila - all methods generated.

  • One last question - and thanks so much. After creating my dataadapters, I made some changes to the database including adding and deleting fields and even got rid of a few test tables.

    The .xsd that I created yesterday doesn't reflect these changes - even bringing up the tables I deleted (though I had no adapters created against them).

    Is there a way to refresh the entire .xsd to reflect the current state of the database?Sorry I'm not sure how to exactly referr to the .xsd space.

  • Hi Dave,

    You should be able to right-click on any of the Tables in the DataSet designer and choose "configure data source" again. This will allow you to walkthrough and refresh the schema.

    Hope this helps,

    Scott

  • I've literally had this problem everytime i've tried to use Typed DataSet

    I have just a default database made in VS, in it i have 2 tables relating to each other.

    (Lets say Member Table, and Association Table, ie Members would have 1 association, related by PK in Association and FK in Member)

    i then drag those tables (with the relation) into an xsd, which creates the relation and tables in that.

    I then compile the Typed Dataset, and get the generated classes.



    So then in my application, I load the information using a DataAdapter and get lets say a MemberRow object

    For the life of me, the AssociationRow property of the MemberRow (ie the parent row property that the dataset defines)

    NEVER has a value, It's always null, I've tried everything and its driving me up the wall

    shouldn't it see the data in the DB and load the related row????? (yes the data in the DB is correct)

    so do i have to use a datadapter/partial class to get the parent row, and if so, why is a parent row defined and not used?

  • Scott,

    Can you use the DAL to do LIKE '%@CompName%' queries as i can't get it to work?!

    Thanks

    Chris

  • Hi Like,

    You should be able to use the LIKE syntax with the TableAdapters.

    Hope this helps,

    Scott

  • Hi Ryan,

    Unfortunately when using Typed DataSets, the associated tables don't automaticlaly populate when you reference them. Instead, you need to load both tables into a dataset - and then the associated references work.

    This is somewhat inconvenient I'm afraid.

    Sorry!

    Scott

  • Can you give me an example of a LIKE query as mine doesn't seem to recognise the parameter is there and therefore i can't select a control to pass the string to search on.

    SELECT * FROM Table WHERE CompanyName LIKE '%@CompName%'

    The TableAdaptor doesn't see the @CompName.

    Thanks

  • Hi,

    Very good example but unfortunetly I am very new to VB.NET 2005, is it possible to write a Master-Detail VB.NET 2005 article and explain like Step-By-Step way.

    I couldn't find any great article about it and it seems you can write better than most people.

    Thank you.

  • Hi NTMS,

    Scott Mitchell has written some great data tutorials that you might want to check out here: http://www.asp.net/learn/dataaccess/default.aspx?tabid=63

    These include some master/details samples and are all in both VB and C#.

    Hope this helps,

    Scott

  • Hi Chris,

    Can you send me an email (scottgu@microsoft.com) with this question in it? I'll then loop someone from the VS data team who might be able to help.

    Thanks,

    Scott

  • I have tried to use typed dataset with object source. when i created a new row in the object source the same is not refreshed in the gridview. I would like to have a clarification, which is the best method to work with objectdatasource. Is it typed dataset or custom dataset. how to handle the transactions on the typed data set

  • Hi,You mentioned that the easiest way to change the connection string is to just edit it within the web.config file. Is ther any way to do thi programmatically on the fly? I have several database whos names are all different and would like to be able to connect to one bassed on a users selection.

  • Hi Alan,

    This blog post covers how to dynamically set the connection-string at runtime: http://blogs.msdn.com/smartclientdata/archive/2005/07/25/443034.aspx

    Hope this helps,

    Scott

  • Scott,It was a great article, thanks.

    I have same problem with some of the people here when I try to use LIKE syntax with the TableAdapters. I does not work.

    I created it with this query below it works perfect."SELECT * FROM Table WHERE CompanyName =@CompName"

    as soon as I change this "SELECT * FROM Table WHERE CompanyName LIKE '%@CompName%'" It does not work.

    Can you please post the sample answer to this problem, thanks.



  • Hi NTMS,

    Unfortunately I don't have any WinForms tutorials like this. You might try the www.winforms.net web-site - as it might link to some.

    Hope this helps,

    Scott

  • Hi Alan,

    Here is an article that discusses how to dynamically change the connection-string on the fly: http://blogs.msdn.com/smartclientdata/archive/2005/07/25/443034.aspx

    Hope this helps,

    Scott

  • Scott,

    I am trying to find out how to bind a dataset that has 2 tables and relationship setup to a gridview that will show patent/child rows

    Do you know if this possible, and if so can you direct me too some code.

    Malcolm

  • Hi Malcolm,

    Have you had a chance to check out this data series yet: http://www.asp.net/learn/dataaccess/default.aspx?tabid=63

    It builds upon my sample above and shows a varity of scenarios - including master/details.

    Hope this helps,

    Scott

  • Hope some one can point me in the right directions. I have created a DAL with 2 strongly typed tabeladapters. I have created a realtionship between these 2 adapters. Now I want to display the data using nested Repeaters. I set the first repeater to the parent table. Now for the child repeater, i am using the following code:

    Protected Sub parent1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles parent1.ItemDataBound
    If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
    CType(e.Item.FindControl("child1"), Repeater).DataSource = CType(e.Item.DataItem, DataRowView).CreateChildView("rel1")
    CType(e.Item.FindControl("child1"), Repeater).DataBind()
    End If
    End Sub

    And when I run it I always get the error "The relation is not parented to the table to which this DataView points". Any ideas. I'm at a lost right now.

    Thanks

  • Hi,

    I would like to create/change runtime connection without coding and without using wizard table adapter. If u enable to do this task. Please give me some help.

    Thansk

  • I have built a BLL calling into the TableAdapters but I am getting an error for which there doesnt seem to be a logical explaination:

    The error: Cannot implicitly convert type 'int'? to type DataTable.

  • Hi Geee,

    This error probably means you are trying to access a column value but save it as a DataTable. What does the line of code causing this error look like?

    Thanks,

    Scott

  • Hi Kaushik,

    This blog post covers how to dynamically change a connection string for a TableAdapter on the fly: http://blogs.msdn.com/smartclientdata/archive/2005/07/25/443034.aspx

    Thanks,

    Scott

  • Hi ! Great article, but there are some more interesting things about typed Datasets.
    If I have a database with more than 20 tables &nbsp;where every table is related with one or more tables, like in a conex graph, what are the principles in designing a typed dataset?
    Is it safe/correct to put all the data tables in the same dataset ?
    If I have Table 1 &nbsp;(1)-(n) Table 2, and an insert in Table 2 updates also the corresponding parent record in Table 1, should this two tables (with their table adapters) be in the same dataset?
    Also, .. it is ok if a BLL class has as a member another BLL class ? (Like OrderBLL class would have a CustomerBLL customerAPI, and customerAPI performs some operations on customers; or it would be best is OrdersBLL should have itself a CustomerTableAdapter and perform operations on CustomerDataTable ?)
    Please, can you give some answers and maybe some examples !
    Great thanks !, Adrian
    This is for a 3-tier web app.

  • Thanks for this excellent step-by-step. Unfortunately for me, it looks like I'm already doing the same thing as your sample code, and I'm still having a weird problem. I've created a strongly typed data table and adapter, and I've hooked up my own stored procedures for the select, insert, and update commands. Selecting works fine to return lots of records. However, when I populate a data table with several new rows (using New..Row and Add..Row methods) then try Adapter.Update(table), only the first row in the table gets saved. No exceptions occur - it just inserts or updates the first row from the table, and nothing else. I have verified that the table has multiple rows containing unique data, so it's not just a copy of the same row over and over. Am I missing something simple? Any idea where I should start looking?

  • Hi Julie,

    Any chance you could send me an email with more details on this error? I can then loop you in with someone who can help.

    Thx!

    Scott

  • Hi Adrian,

    The good news is that Scott Mitchell has put together a ton of really good data tutorials here that should help with some of these questions: http://www.asp.net/learn/dataaccess/default.aspx?tabid=63

    Hope this helps,

    Scott

  • Scott - thanks for your kind offer, but my post didn&#39;t show up for a while, and in the mean time I finally figured out what was going on. I hadn&#39;t noticed that the strongly typed data tables automatically enable a property called AutoIncrement on the primary key column, so each time I was using the New..Row method, it was filling my primary key with sequential numbers from 0-x. My stored proc was handling the 0 in the first record and treating it as a new insert, but it was assuming the remaining records were existing records to find and update, and hence the lack of any action. Live and learn!
    Thanks again,
    Julie

  • I was wondering how those queries that return single values are accessed (e.g. count, max or @@identity): if there's a possibility to choose them, I thought it would be easy to call them through the dataset class or through an objectdatasource, but I didn't find a way...and your nice walkthrough - as others - thoroughly avoided that case. Thanks anyway in advance & bye.

  • Hi Scott

    Thanks for the great tutorials, I have also checked data tutorials on asp.net site and used in a small website. It is was cool and very fast developement.

    But for another project already developed and running.
    I was manually created dal using datareader and dataadapter.

    Typed dataset creates dataset for every request to data. And I have learnt that creation of dataset is a complex task.

    Seems like Entity classes and IList can provide a better solution for my application which is very data centric.
    Please help

    Thanks,

    Vishal

  • I have a TableAdapter that is pointed to my development database. This TableAdapter has several queries defined against it. Using the "TableAdapter Query Configuration Wizard", I updated the ConnectionString to point to my test database.

    When I run the default query for this TableAdapter (GetData), it runs and returns the correct results from my test database. When I run the other queries I have defined in this TableAdapter, they run and return results from my development database. Through the "TableAdapter Query Configuration Wizard" and the Properties window, I do not see anywhere to explicitly change the ConnectionString for each one of these queries.

    Could you help me out here or point me in the right direction? This seems simple enough but I must be missing something.

    I great appreciate any help you can provide.

  • Thanks for the great how-to information Scott. I do have one question related to typed datasets and SP's. Is there a way to obtain the return code from a SP via a typed dataset without writing custom code?

  • Have terrible problems with optimistic concurency. Parameters missing, Datavalue overflows. The generated code not being regenerated properly.
    Started out with this 3tier DAL - BLL development and this dataset designer. I cannot get the code to work with a relative small database in a multiuser mode (builting optimistic concurrency) because of incorrectly generated code.
    When i try to reconfigure it sometimes does not regenerate to full code but only the SQL methods which than are working with the supplied NULL values, while the adapter.update is not accepting the needed isNULL fields.

    Also giving adapter.update(datatable) and adapter.update(datarow) keeps nagging about an isnull_ID column which is not given because i am giving a datatable / datarow and not individual fields. Terrible. I have build my database on this dataset designers concurrency and now i am totally stuck with it.

    Any advice welcome.

    regards

  • Very clear sample, thanks

  • Hello Scott,
    I am working on a big project which will require 20+ database tables. Should I put all the TableAdapters in one dataset, or separate them into multiple dataset. For example, Supplier dataset may have few DataAdapters which return different sets of data, and customers would be on another dataset and there will be few dataAdapters also.

    Most of the samples I saw has very few database tables. So authors usually only demo putting everything in one dataSet.

    I want to make my code more efficient.Please advice. Thank you.

  • Great Article. I had been using the concept since mid last year. I do have another question though.

    Can we set a constrainst property on a tableAdapter.

    The reason for me is i keep getting a constrainst error when i access a SQL view that is build using the Parent and child tables.

  • Hi Sam,

    I believe you should be able to set a constraint on a property. If you send me email, though, I can connect you up with someone who knows for sure.

    Thanks,

    Scott

  • Hi Madeline,

    Any chance you could send me an email with this question? I can then loop someone in from the data team to get a good answer for you on this.

    Thx!

    Scott

  • Hi Scott,
    This is an excellent tutorial.
    I learnt a lot from there.
    I have a question.
    On the "TableAdapter Configuration Wizard", if I select "Create new stored procedure", then I ended up creating stored procedure in the user schema
    for example vono.sp_GetAllProducts (this is because when I created new connection in Server Explorer, I can not use dbo account)

    I want to create stored procedure in the dbo schema. How to do it?

    Thanks
    Chris

  • Heya, great article!

    Although, I'm kinda stuck. I'm writing in C# and baffled why I have this problem.

    I don't have a problem creating the Datatable, but whenver I try to create the dataRow I get :

    Error 172 The type 'news.AuthorbyStaffIDRow' has no constructors defined C:\Inetpub\wwwroot\etruth\Know\Index.aspx.cs 191 39 C:\...\etruth\


    ----------
    Here are the three lines that I'm using:

    newsTableAdapters.tblStaffTableAdapter doa_Authors = new newsTableAdapters.tblStaffTableAdapter();
    news.AuthorbyStaffIDDataTable dt_Staff = new news.AuthorbyStaffIDDataTable();
    news.AuthorbyStaffIDRow xxd = new news.AuthorbyStaffIDRow();

    Help me please ! Thanks .net gods !
    whirley

  • Hi Whirley,

    Rather than use a constructor, I believe you need to call the NewRow() method on the TableAdapter itself. This will return a DataRow instance you can then set properties on and use.

    Hope this helps,

    Scott

  • Suppose i have dataview in which "Column1" is of type string. this column has value such as 1,2,3,4,5,6,7.

    Now i want to sort this dataview using a "column1" but i want a integer sort.
    In SQL we ll do "Oreder by cast(column as int)"

    Is it possible to do the same in dataview?

  • Any way of making this type of DataLayer work with the Data Access Application Block from MS?

  • Hi,

    I have a DataList control which is bound to an ObjectDataSource, which is bound to a typed TableAdapter.

    The ObjectDataSource only contains one Update method - however, I want to be able to execute any one of several update queries, depending on which button the user presses inside the DataList template.

    What I did is I created an instance of my typed TableAdapter in addition to my ObjectDataSource, just for the purpose of calling the various Update functions that are part of it. However it seems like a waste of resources, because I think that the ObjectDataSource is also building an instance of the same TableAdapter.

    Is the best option here to not use an ObjectDataSource and bind the DataList manually? I'm not using any features such as sorting, paging, filtering. Or should I change the UpdateMethod and paramters of the ObjectDataSource on the fly, depending on what the user is pressing?

  • Hi Tudor,

    If you are using the DataList, then I'd probably recommend just binding the DataList manually.

    The DataList control doesn't actually know how to use the Update/Insert/Delete logic, and doesn't have great built-in support for paging.

    The new ListView control in Orcas will have all these features, and be a superset of the DataList. But until then I'd probably just bind the DataList control manually.

    Hope this helps,

    Scott

  • Hi David,

    Unfortunately the approach I described above is different from the Data Access Block - so the designer here doesn't work with that I'm afraid.

    You can, though, use all of the ASP.NET databinding features with the Data Access block though.

    Hope this helps,

    Scott

  • This all works well apart from LIKE just simply does not work.

    I can build the query and test it in the query builder, but if i try and actually use the function or even right click it and preview data passing in a string it does not return any rows.

    Query as below. You notice here i use '?', if i try to use @param it just tells me @param is not defined. If it needs to be defined where should it be?

    SELECT ProductId, ProductCode, ProductDescription, ProductPrice, NavSectionId
    FROM LPlus2007ProductTable
    WHERE (ProductDescription LIKE '%' + ? + '%')

Comments have been disabled for this content.