Using Sequence in SQL Server 2011 (an Alternative to Identity)

Using the Identity attribute for a column in SQL Server is a very convenient way to auto-increment a field in a row and generate unique ID’s for use as a primary key. SQL Server 2011 introduces Sequence, which is similar in many respects but is offers greater performance and flexibility – read our full tutorial on Using Sequence in SQL Server 2011.

Working with ADO.NET Transactions

A transaction is a group of operations combined into a logical unit of work that is either guaranteed to be executed as a whole or rolled back. Transactions help the database in satisfying all the ACID (Atomic, Consistent, Isolated, and Durable). Transaction processing is an indispensible part of ADO.NET. It guarantees that a block of statements will either be executed in its entirety or rolled back,( i.e., none of the statements will be executed). Transaction processing has improved a lot in ADO.NET 2.0. This article discusses how we can work with transactions in both ADO.NET 1.1 and 2.0.

Implementing Transactions in ADO.NET

Note that in ADO.NET, the transactions are started by calling the BeginTransaction method of the connection class. This method returns an object of type SqlTransaction.
Other ADO.NET connection classes like OleDbConnection, OracleConnection also have similar methods. Once you are done executing the necessary statements within the transaction unit/block, make a call to the Commit method of the given SqlTransaction object, or you can roll back the transaction using the Rollback method, depending on your requirements (if any error occurs when the transaction unit/block was executed).
To work with transactions in ADO.NET, you require an open connection instance and a transaction instance. Then you need to invoke the necessary methods as stated later in this article.  Transactions are supported in ADO.NET by the SqlTransaction class that belongs to the System.Data.SqlClient namespace.

The two main properties of this class are as follows:

  • Connection: This indicates the SqlConnection instance that the transaction instance is associated with
  • IsolationLevel: This specifies the IsolationLevel of the transaction

The following are the methods of this class that are noteworthy:
Commit()   This method is called to commit the transaction
Rollback()  This method can be invoked to roll back a transaction. Note that a transaction can only be rolled back after it has been committed.
Save()       This method creates a save point in the transaction. This save point can be used to rollback a portion of the transaction at a later point in time. The following are the steps to implement transaction processing in ADO.NET.

  • Connect to the database
  • Create a SqlCommand instance with the necessary parameters
  • Open the database connection using the connection instance
  • Call the BeginTransaction method of the Connection object to mark the beginning of the transaction
  • Execute the sql statements using the command instance
  • Call the Commit method of the Transaction object to complete the
    transaction, or the Rollback method to cancel or abort the transaction
  • Close the connection to the database

The following code snippet shows how we can implement transaction processing using ADO.NET in our applications.

string connectionString = ...; //Some connection string
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();

SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();

SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Transaction = sqlTransaction;

try
{
sqlCommand.CommandText = "Insert into Employee (EmpCode, EmpName) VALUES (1, 'Joydip')";
sqlCommand.ExecuteNonQuery();
sqlCommand.CommandText = "Insert into Dept (DeptCode, DeptName, EmpCode) VALUES (9, 'Software', 1)";
sqlCommand.ExecuteNonQuery();
sqlTransaction.Commit();
//Usual code
}

catch(Exception e)
{
sqlTransaction.Rollback();
//Usual code
}

finally
{
sqlConnection.Close();
}

The next piece of code illustrates how we can use the “using” statement for the above code. According to MSDN, the “using” statement, “defines a scope, outside of which an object or objects will be disposed.
Continues…

WebMatrix Tutorial – Working with Data

In this tutorial we will focus on working with the inbuilt database (SQL Server CE 4) in WebMatrix. If you are new to WebMatrix please check out Part 1 of the WebMatrix Tutorial which deals with installing WebMatrix and working with the new ASP.NET Pages code files.

Working with the Database in WebMatrix

SQL Server CE is deeply integrated into WebMatrix, select Databases on the menu at bottom left of the interface and if you do not currently have an associated database you will be confronted with the below screen:

WebMatrix SQL Server Database

Click Add a Database to your site , then right-click Tables and select New Table.

WebMatrix SQL Server Database

You will then be able to add columns to your table (just click New Column on the ribbon for add the additional columns). You can specify the column details in the Column Properties settings list:

WebMatrix Database

You can edit or add data by clicking Data in the ribbon, which will take you to the existing data in the database presented as an editable grid:

WebMatrix SQL Server Database

Of course you would not normally add data using this method but it is convenient for a quick edit or to add some dummy data.

Note that using WebMatrix does not mean that you are tied to using the inbuilt SQL Server CE, you can interact with any version of SQL Server or other databases supported by the .NET framework such as MySQL.

Accessing the Database using ASP.NET Pages in WebMatrix

The database can be easily accessed using the new database API – primarily using the Open, Query and Execute commands.

The Open command combined with the Query command can be used to access the data in the database:

WebMatrix Databases

You can use queries in parameters to generate dynamic pages. Note the syntax of the Query command of the database API, which can accept numbered parameters:

WebMatrix

Updating the Database using ASP.NET Pages in WebMatrix

The Execute command of the Database object can be used to insert, update or delete data from the database. The first paramater of the Execute command accepts the SQL string for the database operation and the subsequent parameters are for the parameters to be inserted into the SQL string.
Continues…

Implementing a Generic Data Access Layer in ADO.NET Part 3

In this the final article of this series, I will discuss the other classes in this framework and how we can use this framework to perform various CRUD operations in our applications.

Let us start from where we left off in the previous part of this series. Note that most of the methods of the DatabaseHelper class have been marked as “internal” to prevent them from being called outside of the “ApplicationFramework.DataAccessLayer” namespace.

Now we will come to the DBManager class; the wrapper class that encapsulates the calls to another class called DBHelper that actually performs the CRUD operations on the underlying database. The DBManager class extends the DBManagerBase abstract class. The DBManagerBase class contains the definition for the Open () and the Close () methods and some other public properties that are generic and can be used by any class that acts as a wrapper. We will have a look at the DBManagerBase class first.

The following code listing shows the DBManagerBase class.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.IO;

namespace ApplicationFramework.DataAccessLayer

{
public abstract class DBManagerBase

{

protected DatabaseHelper databaseHelper = null;
protected DbDataReader dbDataReader = null;
protected DataSet dataSet = null;
protected ProviderType providerType;
protected String connectionString = String.Empty;
protected bool isOpen = false;

public bool IsOpen

{

get
{
return isOpen;

}

set
{

isOpen = value;

}

}

 public string ConnectionString
{
get

{
return connectionString;
}

set

{
connectionString = value;
}

}

public DbConnection Connection
{
get
{

return databaseHelper.Connection;
}
}

public DbCommand Command

{
get
{

return databaseHelper.Command;
}

}

public ProviderType DBProvider
{
set
{
providerType = value;
}

get
{
return providerType;
}

}

public DataSet DBSet
{
get
{
return dataSet;
}

}

public DbDataReader DBReader
{
get
{
return dbDataReader;
}

}

protected void Open(string connectionString)

{
databaseHelper = new DatabaseHelper(connectionString, DBProvider);
}

protected void Close()
{
if (dbDataReader != null)
if (!dbDataReader.IsClosed)
dbDataReader.Close();
databaseHelper.Dispose();
}

public void BeginTransaction()
{
databaseHelper.BeginTransaction();
}

public void CommitTransaction()
{
databaseHelper.CommitTransaction();
}

 public void RollbackTransaction()
{
databaseHelper.RollbackTransaction();
}
}
}

Note that the DBManagerBase class contains the most common methods that are required. You can Open or Close a connection, Begin, Commit or Rollback transactions, etc. These methods would remain the same and are mandatory in this context even if you decide to have another version of the DBManager class with some more methods implemented it.
Continues…

Implementing a Generic Data Access Layer in ADO.NET Part 1

A Data Access Layer (DAL) is an integral part in the design of any application. There are plenty of articles that discuss how we an implement a DAL using ADO.NET. Most of these have constraints in the sense that they are not generic in nature. In other words, they are not provider independent. This series of articles will discuss the implementation of a generic, i.e., a provider independent Data Access Layer in ADO.NET. The basic prerequisite to learning this article is a proper understanding of ADO.NET and good coding skills in C#. I will present the code examples in this article in C#. However with little effort, you can twist it over to VB.NET as well.

The Strategies Involved in Creating a Data Access Layer

Let us first understand what the necessities are for building such a layer. I would rather start by discussing how an application designed using ADO.NET actually connects to the database and performs the CRUD (Create, Read, Update and Delete) operations.

First, you need to open the connection using a database provider. Fine, but what is a provider anyway? A provider is responsible for connecting to a specific database. Why specific? The reason is that a provider for an Oracle database cannot be used to connect to a SQL Server database and vice-versa. Next, you need a command object that can be used to execute the database commands of your choice. This is followed by the usage of a DataReader or a DataSet or a DataTable instance to retrieve data (if you are performing a Read operation) from the database table. When you use a DataSet, you need a DataAdapter as a bridge between the actual database and the DataSet instance.

Implementing the DAL Framework

With this in mind, let us design a provider independent Data Access Layer.

Let us first understand the ADO.NET Library. The major classes that constitute the ADO.NET library are:

     

  • Connection
  • Command
  • Data Reader
  • Data Adapter
  •  

The corresponding interfaces that the above classes implement are stated below.

     

  • IDBConnection
  • IDataReader
  • IDBCommand
  • IDBDataAdapter
  •  

The Data Providers that make up the library are specific to a particular database that they would connect to. These are the Data Providers that are available in ADO.NET.

     

  • SQL Server Data Provider
  • Oracle Data Provider
  • ODBC Data Provider
  • OleDB Data Provider
  •  

Now we are all set to implement our DAL. The major components that constitute our DAL block are:

     

  • ProviderType (Enum)
  • DatabaseConnectionState (Enum)
  • StoredProcedureParameterDirection (Enum)
  • DBManager (Class)
  • DBHelper (Class)
  •  

We will start our discussion with the enum data type that would contain the data provider types in it. These provider types relate to the databases that we will be connecting to, depending our requirements. The following code snippet illustrates the ProviderType enum that contains four values that correspond to a specific data provider.Continues…