Visual Studio 2005 Hands-On Tutorial

By David Catherman

Introduction

Visual Studio 2005 and SQL Sever 2005 are fairly new products with many new concepts. Those who are eager adopters of new technology have plunged in and written many great articles about the new productivity. But others have taken a "wait and see" attitude, and need a little encouragement and even hand-holding to get over the learning curve with less frustration.

For those who prefer to learn with a guided training environment, I have written this series of hands-on tutorials that will take you through all the steps to build a complete application using the new database and IDE tools. This application is architected in a 3-tiered model with a business/data access layer against a SQL Server data layer, both a Windows (Smart Client) and Web user interface layers and finally a Web Service to communicate between the layers.

Project Description

The Internet is a great place to find documentation and training on new products like Visual Studio 2005. There are many great websites that publish articles that can help us get up to speed quickly. The problem is that with these articles scattered all over the net, it is hard to find the information you need. Search engines are a good start, but since they index on words instead of topics, it takes a lot of searching to find the right articles.

The .NET WebLinks project is a collection of Internet sources for information about .NET 2.0, Visual Studio 2005 and SQL Server 2005. Links to these articles are collected into a searchable database and categorized by subject. The project will allow all the .NET enthusiasts to add to the database, so that it will grow to become a very valuable tool for the .NET community.

Part 1 will define the database using SQL Server 2005 and business logic using datasets.

Part 2 is a Windows interface to the database, allowing individuals to import links from their Favorites directory, edit the links and upload to the master database.

Part 3 is a Web based interface to allow the general community access to search the database for the desired links.

Part 4 will define the Web services for the project to communicate between the layers, how to define and consume them.

Part 1 - Database and Business Logic

Designing the SQL Server Database

Before beginning a database design, a few items need to be discussed like methodology and naming conventions. While there are a variety of opinions, many database designers prefer using table names in the singular form (name each as if there were only one record), design to 3rd normal form, and using Identity integer fields as primary keys. In naming fields, the primary key is always the table name followed by "ID", foreign key fieldnames are the same as the primary key they point to, and the field names or context should imply the field type. If a database can be updated by disconnected applications, primary keys may need to GUIDs instead of (or in addition to) identity integers to avoid duplication of keys.

Visual Studio has a panel (exposed by selecting Server Explorer from the View menu) which has almost all the functionality needed to design and build a database. The only function that is missing is the ability to rename tables (so take care to get them right the first time.)

Note: The Server Explorer is known as the Database Explorer in the Express versions of Visual studio.

  1. Begin this session by opening Visual Studio 2005 and opening the Server Explorer (under the View menu).

  2. Right click "Data Connections" and select "Create New SQL Server Database...". This is also where you can select "Add Connection..." to connect to an existing database.

  3. Enter (or select) the name of the server. A shortcut is to enter a period (.) to select the default localhost server.

  4. Select the Authentication desired--normally "Windows Authentication" is sufficient.

  5. Enter WebLinks as a new database name and click OK.

  6. The wizard automatically creates a connection to the new database. A tree view shows the default schema.

    The WebLinks database is made up of four tables: the hierarchical Topic table to define structure to categorize the links; the Link table to store information about each web reference, a lookup table to define the different Link Types, and a Topic Link table for the many-to-many resolution table between Topic and Link so a reference can be classified under multiple topics.

  7. Next, create the tables. Right click on the Tables entry and select Add Table. In the grid provided enter the fields for the Topic table.

  8. In the column properties detail section below the grid, specific properties can be added for each column (the displayed properties are for the selected field in the grid). For the TopicID field, scroll down in the properties to the Identity Specification. Click the plus (+) next to the entry to expand it and set the "Is Identity" to true, leave the seed and increment to 1.

  9. In the toolbar above the Server Explorer, click the key icon to set the TopicID field to be the primary key for the table.

  10. In each field, you can fill in the Description property, and in the InactiveFlag field, fill in the Default Value property to 0. (Bit flag fields are difficult to handle when they are null. By forcing a default value, the field should never be null.)

  11. After entering all the fields and properties, click the save button in the toolbar (diskette icon) and save the table with the name Topic.

  12. Repeat this procedure for each of the other three tables using the following fields:

    LinkTable
    LinkID int Primary Key
    LinkTitle varchar(200) Title of the work
    LinkTypeID int NULL
    Description varchar Paragraph describing contents of article
    URL varchar(500) Actual link to article
    Author varchar(150) NULL
    PrimaryLinkID int ID of primary URL for article
    SecondaryType varchar(50) Type of entry (Primary, Print, Page, Alternate, Language, etc.)
    InActiveFlag bit NULL
    ArticleDate datetime Original Date published to the Web
    AccessCount int Number of users accessing this link
    RatingCount int Number of users offering a rating
    RatingTotal int Total Rating score
    AvgRating real current average rating
    DateCreated datetime Date added to the database
    DateModified datetime Date of last modification
    ModifiedBy varchar(50) Username of person making the modification
    LinkType Table
    LinkTypeID int Primary Key
    LinkTypeDesc varchar(50) Link Type Description
    Topic Table
    TopicID int Primary Key
    TopicName varchar(50) Name or Description of the topic
    ParentTopicID int FK to Topic for Parent Topic (self-join)
    DateCreated datetime Date Created
    CreatedBy varchar(50) Created By
    InActiveFlag bit Inactive Flag (no longer active)
    TopicLink Table
    TopicLinkID int Primary Key
    TopicID int FK to Topic
    LinkID int FK to Link
    Comment varchar(500) Freeform comment on why topic applies to link.
    InActiveFlag bit NULL
    DateCreated datetime Date Created
    CreatedBy varchar(50) Created By Username

  13. You can click the icon in the toolbar to create relations manually, but an easier way is to use the Database Diagrams wizard. In the Server Explorer, right click the Database Diagrams folder and select Add New Diagram.

  14. In Add Tables dialog, select all four tables and click OK. (If you prefer, you may skip the dialog and drag and drop the tables from the Server Explorer.) Each of the four tables will be shown in the diagram with a list of fields below.

  15. To create a relation, select the primary key of the parent table and drag and drop it on the foreign key of the child table. Create the following relationships:

  16. Save the diagram as Main and confirm the creation of foreign keys. SQL Server will also create indexes for all primary and foreign keys.

Adding Functionality

The WebLinks application needs several back-end functions to process the data more efficiently.

  1. The hierarchical Topic table needs a function to help process the structure. Right click on the Functions folder in Solution Explorer and click, Add New, Table-valued Function.

  2. There is no Wizard to help you here--you have to write the TSQL code yourself. VS does add a decent template to pattern from. Paste in the following code:

    /* <FunctionName>udfTopicTree</FunctionName>
    // <Summary> Returns table of all rows with level and sort
    //           made up of hierarchy of TopicName
    // </Summary>
    */

    CREATE FUNCTION udfTopicTree()
    RETURNS @Tree TABLE (TopID Int, ID Int, Lev int, Sorted VarChar(255), HasChildren int)

    AS
    BEGIN
        --declaration and initialization
        Declare @Level Int, @Count Int
        Set @Level=0
        --Get top level records
        INSERT INTO @Tree
        SELECT Topic.TopicID AS TopID, Topic.TopicID AS ID, 0 AS Lev,
            RTRIM(Topic.TopicName) AS Sorted,
            ISNULL(COUNT(Topic_1.TopicID), 0) AS HasChildren
        FROM Topic LEFT OUTER JOIN
            Topic AS Topic_1 ON Topic.TopicID = Topic_1.ParentTopicID
        WHERE (Topic.ParentTopicID IS NULL)
        GROUP BY Topic.TopicID, RTRIM(Topic.TopicName)
        Set @Count= @@RowCount

        While @Count>0 Begin        --While records are still being added
            Set @Level=@Level+1
            IF @Level>9 Return      --in case of infinite reference loop

            INSERT INTO @Tree
            SELECT TopID, Topic.TopicID AS ID, @Level AS Lev,
                Sorted + '--' + RTRIM(Topic.TopicName) AS Sorted,
                ISNULL(COUNT(Topic_1.TopicID), 0) AS HasChildren
            FROM Topic LEFT OUTER JOIN
                Topic AS Topic_1 ON Topic.TopicID = Topic_1.ParentTopicID
            INNER JOIN
                @Tree ON [Topic].ParentTopicID = [ID]
            WHERE Lev = @Level - 1
            GROUP BY TopID, Topic.TopicID, RTRIM(Topic.TopicName), Sorted
            Set @Count= @@RowCount
        End
        Return
    End

  3. VS does have some tools to help you. Notice the blue boxes around the Select statements. You can right click in these boxes and select Design SQL Block to show a visual query designer. If the query is complex, you can highlight a certain section and just build that part of the query. Open the query builder for the first Select by right clicking inside the blue box and choosing "Design SQL Block".

    This function will return several fields that are important for the processing of hierarchical data. For each record in the table, you will need to know the ID of the top level parent, the level in the tree, a composite field showing the names of all previous levels in the tree, and a field for showing whether or not (or how many) children are present for a given node.

    The first part of the function selects all of the top level nodes. The following shows the builder for the first part of the query.

    The first panel shows the tables and the joins between them. In this case, the function needs to know if there are child records, so the table has an outer join with itself. You can right click on the join icon between the tables to edit the join type.

    The second panel shows a list of the fields or calculations that are returned as fields. You can also define sort, group and filter for fields.

    The third panel shows the resulting SQL statement and the forth panels shows the resulting rows when you click the Execute Query button. Click the "Cancel" button on the Query Builder dialog.

    The second half of the function selects the next level nodes and joins them with the results of the previous query(ies) and loops through each succeeding level appending the nodes to the temporary table.

  4. Click the Save (diskette) icon in the toolbar to save the function. If there are any errors, the SQL engine will give a very nondescript error message. If the Save icon is disabled, click outside the first SQL Select box in the function.

    Another function needed is to do a word search of the Link table for keywords entered by the user. The search follows the basic rules of Google and other search engines. This function will be implemented through a Stored Procedure.

  5. Right click on the Stored Procedure folder and click Add New Store Procedure.

  6. Once again, all you get to work with is a template and the query builder. Here is the T-SQL for the search function:

    CREATE PROCEDURE dbo.SearchByKeywords
        (@Search VarChar(255))
        --Search for Links by Keyword
        --returns a data table of Links
    AS
        /* SET NOCOUNT ON */
        --get a set of search words
        declare @where varchar(8000)
        set @where=''
        declare @word varchar(255)
        DECLARE @pos int
        --check for injection - may need to add other checks here
        if charindex('''',@search)>0 or charindex(';',@search)>0 begin
            raiserror('SQL Injection error - Cannot use single quotes ' ,16,1)
            return
        end
        set @search=ltrim(@search) + ' '
        while len(@search)>0 begin
            --is next word in quotes - take whole quoted section as word
            if @search like '"%"%' begin
                set @pos=charindex('"',@search,2)
                set @word=substring(@search,2,@pos-2)
            end
            -- select the next word in search words list
            else begin
                set @pos=charindex(' ',@search,1)
                set @word=substring(@search,1,@pos-1)
            end
            --create a where clause for the search of the following pattern
            --if the word is "DotNet"
            -- Where PatIndex('%DotNet%',[LinkTitle])>0
            -- OR PatIndex('%DotNet%', [Description])>0
            if len(@where)>0 set @where= @where + ' OR '
            --append a space on the begin and end to handle first and last words
            set @where=@where + ' PatIndex(''%[ -]' + @word + '[ ,.;-?!:]%''' +
                ','' '' + [LinkTitle] + '' '' + ISNULL(Description, '''') + '' '')>0'
            --remove the word from the search string
            set @search=ltrim(substring(@search,@pos+1,9999))
        end --loop
        --return a set of rows that have the search words - in the same format as the
        --TopicLink table since that is where they will be displayed
        exec('SELECT LinkID AS TopicLinkID, LinkID, 0 AS TopicID, NULL AS Comment,
                  0 AS InactiveFlag, NULL AS DateCreated, NULL AS CreatedBy, LinkTitle
              FROM Link WHERE ' + @where)
        RETURN

  7. Click the Save button to process and save the stored procedure. Notice that the editor changes the Create keyword to Alter after it is saved successfully.

  8. One more stored procedure is needed to accept votes on the article rating. Right click on Stored Procedures and Add New Stored Procedure. In the code window, add the following:

    -- Record a new vote on a Link and calculate the new average
    CREATE PROCEDURE dbo.LinkRatingVote
        (
        @LinkID int,
        @Rating int, --new rating
        @AvgRating int OUTPUT
        )
    AS
        SET NOCOUNT ON
        Declare @RatingCount int
        Declare @RatingTotal int
        --get the Link record
        Select @RatingCount=RatingCount, @RatingTotal=RatingTotal
        From Link Where LinkID=@LinkID
        --calculate new values
        Set @RatingCount=ISNULL(@RatingCount,0)+1
        Set @RatingTotal=ISNULL(@RatingTotal,0)+@Rating
        Set @AvgRating=@RatingTotal/@RatingCount
        --Update the link record with new values
        Update Link
        Set RatingCount=@RatingCount, RatingTotal=@RatingTotal, AvgRating=@AvgRating
        Where LinkID=@LinkID
        RETURN

  9. Click the save button.

Add a Connection to an Existing Database...

If you wish to save time and have a populated database, you can download the above database already created as part of the code samples. Follow the instructions there to restore the SQL Server backup to your server. Then you can connect it to the Server Explorer as follows:

Right click in the blank area of the Server Explorer and click Add New Connection or click the Connect to Database icon at the top of the Server Explorer.

  1. In the first page of the connection wizard, make sure the Data Source is set to "SQL Server (SqlCLient)". If it is not, click the Change button and select SQL Server from the list of options.

  2. Select the server from the list or enter a period (.) for the localhost option.

  3. If the database has been set up for integrated security, choose Windows Authentication, otherwise select "SQL Server Authentication and enter the user name and password given by your DBA.

  4. In the bottom section, select the WebLinks database from the list.

  5. Click the Test Connection button to verify access to the database.

  6. Back in the Server Explorer, you will see a tree view showing the schema of the database.

  7. At this point you can right click on any of the tables and select Open Table Definition to make edits to the design or you may select Show Table Data to get a grid showing the data in the table. You can even use this grid to make manual changes to the data.

Creating the Business Logic DataSet

It is important to build the Business Logic portion of the project first since the other three parts will use this middle tier in the form of a dataset to store the data locally.

Open Visual Studio 2005 development environment.

Note: there is a setting under Tools, Options, Projects and Solutions, General to always see the solution in the explorer. With the solution showing, it is easier to right click and add a new project to the solution.

If you have not done so yet, use the following procedure to setup a properly structured Solution with multiple projects.

  1. Open Visual Studio 2005

  2. From the File Menu, select New, Project...

  3. In the left pane choose Visual Studio Solutions under Other Project Types, and in the right pane, Blank Solution. Name the new solution "WebLinks" and choose a directory where you wish to store the solution.

    Note: This procedure will create the solution file in the proper directory. If you create the solution at the same time as the project, the solution file gets placed under the project folder which makes it hard to find the solution when multiple projects are created in the solution.

  4. Now add a project to the solution for the shared business classes. Either from the File menu or by right-clicking on the Solution in the Solution Explorer, select Add, New Project, Visual Basic, Class Library. Name the project Biz.

  5. VS automatically creates a new class called Class1. In the Solution Explorer, right click Class1 and select Rename and change the name to Business.vb. This will be our base class for our business objects to inherit from. We will come back to this later. For now, close the Business class using the small "x" in the upper right of the code pane window.

    Note: It is easy to click the wrong close button. The big red "X" in the upper right of the screen will close the whole Visual Studio program, sometimes without prompting, and takes some time to get restarted. Be careful to use the small grey "x" below the tool bar to close files.

  6. Visual Studio has a great visual tool to build a Typed Dataset. Right-click on the Biz project in solution explorer and from the context menu, select Add, New Item. From the dialog, select Dataset and name it WebLinksDataSet.xsd.

  7. The Dataset Designer will present a blank screen to build the tables in the dataset. The easiest way to do this is to drag the tables from the Server Explorer. Under the View menu select Server Explorer (or press Ctrl+Alt+S) to add the pane to the left side of your designer.

    Note: If you are using an Express version of Visual Studio, this will be called Database Explorer instead of Server Explorer.

  8. If your connection to WebLinks is already open, you can jump to step 14 and drag all four tables to the designer as well.

  9. Right-Click on the top DataConnections node and select Add Connection...

  10. In the Add Connection Dialog, fill in the name of the server. If you are using the default installation of SQL Server on the local system, you can use a single period as a shortcut. Otherwise, type or select from the list the server to which you wish to connect.

  11. You should be able to use Windows Authentication. If you do not control the Windows server, you may have to switch to SQL Server Authentication, but this is less flexible and requires setting up usernames and passwords on the SQL Sever.

  12. Select the WebLinks database from the list provided in the database section of the dialog.

  13. Click the Test Connection button to ensure all the permissions are set correctly and then click OK.

  14. The new connection should now show as a node under the Data Connections root. Click the "+" to the left of the node to expand and see the database items. Click the "+" next to the Table node to see the tables in the database. Click the first table and shift click the last table to highlight all four tables. Now drag and drop these tables onto the Dataset Designer screen.

  15. The wizard will read the metadata from the database and build Data Tables for each of the tables and add relations for each of the relationships in the database. You can now organize these tables by clicking in the header and moving them around. You should end up with a diagram similar to the one shown below.

  16. From the File menu, select Save (or Ctrl+S or click the diskette button on the toolbar) to save the dataset. The wizard will now generate over 4000 lines of code for you in the WebLinks.Designer.vb file. You need to click the Show All button at the top of Solution Explorer to see this hidden file as well as the partial class to be created next. You should end up with a diagram as follows:

  17. You can edit the query for each table by right clicking on the header or Table Adapter band and select Configure. The Wizard will open and prompt you for information. Right click the Topic table and select Configure.

  18. The first screen shows the query being used by the Topic table adapter to select and update data.

  19. If you click on the Previous button, you can change from using imbedded SQL to Stored Procedures. You can use your own or allow Visual Studio to generate them automatically. While using Stored Procedures is a good idea, we will continue to use the embedded SQL for this exercise. You can come back at a later time and generate the procedures. Click Next to return to the SQL Statement screen.

  20. Click the Query Builder button to view the query graphically. This is very similar to the query builder used on the Stored Procedures. You can manipulate the query as necessary to get the results you need for the program. When finished, click the OK button to return to the SQL Statement screen

  21. If you click the Advanced Options button, you will be given several options. If your table is read only in this dataset, you can deselect the "Generate Insert, Update, and Delete statements" option. Such is the case for the LinkType table.

    The "Use optimistic concurrency" compares every field in a record before updating or deleting the record. If you don't care about two users changing the same record at the same time, but prefer to let the latest edits win, you can turn this off. It does simplify the number of parameters passed to the Update and Delete queries.

    The "Refresh the Data Table" option causes the Insert and Update queries to return the updated record so the dataset knows about database generated fields. This is important when using Identity fields as primary keys so the dataset can update the keys properly.

  22. Back on the SQL Statement screen, click Next to go to the next screen. The Choose Methods to Generate screen allows you to choose which type of methods you wish the wizard to generate. The Fill methods will add records to an existing table in the dataset. The Get methods return a data table object to your application. You can turn off the Update methods for read-only tables (like LinkType).

  23. The next screen gives a summary of the methods generated. These are available for your application to call. Click Finish to update the Table Adapter and return to the DataSet.

  24. Now you need to add several additional queries for selecting specific data. Right Click on the TopicTableAdapter band and click Add Query. This will take your through the same wizard and allow you to modify the query to provide parameters that limit the rows returned. One of the ways we want to limit the Topic table is to show only nodes that belong to a certain root. (The root of the tree is the username of the person importing favorite links. You may want to limit the tree to a certain user).

  25. In the Wizard, click Query Builder to view the query. In the top panel, right click and select Add Table.

  26. In the Add Table dialog, select the Functions tab and the udfTopicTree function.

  27. Join the function to the Topic table by dragging the TopicID primary key from the Topic table and drop it on the ID field of the udfTopicTree table. This will create a join line between them in the diagram and add the Inner Join phrase to the SQL.

  28. In the second panel, scroll down to the first empty row and add a row for the TopID field of the udfTopicTree_1 table. Uncheck the Output box and in the Filter column add the condition "=@TopID".

  29. Click OK on the Query Builder and then Next on the wizard to the Method Name screen. Change the name of the new methods to "FillByTopID" and "GetByTopID". (The wizard defaults to "GetDataBy". Take out the word Data and add TopID.)

  30. Click Finish and back on the DataSet screen, you will see a new line added in the Table Adapter section of the Topic table showing the new query. The corresponding methods were generated in the class.

    The TopicLink table needs to be modified to show the corresponding LinkTitle for each record. In the Windows interface, it is not too difficult to show related data using a combo box in the grid, but in the Web interface, it would require converting the column to a Template, making it more complex. It is easier to modify the query to join the tables and return the Title information. This join will be added in another query, but the field needs to be accounted for in the default query so the schemas will be consistent.

  31. Modify the configuration of the TopicLink table (right click, configure) and add an empty field with the alias "LinkTitle". The SQL should now look like this:

    SELECT TopicLinkID, TopicID, LinkID, Comment, InActiveFlag, DateCreated,
        CreatedBy, '' as LinkTitle
    FROM dbo.TopicLink

  32. When VS added this new field to the TopicLink table, the default size was set to 1 character. This will give an error when the table is filled with actual titles in the next step, so the parameter must be changed manually. In the DataSet Designer screen, select the LinkTitle field in the TopicLink table and in the properties panel, change the MaxLength property to 2000.

  33. In the TopicLink table, follow the above procedures to create a new query to return only records for a specific TopicID. Add a new query and in the Query builder, add the parameter @TopicID to the filter column for the TopicID field. Also add a join for the Link table and replace the empty field. The SQL should look like this:

    SELECT TopicLink.LinkID AS TopicLinkID, TopicLink.TopicID, TopicLink.LinkID,
        TopicLink.Comment, TopicLink.InActiveFlag, TopicLink.DateCreated,
        TopicLink.CreatedBy, Link.LinkTitle
    FROM TopicLink INNER JOIN
        Link ON TopicLink.LinkID = Link.LinkID
    WHERE (TopicLink.TopicID = @TopicID)

  34. Click Next and name this query "FillByTopicID" and "GetByTopicID".

  35. Create another query for the TopicLink table that filters the LinkID by a parameter @LinkID. Use the following SQL and give it a name of FillByLinkID, GetByLinkID.

    SELECT TopicLinkID, TopicID, LinkID, Comment, InActiveFlag, DateCreated,
        CreatedBy, '' AS LinkTitle
    FROM TopicLink
    WHERE (LinkID = @LinkID)

  36. The third query needed for the TopicLink table is to get the records using the keyword search stored procedure. Right click on the TopicLink Table Adapter and select new Query. In the first wizard page, select "Use Existing Stored Procedure."

  37. In the second wizard page, select the stored procedure "Search by Keywords."

  38. In the third screen, select the "Tabular Data" option.

  39. In the last screen, name the query FillByKeyWords and GetByKeyWords and click Finish.

  40. For the Link table, create a query that gets the data by LinkID using the paramerter @LinkID using the following SQL.

    SELECT AccessCount, ArticleDate, Author, AvgRating, DateCreated, DateModified,
        Description, InActiveFlag, LinkID, LinkTitle, LinkTypeID, ModifiedBy,
        RatingCount, RatingTotal, URL
    FROM Link
    WHERE (LinkID = @LinkID)

  41. Also in the Link table, add another query to filter by TopicID. This will require adding a join for the TopicLink table to the query and filtering by @TopicID, but do not include the TopicID field in the output of the query.

    SELECT Link.AccessCount, Link.ArticleDate, Link.Author, Link.AvgRating,
        Link.DateCreated, Link.DateModified, Link.Description, Link.InActiveFlag,
        Link.LinkID, Link.LinkTitle, Link.LinkTypeID, Link.ModifiedBy,
        Link.RatingCount, Link.RatingTotal, Link.URL
    FROM Link INNER JOIN TopicLink ON Link.LinkID = TopicLink.LinkID
    WHERE (TopicLink.TopicID = @TopicID)

  42. One last thing, open the Server Explorer panel and drag the LinkRatingVote stored procedure and drop it on the Dataset screen. The wizard will create a special table adapter called QueriesTableAdapter for it and will generate the ADO code necessary to call the stored procedure.

  43. You should end up with your Dataset looking like this:

Data Access Logic

Now you need to create the Data Access logic to read information from the database to fill the dataset. This is an important part of an n-tiered architecture. Only the Business layer should have access to Data Access logic. Therefore, you need to provide a wrapper for the data access logic that can be referred to in the presentation layer. To begin with, use direct access to the database. In a later section, you will come back and provide the option getting the data through Web Services. This will show the importance of separating these layers as the business layer can be switched to access data via a Web Service without any changes necessary to the front-end.

Right-click anywhere in the Dataset designer screen and select View Code. This will create a partial class into the dataset where you can enter customized code. The Partial Class for the WebLinksDataset will be created automatically, but you can also create sub classes for each of the tables, rows, change events, and even the table adapters (in a different namespace.)

The goal is for each table to create a method to fill for each of the queries and to update. Additional methods will be created for the Web interface to return a table rather than fill an existing table so it will remain stateless.

Enter the following code:

Imports ta = Biz.WebLinksDataSetTableAdapters

Partial Class WebLinksDataSet

    Protected Shared taTopic As New ta.TopicTableAdapter
    Protected Shared taTopicLink As New ta.TopicLinkTableAdapter
    Protected Shared taLink As New ta.LinkTableAdapter
    Protected Shared taLinkType As New ta.LinkTypeTableAdapter
    Protected Shared taSP As New ta.QueriesTableAdapter

    Public Sub FillAll()
        LinkType.Fill()
        Link.Fill()
        Topic.Fill()
        TopicLink.Fill()
    End Sub

    Public Function SubmitRating(ByVal LinkID As Integer, ByVal NewRating As Integer) As Long
        Dim AvgRating As Long = 0 'output parameter returned
        AvgRating = taSP.LinkRatingVote(LinkID, NewRating, AvgRating)
        'update the avg rating in dataset
        Dim row As LinkRow = Link.FindByLinkID(LinkID)
        row.AvgRating = AvgRating
        row.EndEdit()
        row.AcceptChanges() 'no need to pass to the database
    End Function

#Region "Data Tables"
    Partial Class LinkDataTable
        Public Sub Fill()
            taLink.Fill(Me)
        End Sub

        Public Sub FillByLinkID(ByVal LinkID As Integer)
            taLink.FillByLinkID(Me, LinkID)
        End Sub

        Public Sub FillByTopicID(ByVal TopicID As Integer)
            taLink.FillByTopicID(Me, TopicID)
        End Sub

        Public Function GetByID(ByVal LinkID As Integer) As LinkDataTable
            Return taLink.GetByLinkID(LinkID)
        End Function

        Public Sub Update()
            taLink.Update(Me)
        End Sub
    End Class

    Partial Class TopicDataTable
        Public Sub Fill()
            taTopic.Fill(Me)
        End Sub

        Public Sub FillByTopID(ByVal TopLevelID As Integer)
            taTopic.FillByTopID(Me, TopLevelID)
        End Sub

        Public Function GetData() As TopicDataTable
            Return taTopic.GetData()
        End Function

        Public Sub Update()
            taTopic.Update(Me)
        End Sub
    End Class

    Partial Class TopicLinkDataTable
        Public Sub Fill()
            taTopicLink.Fill(Me)
        End Sub

        Public Sub FillByTopicID(ByVal TopicID As Integer)
            taTopicLink.FillByTopicID(Me, TopicID)
        End Sub

        Public Function GetByTopicID(ByVal TopicID As Integer) As TopicLinkDataTable
            Return taTopicLink.GetByTopicID(TopicID)
        End Function

        Public Sub Update()
            taTopicLink.Update(Me)
        End Sub

        Public Function GetByKeyWords(ByVal KeyWords As String) As TopicLinkDataTable
            taTopicLink.FillByKeyWords(Me, KeyWords)
            Return Me
        End Function
    End Class

    Partial Class LinkTypeDataTable
        Public Sub Fill()
            taLinkType.Fill(Me)
        End Sub
    End Class
#End Region
End Class

Save and close the Code window and the Dataset window.

The last step needed is to build the Biz project. In the Solution Explorer, right click on the Biz project and select Build.

This project will be referenced by the other projects making this logic available for other parts of the program.

Conclusion

In this first part of the tutorial, you have seen how Visual Studio 2005 can be used as a great tool for designing and building SQL Server 2005 (and previous versions) databases, whereas in previous versions, you needed several different tools.

With the significant advancements in the DataSet Designer and the partial classes, it is very easy to use visual tools to generate the entity objects and methods needed for our business logic. With Typed DataSets, our business logic layer can make use complex business objects with multiple tables, leveraging the relationships between tables rather than using simple business objects based on single tables.

In our next tutorial, you will create a Windows Forms based user interface for our application.

References

About the Author

David Catherman - CMI Solutions

Email: DCatherman (at) CMiSolutions (dot) com

David Catherman has 20+ years designing and developing database applications with specific concentration for the last 4-5 years on Microsoft .NET and SQL Server. He is currently Application Architect and Senior Developer at CMI Solutions using Visual Studio 2005 and SQL Server 2005. He has several MCPs in .NET and is pursuing MCSD.



Downloads

Comments

  • Thanks :)

    Posted by saicyLaxbic on 05/17/2013 09:22pm

    Thanks for supplying like wonderful articles.

    Reply
  • Thanks :)

    Posted by saicyLaxbic on 05/17/2013 11:21am

    Thanks with regard to supplying such awesome details.

    Reply
  • How To Hear To Tunes

    Posted by evaccelty on 05/09/2013 11:59am

    You will locate that at the time you start to use an MP3 player you will never ever go again to CD's ever yet again. . rihanna mp3 Stop by DSi Obtain Center for much more data. Most of the web-sites while declare to be a free web site or paid out site for come up with some concealed costs though in the system of downloading the file. Brande Edenfield

    Reply
  • Learn visual basic

    Posted by Nitn on 11/29/2012 12:15am

    I want to learn visual bassic. how to learn visual basic online and which site

    Reply
  • Programmer

    Posted by Edit-h on 08/01/2012 11:34am

    where is the link to the next tutorial mentioned above? thank you.

    Reply
  • I Like Study Visual Basic

    Posted by Junirullah on 07/05/2012 10:42pm

    I want to learn visual bassic. But I do not understand it because I do not have basic programming. How do you respond? Thank you. of Junirullah

    Reply
  • sql

    Posted by kuldipsinh on 06/28/2012 10:44am

    so nice and usable topic . Thaks.

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • On-demand Event Event Date: September 10, 2014 Modern mobile applications connect systems-of-engagement (mobile apps) with systems-of-record (traditional IT) to deliver new and innovative business value. But the lifecycle for development of mobile apps is also new and different. Emerging trends in mobile development call for faster delivery of incremental features, coupled with feedback from the users of the app "in the wild." This loop of continuous delivery and continuous feedback is how the best mobile …

  • Java developers know that testing code changes can be a huge pain, and waiting for an application to redeploy after a code fix can take an eternity. Wouldn't it be great if you could see your code changes immediately, fine-tune, debug, explore and deploy code without waiting for ages? In this white paper, find out how that's possible with a Java plugin that drastically changes the way you develop, test and run Java applications. Discover the advantages of this plugin, and the changes you can expect to see …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds