Data Access Controls and Techniques

Sql transaction with rollback and savepoint !

2008-07-04T12:45:00.000-07:00

using (SqlConnection connection =
new SqlConnection(connectionString))
{
using (SqlCommand command =
connection.CreateCommand())
{
connection.Open();

SqlTransaction transaction =
connection.BeginTransaction();

command.Transaction = transaction;

try
{
command.CommandText = "Update...";
command.ExecuteNonQuery();

// Create a Savepoint
transaction.Save("FirstUpdate");

command.CommandText = "Insert...";
command.ExecuteNonQuery();

// Rollback to FirstUpdate
transaction.Rollback("FirstUpdate");

transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}

source:http://davidhayden.com/blog/dave/archive/2005/10/15/2517.aspx

Expert business object CLSA.net

2008-07-04T12:13:00.000-07:00

Rockford describes n-tier architecture from both a logical and physical perspective. When most people talk about n-tier applications, they're talking about physical models in which the application is spread across multiple machines and different functions: a client, a web server, an application server, a database server, and so on.

Logical n-tier architecture (personally I call this a layered architecture) is about spreading different types of functionality. The most common logical separation is into a UI tier, a business tier, and a data tier that may exist on a single machine, or on three separate machines - the logical architecture doesn't define those details.

Rockford describes the Five Logical Tiers and the Roles They Provide:

Presentation - Renders display and collects user input

UI - Acts as an intermediary between the user and the business logic, taking use input and providing it to the business logic, then returning results to the user.

Business logic - Provides all business rules, validation, manipulation, processing, and security for the application.

Data access - Acts as an intermediary between the business logic and data management. Also encapsulates and contains all knowledge of data-access technologies (such as ADO.NET), databases, and data structures.

Data storage and management - Physically creates, retrieves, updates, and deletes data in a persistent data store.

Anchored object - When an anchored object is "passed" from one machine or process to another, only a pointer, or reference, is passed, not the object itself.

Unanchored object - When an unanchored object is "passed" from one machine or process to another, the object is physically copied and passed by value.

source: http://davidhayden.com/blog/dave/archive/2004/09/11/471.aspx

Customize SqlDataAdapter with your own commands !!

2008-07-02T05:03:00.000-07:00

public static SqlDataAdapter CreateCustomerAdapter(

SqlConnection connection)

{

SqlDataAdapter adapter = new SqlDataAdapter();

// Create the SelectCommand.

SqlCommand command = new SqlCommand("SELECT * FROM Customers " +

"WHERE Country = @Country AND City = @City", connection);

// Add the parameters for the SelectCommand.

command.Parameters.Add("@Country", SqlDbType.NVarChar, 15);

command.Parameters.Add("@City", SqlDbType.NVarChar, 15);

adapter.SelectCommand = command;

// Create the InsertCommand.

command = new SqlCommand(

"INSERT INTO Customers (CustomerID, CompanyName) " +

"VALUES (@CustomerID, @CompanyName)", connection);

// Add the parameters for the InsertCommand.

command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");

command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");

adapter.InsertCommand = command;

// Create the UpdateCommand.

command = new SqlCommand(

"UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +

"WHERE CustomerID = @oldCustomerID", connection);

// Add the parameters for the UpdateCommand.

command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");

command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");

SqlParameter parameter = command.Parameters.Add(

"@oldCustomerID", SqlDbType.NChar, 5, "CustomerID");

parameter.SourceVersion = DataRowVersion.Original;

adapter.UpdateCommand = command;

// Create the DeleteCommand.

command = new SqlCommand(

"DELETE FROM Customers WHERE CustomerID = @CustomerID", connection);

// Add the parameters for the DeleteCommand.

parameter = command.Parameters.Add(

"@CustomerID", SqlDbType.NChar, 5, "CustomerID");

parameter.SourceVersion = DataRowVersion.Original;

adapter.DeleteCommand = command;

return adapter;

}

DAL and BLL ?

2008-07-01T05:22:00.000-07:00

The main difference between the business logic layer and data access layer is that it returns a strongly-typed RecordCollection of Record objects, rather than a DataView. It also allows for Update, Insert, and Delete operations that take this Record object as a parameter. The ObjectDataSource DataObjectTypeName property allows you to configure the ObjectDataSource to pass this type to methods instead of individual field values. In the business logic layer method implementations, you can include custom logic for validating business rules. For example, you could ensure that only Records in the "In Review" category can be updated or only Administrators can insert new records. You can also include validation logic to ensure the data types and values supplied as arguments are correct before inserting or modifying data in the database. Note that validation logic in business layer is not a substitute for input validation at the presentation layer, which helps guide the end user to enter the correct values before submitting the update.

source:http://quickstarts.asp.net/QuickStartv20/aspnet/doc/ctrlref/data/objectdatasource.aspx

Server.HTMLEncode Method

2008-07-01T03:54:00.000-07:00

The HTMLEncode method applies HTML encoding to a specified string. This is useful as a quick method of encoding form data and other client request data before using it in your Web application. Encoding data converts potentially unsafe characters to their HTML-encoded equivalent.

If the string to be encoded is not DBCS, HTMLEncode converts characters as follows:

The less-than character (<) is converted to <.
The greater-than character (>) is converted to >.
The ampersand character (&) is converted to &.
The double-quote character (") is converted to ".
Any ASCII code character whose code is greater-than or equal to 0x80 is converted to &#, where is the ASCII character value.

source:http://msdn.microsoft.com/en-us/library/ms525347.aspx

Determine How ASP.NET Web Pages Were Invoked

2008-07-01T03:42:00.000-07:00

It is often useful to know how an ASP.NET Web page was invoked: whether by an original request (an HTTP GET), a postback (an HTTP POST), a cross-page post from another page (an HTTP POST), or a transfer from another page using the Transfer method or using a callback from the browser. The Page class exposes a set of properties that you can use to determine how a page was invoked.

To determine how an ASP.NET Web page was invoked
• Examine the values of the following Page class properties, and then refer the table to determine how the page was invoked:
o IsPostBack
o PreviousPage
o IsCrossPagePostBack
o IsCallback

Original request
• IsPostBack is set to false.
• PreviousPage is set to null (Nothing in Visual Basic).
• IsCallback is set to false.
Postback
• IsPostBack is set to true.
• PreviousPage is set to null (Nothing in Visual Basic).
• IsCallback is set to false.
Cross-page posting
• IsPostBack is set to false.
• PreviousPage references the source page.
• IsCrossPagePostBack is set to true.
• IsCallback is set to false.
Server transfer
• IsPostBack is set to false.
• PreviousPage references the source page.
• IsCrossPagePostBack that is referenced in the PreviousPage is set to false.
• IsCallback is set to false.
Callback
• IsPostBack is set to false.
• PreviousPage is set to null (Nothing in Visual Basic).
• IsCallback is set to true.

source: http://msdn.microsoft.com/en-us/library/ms178141.aspx

Cross-Page Posting in ASP.NET Web Pages

2008-07-01T03:31:00.000-07:00

Under some circumstances, you might want to post one page to another page. For example, you might be creating a multi-page form that collects different information on each page. In that case, you can configure certain controls (those that implement the IButtonControl interface, such as the Button control) on the page to post to a different target page. This is referred to as cross-page posting. Cross-page posting provides some advantages over using the Transfer method to redirect to another page.

Getting Information from the Source Page

Getting Control Values

If the source and target page are in different applications, you cannot directly get the values of controls on the page, but you can read the posted data from the Form dictionary. You cannot read view state from the source page, because it is hashed. If you want to store values in the source page and make them available in a target page in another application, you can store the values as strings inside hidden fields on the source page and access them through Request.Form on the target page.
Using the reference in the PreviousPage property, you can search for controls on the source page and extract their value. You typically do this with the FindControl method.

if (Page.PreviousPage != null)
{
TextBox SourceTextBox =
(TextBox)Page.PreviousPage.FindControl("TextBox1");
if (SourceTextBox != null)
{
Label1.Text = SourceTextBox.Text;
}
}
------------------------------------------------------------

Login LoginControl = (Login)PreviousPage.FindControl("Login1");
if (LoginControl != null)
{
TextBox UserName = (TextBox)LoginControl.FindControl("UserName");
if (UserName != null)
{
Label1.Text = UserName.Text;
}
}
else
{
Label1.Text = "Cannot find user name in Login control.";
}

Getting Public Property Values from the Source Page

To get public members of the source page, you must first get a strongly typed reference to the source page.
You can do so in a number of ways. The first is to include an @ PreviousPageType directive in the target page, which allows you to specify the source page
<%@ PreviousPageType VirtualPath="~/SourcePage.aspx" %>
Label1.Text = PreviousPage.CurrentCity;

Another way to get a strongly typed reference to the source page is to include an @ Reference directive in the target page that references the source page, as you would reference any type that you wanted to use in your page. In that case, in the target page you can get the target page's PreviousPage property and cast it to the source page type.
SourcePage_aspx sourcePage;
sourcePage = (SourcePage_aspx) PreviousPage;
Label1.Text = sourcePage.CurrentCity;

Checking for Postbacks in the Target Page

During a cross-page postback, the contents of the source page's controls are posted to the target page, and the browser executes an HTTP POST operation (not a GET operation). However, in the target page, the IsPostBack property is false immediately after a cross-page post. Although the behavior is that of a POST, the cross-posting is not a postback to the target page. Therefore, IsPostBack is set to false and the target page can go through its first-time code.
If it is useful in your application, you can determine whether the target page is running as the result of a cross-page post. To do so, you can test the IsCrossPagePostBack property of the page reference returned by the target page's PreviousPage property.

if(PreviousPage != null)
{
if(PreviousPage.IsCrossPagePostBack == true)
{
Label1.Text = "Cross-page post.";
}
}
else
{
Label1.Text = "Not a cross-page post.";
}

source:http://msdn.microsoft.com/en-us/library/ms178139.aspx#GettingPublicPropertyValuesFromSourcePage

DBNull!! you cannot ignore and the semantic difference between as operator and casting

2008-06-30T23:57:00.001-07:00

Since the beginning of my adventure with .NET I have always asked myself one question: why do we need the DBNull type? Is a simple null reference not enough?
MSDN says that DBNull "Represents a nonexistent value". From a logical point of view - this one sentence explains why a null reference cannot be used - because it is a null reference and not a lack of value. But is it enough to introduce a type that causes a lot of trouble?

DBNull is typically used in ADO.NET when returning data from the database. If one column does not have a value in the database, it will be represented as a DBNull object. That is something altogether different from what I would expect at the domain level. When working with objects when there is a Client object, and I ask for his Name then if the name is not there I expect to get null in return. It is very natural to me and probably to most of the developers. When we need to interact with data layer that is where most of the problems with DBNull kick in.

Consider a simple example of a Client's Name property which is of a String type. In your code you accept a situation when the Name is null which may mean that it has never been set. When you save such a Client to a database using a simple Insert call, everything works OK. (it was in fact my data access library that handled null inserts by changing them to DBNulls) You check the database and the data is in fact there and the Name is "null". When next time you try to get the Client from the database, you have to read all the columns and put the values into your object like in the following example:
Client c = new Client();
c.Id = (Guid)row["Id"];
c.Name = (string)row["Name"];
...
This will result in an InvalidCastException exception being thrown with a message "Unable to cast object of type 'System.DBNull' to type 'System.String'." That is of course because null values from the database are represented as DBNull objects.
Typical solution for this problem is to check the column for DBNull before casting or using an "as" operator which you have to be carefull with as I describe in another post.
Whichever way you choose, what you get is an overly complicated code that does a simple thing!
I don't think that the sentence from MSDN that describes the purpose of DBNull is a good excuse for introducing DBNull type. I don't think that returning null instead of DBNull would cause any loss of information. What I think is that DBNull should have never been introduced because it makes developer's work harder without adding any real value. Additionally ADO.NET is inconsistent in that it allows saving a null referrence to a database but does return DBNull instead. Because of the fact that I don't like to deal with DBNulls, in my data access library, I'm always replacing it with a null reference so I never get it to propagate to the upper layers. That way I get a better separation from a database level stuff.

Semantical Difference Between as Operator And Casting
The "as" operator as described in MSDN is an operator: "Used to perform conversions between compatible reference types". We have to keep this in mind when coding. Often it is just easier (and it looks nicer too) to use an "as" operator instead of a cast, or when we want to handle the DBNull value returned from the database like in the following scenario:
string s = reader["Name"] as string;
instead of
string s = (string)((reader["Name"] is DBnull) ? null : reader["Name"]);
Assuming that the name column can be null in the database.
When we use it we accept that something could be of incompatible type such as Name column being of numeric type return. What happens then? What happens if the column type has changed? In the scenario above, we will always get a null reference, which in a worst case scenario we will discover very late in project.
Of course in some situations it is just what we need. What I have found however is that misusing the "as" operator can often lead to unexpected NullReferrenceException exceptions being thrown (or worse yet nothing wrong happens at first). If you find that happening it may mean that you are using "as" operator as a shortcut where an ordinary cast would be in place. Using a cast often causes bugs to appear sooner in the development life cycle which is always a nice thing. So basically a rule of thumb is to always think twice whether to use a cast or an "as" operator.

source:http://vaultofthoughts.net/WhatIsDBNullNeededForAnyway.aspx

http://aspalliance.com/1460_More_InDepth_About_Nulls_And_DBNull.all

Client Callback Demystified :

2008-06-29T10:01:00.000-07:00

In asp.net 2 making a client callback is a 4 step process:

1. First of all you need to retrieve some data from the client side, which is accessed by using DOM and JavaScript.
2. The data accessed in step 1 will be sent to the server side function which will process the data, for that you need a client side function which will call that server side method asynchronously.
3. The server method called asynchronously in step 2 will now process the data and then return the some result back to the client.
4. The result produced in step 3 by the server and sent to the client will be used to update the client by using DOM and JavaScript.

Implementing Client Callback functionality on the Server

To implement the client callback on the server side your class must implement the ICallbackEventHandler interface. In our case the Page class is the class which will implement this interface. This interface is used to indicate that a control can be the target of callback event on the server, i.e. a client can call its methods. The interface implements two methods RaiseCallbackEvent and GetCallbackResult.
RaiseCallbackEvent method takes one string argument. This is the method actually called by the client side function and passes the username text box value in the string argument. Once this method produces result then GetCallbackResult function is invoked and returns the string produced by RaiseCallbackEvent. GetCallbackResult does not take any argument, and RaiseCallbackEvent returns void.

Calling the Server Method

Now we need a function which can call the server method asynchronously. We use the GetCallbackEventReference method of the ClientScript property of the Page class in Asp.net 2, and it generates the code to call the server method.

The GetCallbackEventReference method takes five arguments as follows:

1. The reference of the control which has implented the ICallbackEventHandler interface. You do this by providing 'this' keyword in the first parameter. Now the GetCallbackEventReference know that the current class will have a method RaiseCallbackEvent which needs to be invoked.
2. The string value that the client must pass to the server-side method (RaiseCallbackEvent). This will be the value of the username textbox which we have extracted using lookup Value () client side function.
3. The name of the JavaScript function (displayResult) that will be automatically called when the response from the server arrives. When the GetCallbackResult method returns value that value is catches by displayResult client side function's parameter (rValue).
4. The string value that is passed to the dispalyResult function. (This parameter will not be used in this tutorial series.)
5. A boolean value that specifies whether the callback should be done asynchronously. In our case it will be true. Controls like GridView uses synchronous Callbacks.

So how this all is going to work?

GetCallbackEventReference returns a string, which actually will be the content of the function which calls the server side method (RaiseCallbackEvent). The function which calls the server side method will be created in the Load event of the page using RegisterClientScriptBlock method of the ClientScript property of the page.
The names of the arguments that are accepted by the function you are generating ust match the names of the values that you pass to the GetCallbackEventReference method.

Note: Read the following steps slowly and review your code after each step.

1. The lookupValue javascript function gets the value from the textbox. Written by you in the script section.
2. The lookupValue function then passes the value of the textbox to the CallServer function. The callserver function is created from the Page_Load event and GetCallbackEventReference was passed as its content.
3. The CallServer function invokes the RaiseCallbackEvent method of the page, with the help of GetCallbackEventReference. The RaiseCallbackEvent takes a parameter arg in CallServer and eventArgument in RaiseCallbackEvent. RaiseCallbackEvent checks for the user and stores a string result in a variable.
4. The result is returned to the client from the GetCallbackResult method, which is invoked by GetCallbackEventReference.
5. The GetCallbackEventReference then passes the result to the displayResult function on the client side, and that function sets the inner html of the result span to the result sent by GetCallbackEventReference.

source:http://www.themastech.net/Tutorials/ClientCallbacks/Default.aspx

Dynamic controls: Is necessary ?

2008-06-26T03:21:00.000-07:00

Building the tree is your responsibility. Whether you do it dynamically or statically, it doesn't matter. One way or another, the controls of the tree must be completely reconstructed upon each and every request to the page. A common mistake made by developers is to add a dynamic control to the tree only in response to some event. For example, lets say you have a button on the form that reads, "Click here to enter your name", and when clicked you want to dynamically add a TextBox for them to enter it into:

private void cmdEnterName_Click(object sender, EventArgs e) {

TextBox txtFirstName = new TextBox();

this.Controls.Add(txtFirstName);

}

If you try it, you'll see the TextBox like you expect. But on the next postback, unless the user clicks this very same button a second time, the TextBox will cease to exist! Many developers who run into issues like this believe it to be due to broken ViewState. They scour the internet with queries like "how to enable ViewState for dynamic controls". It has nothing to do with ViewState. ViewState is responsible for maintaining the state of the controls in the control tree -- not the state of the control tree itself! That responsibility lies wholly on your shoulders. Dynamically created controls will not exist on the next postback unless you add them to the control tree once again. Once a control is a member of the tree, the framework takes over to maintain the control's ViewState.

Okay. How then can we solve the problem stated in the example? As I've mentioned already -- and you will hear more about it later on -- when it comes to dynamic controls, just because you can doesn't mean you should! It's always better to statically declare controls whenever it is humanly possible. You will save yourself a lot of work. So the solution here is to avoid using dynamic controls in the first place.

Simply declare that TextBox on the form, but mark it as Visible="false". Then when you need it, just make it Visible. When you don't need it any more, make it not visible again. Simple as can be.

By the way, some of you may be aware of a little control out there called the DynamicPlaceHolder. I am reluctant to even mention it, because if you haven't heard of it you might go looking for it and then you will be tainted. Please, don't use it!!! I'm not saying there aren't times where a control like that could be useful -- but 99.999% of the time it is the wrong way go about solving a dynamic control problem. There are all sorts of disadvantages to using it, all of which can be avoided by solving the problem "correctly" to begin with. I believe this so firmly that if you find yourself using it, I will personally work with you via email to find a better way.

Credit to: Dave Reed

source:http://weblogs.asp.net/infinitiesloop/archive/2006/08/30/TRULY-Understanding-Dynamic-Controls-_2800_Part-2_2900_.aspx

Populate DataTable, Insert, Update and Delete on it.

2008-06-15T01:07:00.000-07:00

There are two patterns for populating data:

Method 1 : Fill a DataTable – with this approach a method is created that takes in a DataTable as a parameter and populates it based on the results of the query. The ADO.NET DataAdapter class, for example, implements this pattern with its Fill() method.

Method 2 : Return a DataTable – with this approach the method creates and fills the DataTable for you and returns it as the methods return value.

There are two patterns commonly used for inserting, updating, and deleting data.

Method 1 : Database direct pattern, involves creating methods that, when invoked, issue an INSERT, UPDATE, or DELETE command to the database that operates on a single database record. Such methods are typically passed in a series of scalar values (integers, strings, Booleans, DateTimes, and so on) that correspond to the values to insert, update, or delete.

Method 2: Batch update pattern, is to update an entire DataSet, DataTable, or collection of DataRows in one method call. With this pattern a developer deletes, inserts, and modifies the DataRows in a DataTable and then passes those DataRows or DataTable into an update method. This method then enumerates the DataRows passed in, determines whether or not they've been modified, added, or deleted (via the DataRow's RowState property value), and issues the appropriate database request for each record.

The TableAdapter uses the batch update pattern by default, but also supports the DB direct pattern.

Both data modification patterns use the TableAdapter's InsertCommand, UpdateCommand, and DeleteCommand properties to issue their INSERT, UPDATE, and DELETE commands to the database.

source : http://msdn.microsoft.com/en-us/library/ms233701(VS.80).aspx

searching, filtering and sorting in typed dataset using DataTable and custom expressions

2008-06-10T09:24:00.000-07:00

Searching :

step 1: create typed dataset
step 2: create datatable
step 3: use the datatable findby() function passing primary key to get a particular row.

Filtering :

step 1: create typed dataset
step 2: create datatable
step 3: use the datatable select() function passing selecting condition to get multiple rows.

Sorting :

step 1: create typed dataset
step 2: create datatable
step 3: use the datatable select() function passing selecting condition to get multiple rows with additional parameter of columnname by which it is to be sorted.

Custom Expression :

step 1: create typed dataset
step 2: create datatable
step 3: add column whose data is some combination of other columns.
step 4: additional function compute() of datatable to appply certain function such as sum, avg...etc

source:http://davidhayden.com/blog/dave/archive/2006/01/01/2661.aspx

Demo Gallery for asp.net controls

2008-06-07T21:09:00.000-07:00

source: http://mattberseth2.com/demo/

bulk insert with listview control

2008-06-07T20:23:00.000-07:00

step 1: define datasource and layout template for listview.
step 2: define itemtemplate with textbox using two-way binding of listview.
step 3: define css for it.
step 4: update datasource on click event of submit.

source: http://mattberseth.com/blog/2008/05/bulk_inserting_data_with_the_l.html

export gridview data to excel

2008-06-07T19:40:00.000-07:00

step 1: clear httpcontext's reponse.
step 2: define content header of httpcontext response object.
step 3: set the response type to application/ms-excel
step 4: create a html table and set its gridlines to gridview gridlines
step 5: convert table row and gridview controls are replaced by literals in table.
step 6: render the table into the htmlwriter
step 7: render the htmlwriter into the response

source: http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html