Working with ADO.NET C# Help

This section addresses some common scenarios when developing data access applications with ADO.NET.

Tiered Development

Producing an application that interacts with data is often done by splitting up the application into tiers. A common model is to have an application tier (the front end), a data services tier, and the database itself.

One of the difficulties with this model is deciding what data to transport between tiers, and the format that it should be transported in, With ADO.NET you will be pleased to learn that these wrinkles have been ironed out, and support for this style of architecture is part of the design.

One of the things that is much better in ADO.NET than OLE DBis the support for copying an entire record set, In .NET it is easy to copy a DataSet:

DataSet source = {some dataset};
DataSet dest = source.Copy{};

This creates an exact copy of the source DataSet – each DataTable, DataColumn, DataRow, and Relation will be copied, and all data will be in exactly the same state as it-was in the source. If all you want to copy is the schema of the DataSet, you can use the following code:

Dat.set source = {some dataset};
DataSet dest = source.Clone();

This again copies all tables, relations, and soon, However, each copied DataTable will be empty, This
process really couldn’t be more straightforward.

A common requirement when writing a tiered system, whether based on a Windows client application or the Web, is to be able to ship as little data as possible between tiers, This reduces the amount of resources consumed.

To cope with this requirement, the DataSet class has the GetChanges () method. This simple method performs a huge amount of work, and returns a DataSet with only the changed rows from the source data set. This is ideal for passing data between ders because only a minimal set of data has to be passed along.

The following example shows how to generate a “changes” DataSet:

DataSet source = {some datacast};
DataS dest = source.GetChanges();

Again, this is trivial. Under the hood, things are a little more interesting. There are two overloads of the
Get Changes () method, One overload takes a value of the DataRowState enumeration, and returns u.tly rows that correspond to that state (or states), GetChanges () simply calls GetChanges (Deleted, Modified I Added), and first checks to ensure that there are some changes by calling Has Changes (), If no changes have been made, null is returned to the caller immediately.

The next operation is to clone the current DataSet. Once done, the new DataSet is set up to ignore constraint violations (EnforceConstraints = false), and then each changed row for every table is copied into the new DataSet.

When you have a DataSet that JUSt contains change, you can then move these off to the data services tier for processing, After the data has been updated in the- database, the “changes” DataSet can be returned to the caller for example there might be some output parameters from the tored procedures that have updated values the columns), These changes can then be merged into the.original DataSet using the merged method, Figure 26·9 depicts this sequence of operations.

Figure 26-9

Figure 26-9

Key Generation with SQL Server

The region insert stored procedure presented earlier in this chapter is one example of generating a primary key value on insertion into the data base, The method for generating the key in this particular example is fairly crude and wouldn`t scale well, so for a real application you should use some other strategy some other keys.

Figure26-10

Figure26-10

The script for this table include in the stored proos .sql file, The AuditID Coloumn is defined as an IDENTITY coloumn you then construct a couple of database triggers that will record changes to the Category name field:

If you are used to Oracle stored procedures, SQL Server doesn’t exactly have the concept of OLD and NEWrows; instead, for an insert trigger there is 1n In-memory table called Inser •e.d.,and for deletes and updates the old rows are available within the Deleted table.

This trigger retrieve the CategoryID of the record(s) affected and stores this together with the old and new value of the CategoryName column,

Now, when you call your original stored procedure to insert a new Category!;), vou receive an Identity value; however, this is no longer the identity value from the row inserted into the Categories table – it is now the new value generated for the row in the Ca tegoryAudit table Ouch!

To view the problem fi.st-hand, open a copy of SQL Server Enterprise Manager, and view the Contents of the Categories table (see Figure 26-11).

Figure 26-11

Figure 26-11

This lists all the categories in the Northwind database, The next identity value for the Categories table should be 9. so a new row can be inserted by executing the following code, to see what ID is returned:

DECLARE@CatIDint;
EXECUTECategoryIn~ert ‘Pasties’ , ‘Heaven Sent Food’ , @CatIDOUTPUT;
PRINT@CatID;

The output value of this on a test PC was 1. If you look at the CategoryAudi t table shown in Figure 26-12, you will find that this is the identity of the newly inserted audit record, not the identity of the category record created.

Figure 26-12

Figure 26-12

The problem lies in the way that @@IDENTITY actually works. It returns the LASTidentity value created by your session, so as shown in Figure 26-12, it isn’t completely reliable, Two other identity functions can be used instead of @@IDENTITYbu,t neither is free from possible problems, The first, SCOPE_IDENTITY( ), returns the last identity value created within the current scope, SQLServer defines scope as a stored procedure, trigger, or function, This may work most of the time, but if for some reason someone adds another INSERTstatement into the stored procedure, you can receive this value rather than the one you expected, The other irJentity function, IDENT_CURREN( )T, returns the last identity value generated for a given table in any scope. For example, if two users were accessing SQLServer at exactly the same time, it might be possible to receive the other user’s generated identity value, As you might imagine, tracking down a problem of this nature isn’t easy, The moral of the story is to beware when using IDENTITYcolumns in SQLServer.

Naming Conventions

The following tips and conventions are not directly .NET-related. However, they are worth sharing and following, especially when naming constraints. Feel free to skip this section if you already have your own views on this subject.

¤ Always use singular names – Product rather than Products. This one is largely due to having to explain a database schema to customers; it is much better grammatically to say “The Product table contains products” than “The Products table contains products.” Check out the Northwind database to see an example of how not to do this.

¤ Adopt some form of naming convention for the fields that go into a table – Ours is _ <Table> _Id for the primary key of a table (assuming that the primary key is a single column), Name for the field considered to be the user-friendly name of the record, and Description for any textual information about the record itself. Having a good table convention means you can look at virtually any table in the database and instinctively know what the fields are used for.

Conventions for Database Columns

¤ Use singular rather than plural names.

¤ Any columns that link to another table should be named the same as the primary key of that table. For example, a link to the Product table would be Product_Id, and to the Sample table Sample_Id. This isn’t always possible, especially if one table has multiple references to another, In that case, use your own judgment.

¤ Date fields should have a suffix of _On, as in Modified_On and Created_On. Then it is easy to read some SQLoutput and infer what a column means just by its name.

¤ Fields that record the user should be suffixed with _By, as in Modified_By and Created_By. Again, this aids legibility.

Conventions for Constraints

¤ If possible, include in the name of the constraint the table and column name, as in CK_<Table>_ <Field>. For example, CK_Person_Sex for a check constraint on the Sex column of the Person table. A foreign key example would be FK_Product_Supplier_Id, for the foreign key relationship between product and supplier.

¤ Show the type of constraint with a prefix, such as CKfor a check constraint and FKfor a foreign key constraint. Feel free to be more specific, as in CK_Person_Age_GTO for a constraint on the age column indicating that the age should be greater than zero.

¤ If you have to trim the length of the constraint, do it on the table name part rather than the column name. When you get a constraint violation, it is usually easy to infer which table was in error, but sometimes not so easy to check which column caused the problem. Oracle has a 3O-character limit on names, which is easy to surpass.

Stored Proceduces

Just like the obsession many have fallen into over the past few years of putting a C in front of each and every class they declare (you know you have!), many SQLServer developers feel compelled to prefix every stored procedure with sp_ or something similar. This is not a good idea.

SQL Server uses the sp_ prefix for all (well, most) system stored procedures. So, you risk confusing your users into thinking that sp_widget is something that comes as standard with SQLServer. In addition, when looking for a stored procedure, SQL Server treats procedures with the sp_ prefix differently from those without it.
H you use this prefix and do not qualify the database/owner of the stored procedure, SQLServer will look in the current scope and then jump into the master database and look up the stored procedure there.
Without the sp_ prefix, your users would get an error a little earlier. What’s worse, and also possible to
do, is to create a local stored procedure (one within your database) that has the same name and parameters as a system stored procedure. Avoid this at all costs – if in doubt, don’t prefix,  When railing stored procedures, always prefix them with the owner of the procedure, as in ADO. selectWidgets. This is slightly faster than not using the prefix, because SQLServer has less work to do to find the stored procedure. Something like this is not likely to have a huge impact on the
execution speed of your application, but it is a tuning trick that is essentially available for free, Above all, when naming entities, whether within the database or within code, be consistent.

Posted on October 30, 2015 in Data Access

Share the Story

Back to Top