The main challenge is to understand how the code that accesses the database works. The .NET
technology that permits accessing a database from C# code is called ADO.NET. ADO.NET
groups all .NET classes that are related to database access.
ADO.NET is a complex subject that requires a separate book by itself, so we’ll cover just
enough to help you understand how your business tier works. For more information about
ADO.NET, refer to Beginning C# 2008 Databases: From Novice to Professional (Apress, 2008).
The data access class named GenericDataAccess that you’l l write will make extensive use of
many ADO.NET features. The GenericDataAccess class deals with accessing the database,
executing stored procedures, and returning the retrieved data. This class will provide generic
data access functionality for the business tier classes, which need to read or manipulate that
data.
Each database operation always consists of three steps:
1. Open a connection to the SQL Server database.
2. Perform the needed operations with the database and get back the results.
3. Close the connection to the database.
Before you implement the GenericDataAccess class itself, which implements all these
steps, we’ll have a quick look at each step individually.
■Tip Always try to make the second step (executing the commands) as fast as possible. Keeping a data
connection open for too long or having too many database connections open at the same time is expensive
for your application’s performance. The golden rule is to open the connection as late as possible, perform the
necessary operations, and then close it immediately.
The class used to connect to SQL Server is SqlConnection. When creating a new datab
connection, you always need to specify at least three important pieces of data:
• The name of the SQL Server instance you’re connecting to
• The authentication information that will permit you to access the server
• The database you want to work with
This connection data is grouped in a connection string, which needs to be passed to t
SqlConnection object. The following code snippet demonstrates how to create and open a
database connection. (You don’t need to type anything just yet—here we’re just explaining
theory, and we’ll put it into practice in a step-by-step exercise just a little bit later.)
// Create the connection object
SqlConnection connection = new SqlConnection();
// Set the connection string
connection.ConnectionString = "Server=(local)\SqlExpress; " +
"User ID=balloonshop; Password=ecommerce;" +
"Database=BalloonShop";
// Open the connection
connection.Open();
The code is fairly straightforward: you first create a SqlConnection object, then set its
ConnectionString property, and finally open the connection. A connection needs to be opened
before it is used for any operations.
Understanding the connection string is important—if your program has problems connecting
to the database, these problems likely can be solved by fixing the connection string (assuming
that SQL Server is properly configured and that you actually have access to it).
The connection string contains the three important elements. The first is the name of the
SQL Server instance you’re connecting to. For SQL Server 2008 Express Edition, the default
instance name is (local)\SqlExpress. You’ll want to change this if your SQL Server instance
has another name. You can use your computer name instead of (local). Of course, if you
connect to a remote SQL Server instance, you’ll need to specify the complete network path
instead of (local).
After specifying the server, you need to supply security information needed to log in to the
server. You can log in to SQL Server either by using SQL Server Authentication (in which case
you need to supply a SQL Server username and password as shown in the code snippet) or
by using Windows Authentication (also named Windows Integrated Security). With Windows
Integrated Security, you don’t have to supply a username and password, because SQL Server
uses the Windows login information of the currently logged-in user.
To log in using Windows Authentication, you’ll need to supply Integrated Security=True
(or Integrated Security=SSPI) instead of User ID=username; Password=password. The final part
of the connection string specifies the database you’ll be working with.
Instead of setting the connection string after creating the SqlConnection object, you can
provide the connection string right when creating the SqlConnection object:
// Create the connection object and set the connection string
SqlConnection connection = new SqlConnection("... connection string ...");
// Open the connection
connection.Open();
A final note about the connection string is that several synonyms can be used inside it; for
example, instead of Server, you can use Data Source or Data Server, and instead of Database,
you can use Initial Catalog. The list is much longer, and the complete version can be found
in SQL Server Books Online.
Not original,copied from, :Beginning ASP.NET E-Commerce in C# From Novice to Professional