Using strongly typed data access in Visual Studio 2005 and 2.0

Source: Internet
Author: User
Tags date datetime query visual studio|visual| data

"Never put off until run time what can is done in compile time."
David Gries, Compiler construction for Digital Computers


As programmers, when we are learning some new technologies, examples can sometimes be our biggest enemies. Guides are often designed to be easy to understand, but at the same time lazy, inefficient, and even dangerous code writing can increase. The most prevalent example of such a situation is in the paradigm. In this article, we'll look at the significance of the strongly typed objects in the database, which will allow you to do so in your program, despite the lack of examples.
Somewhat specifically, we will see how strongly typed datasets are created and used in Visual Studio 2005. As this article explores, strongly typed datasets offer a number of benefits compared to another weakly typed data access technology. We'll also see here that creating and using a strongly typed DataSet with Visual Studio 2005 is not easier. If you want to learn more, keep watching.

The basics and benefits of strongly-typed Objects

To understand what a strong type means, you can think about a date first. If you are single, what kind of person would you consider dating? You may have certain criteria (such as health and attractiveness), or the standard is simple or not very clear. No matter what your condition is, when you decide to be more with whom, you will always use your own certain criteria to measure these types of consideration. If you're smart, you'll want to protect yourself from emotional trauma. You may find that, for example, getting along with an alcoholic is unstable unless there is a serious relationship between the two. But it is painful and very difficult to make a person change. Therefore, your wisdom will instruct you to stop the relationship before it begins. Adding a non-alcoholic clause to your dating standard protects you from future heartache and allows you to focus your time and energy on better candidates.
You may be amazed at how this reasoning relates to programming. It's okay, come with me, lovely reader! Ado. NET data Access objects are designed to be highly resilient. When you read data from a database, you may be working with common types of objects that are commonly allowed in the. NET framework, unless you encounter special problems. By applying our dating theory, you can basically think of your relevant data as a generic object. "I just want to go on a date that's not too much trouble." "Can't you be more specific?" he said. There is no limit to even people or other creatures! As your friend, I implore you, "a little more standard!" Let your list shrink a little! "
Just as you ignore the question of who the date is and cause future relationship problems, allowing your objects in your code can also cause some errors. And if you let the old object dance in your subroutine, you may not find it a problem until the program runs. In our dating theory, catching errors at run time is like a painful and embarrassing quarrel between your date and a trendy Italian restaurant. Yes, you see, if you had a plan before, you wouldn't end up in a bunch of diner's eyes and not be embarrassed. Simply apply some strict criteria in your code and you can catch the error before the program starts compiling. For example, the following code example:

String FirstName = Myrow. ("FirstName"). ToString ();
The DataRow in this example is no type, and the result is that you have to use the name of the column as a string to get the value you need (or you can select the index in the column collection of the record). The good news is that the list does exist. The data type of the DataRow column is object, and we assume that the data type below the FirstName column is string, and we must explicitly convert it to string before we use it. If the name of the column changes (for example, to Personfirstname), the compiler has no way to notify you. Depressing, huh? But you can not do that. If your code looks like this, your life will be simpler and your code will be more reliable.

string FirstName = Personrow.firstname;

In this second example, we use a strongly typed row, and we know that the FirstName property is of type string. There are no messy column names, and there is not a messy type conversion. The compiler has done a type check for us and we can rest assured that there is no need to worry about getting the names right.
Everything else is the same, so you will definitely not hesitate to use this approach, instead of using the generic type. But wait a minute, where did the strong type objects come from? And I wish I could tell you these objects are created automatically. However, just as a good relationship takes time and energy, making your objects strong type also requires extra effort. But the extra time spent here is definitely worth it, and it also saves more exponential time in the future when catching bugs.
There are several ways to complete a strong type, and we'll explain how to create a strongly typed dataset in Visual Studio 2005 in the remainder of this article. We will also compare this practice with the pros and cons of other practices.

Creating strongly-typed Datasets in Visual Studio 2005


Strongly typed dataset simply defines the columns and tables of a normal dataset, so the compiler already knows what they contain. Instead of a loose wrapper with a baseball glove, a strongly typed dataset is just like a glove. Each successive version of Visual Studio makes it easier to handle the data set strongly typed. In the following example, we will use SQL Server 2005 's AdventureWorks database. Simply follow these steps:
1. Open Visual Studio and create a new web site.
2. In the Solution Explorer window, right-click the new item and select the dataset. Name it adventureworks.xsd (see screenshot). Visual Studio will recommend that you put the dataset file in the App_Code file, and you just have to agree.
3. After opening adventureworks.xsd, the TableAdapter Configuration Wizard will run. At this point, click Cancel and we will drag the desired table from the Server Explorer.
4. Browse the Server Explorer toolbar to find the AdventureWorks database. (If you have not yet installed the AdventureWorks database, you can go to Microsoft's download page SQL Server databases samples and sample download it and some other SQL Server 2005 examples)
5. Drag the SalesOrderHeader table and the SalesOrderDetail table into the dataset's design window. The window should look like the screenshot. What are we looking at? Whenever we add a table, Visual studio creates a strongly typed DataTable (with the same name as the original table) and a TableAdapter. This DataTable has defined each column for us. TableAdapter is what we use to populate the table, and by default there is a fill () method that gets each row of data from the original table.

As it turns out, this strongly typed DataSet will return all of the records for both tables. But the AdventureWorks database contains a lot of order information, so why not create a more specific query? We can add a method to the TableAdapter object to get a specific set of child recordsets. Right-click Salesorderheadertableadapter, then select add| Query. Select "Use SQL statements" after the next step, then select "Select which returns rows" and then click Next. Recently, enter the following query statement in the window (or you can do this with Query builder):

SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate,
Status, Onlineorderflag, SalesOrderNumber, Purchaseordernumber,
AccountNumber, CustomerID, ContactID, SalesPersonID, TerritoryID,
Billtoaddressid, Shiptoaddressid, Shipmethodid, Creditcardid,
Creditcardapprovalcode, Currencyrateid, SubTotal, Taxamt, Freight,
TotalDue, Comment, rowguid, ModifiedDate

From Sales.SalesOrderHeader
WHERE (OrderDate > @OrderDate)
This SQL query is a simple select query that uses a @orderdate parameter to filter the results. This will allow us to not return all the records in the database. Keep the "Fill a DataTable" and "Return a DataTable" check boxes selected and point complete. After adding this SELECT statement, your designer should now look like a screenshot, with one more query under Salesorderheadertableadapter.

When a strongly typed DataSet is set up, we can easily display the data in a page with a few lines of code. Create a new page in your Web site and go to design mode. Drag a GridView control to the top, leaving its ID as GirdView1. Then go to the Source code page and introduce the Adventureworkstableadapters namespace above the file (in C # syntax is a using Adventureworkstableadapters;). Finally, add the following code to the Page_Load event:

Create the Salesorderheadertableadapter
Salesorderheadertableadapter Salesadapter =
New Salesorderheadertableadapter ();

Get orders so took place after July 1st, 2004
Adventureworks.salesorderheaderdatatable Orders =
Salesadapter.getdataby (New DateTime (2004, 7, 1));

Bind The order results to the GridView
This. Gridview1.datasource = Orders;
This. Gridview1.databind ();

The code is very simple. We create an instance of Salesorderheadertableadapter to populate the datasheet. Notice here that unlike the normal DataTable, we declare a salesorderheaderdatatable type object, and we call the Getdateby () method, passing a DateTime object to populate the data. Also notice here that the commands are strongly typed, so we have to pass a DateTime object instead of an ordinary object. The screenshot below is the result of the code example above.

In addition to binding the result set to the GridView in code, you can also use a ObjectDataSource, Set its TypeName property to Adventureworkstableadapters.salesorderheadertableadapter, and its selectmethod to GetData or Getdataby.
In addition to connecting the database without writing code, another great advantage of using a strongly typed dataset is that there is no column name string lurking in our code that the compiler cannot check. Nor do we need to do any type conversions. If the database schema changes, as soon as the Adventureworks.xsd file is updated, we will find that all relevant changes are automatically completed at compile time.

Other techniques for generating strongly-typed data-access applications

In addition to using strongly typed datasets, there are other ways to implement strong typing in your program. You can create a custom class that is lighter and more consistent with your database than datasets. There are also third-party software developers who have developed tools to automate this process. One of the more special things I prefer is Llblgen Pro, a book I've written about it: Rapid C # Windows development:visual Studio, SQL Server, and Llblgen Pro. (You can read this book 1/3 for free on my website.) Another popular tool is Codesmith. Even Microsoft is developing a gadget called dlinq, but it is still being tested, and it is expected to wait for at least a few years before it is launched.
If you use Visual Studio's strong dataset approach, one of the advantages is that you don't need to purchase other software. All of these solutions have different features and benefits, but the main benefits are reliability, fewer bugs, and less time to debug. It is also easier to check the impact of database schema changes and maintain them. I hope you have recognized the benefits of a strong type. Good luck in development (dating too)!

by Joseph Chancellor


Download the code examined in this article
About the Author
Joseph Chancellor is a C # developer in southern California who has had his fair share of relational. He appreciates all kinds of feedback and suggestions. Visit his blog or read the "the" five chapters of his book on Visual Studio, SQL Server, and Llblgen Pro.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.