Now you should see the assembly created at the following path:. So we have built the Data Access block assembly, now we create a Web Application in which we refer this assembly. In our web application, we use a simple web form to connect to SQL Server and access the data from the database.
NET Web Application and enter the project name and path accordingly. Step 2: Now we add the Data Access Block to our project. To do this, right click on the references node and click Add Reference. Click on the Browse and select the Microsoft. Step 3: Open the WebForm1 and drag a datagrid from the toolbox onto it. We have used the ExecuteDataset static method of the SqlHelper class to fetch the top 10 records from Employees table.
Please change those accordingly. One of the components, Data Access Application Block for. When Microsoft introduced. NET provides a lot of new features for accessing data such as datasets, which are essentially an in-memory database, and data adapters that allow different data providers to interact with a DataSet. This model allows datasets to be completely data provider-independent giving you the option of changing the data provider used to fill a DataSet and update data from a DataSet.
You can even use multiple data providers with a single DataSet? Of course you will find some classes such as a Connection class, Parameter class, and Command class that perform the same basic function as their ADO predecessor.
With ADO. NET's new features and enhancements, of course, comes extra complexity when you write your data access code; you never had to deal with a data adapter in the past. You're in luck. Together these guidelines provide far better guidance for developing strong solutions compared to simple white papers or books alone.
The Reference Building Blocks and IT Services guidelines provide designs and components for building reusable sub-systems in your software solutions. The components of this guideline are referred to as Application Blocks. Table 1 lists the eleven Application Block components that were available at the time this article was written.
The SqlHelper class provides for simplified data access to a SQL server database and the SqlHelperParameterCache class caches SqlParameters for a given command as well as connection so that you can reuse them at any given time.
Together these classes encapsulate the code you will need to perform most of your routine data access logic. The SqlHelper class simplifies the task of writing data access by wrapping up the data access logic for you.
You just have to call a single method with a few parameters, and voila! This class is very well thought out as it contains methods for returning DataSets , DataReaders , XmlReaders , scalar values, and the number of rows affected for non-query commands. It also has specialized methods for performing database updates from a DataSet , and a method for populating a pre-defined DataSet such as a typed DataSet.
The methods fall into two major categories: those that accept parameters from a DataRow object, and those that don't. The two exceptions to the categories are the CreateCommand method and UpdateDataset method. You can easily tell which methods use the DataRow object for passing in parameters by the "TypedParams" at the end of the method name. Table 2 has a list of SqlHelper class methods.
All methods except for the UpdateDataset method require some type of connection or transaction to be specified by passing in a SqlConnection object, SqlTransaction object, or connection string. Note that the CreateCommand method only supports a SqlConnection object.
Also, any method returning an XmlReader object cannot support a connection string parameter since the. All methods also require, except again for the UpdateDataset method, a command string. In addition, depending on the overload or method you use, you may also be required to specify a CommandType parameter of StoredProcedure or Text. You cannot use a TableDirect CommandType because it is only supported by the. All methods except for CreateCommand , UpdateDataset , and DataRow "TypedParams" support overloads for running a stored procedure or in-line text without specifying parameters.
However, if you want to specify parameters for a SqlCommand you have three options:. One important thing to be aware of when you use the DataRow parameter method or the Object array parameter overload is that they both utilize the SqlHelperParameterCache class to retrieve and cache the SqlParameter array for future use.
Your first use of these methods will take a slight performance hit while the SqlHelperParameterCache retrieves the SqlParameter from the stored procedure. Subsequent calls then simply pull the SqlParameter array from the cache. You should note a few other important things about using the Object array parameter overloads. Also, one important thing to note about using the DataRow parameter methods is that the Column name in the DataRow must match the corresponding SQL parameter name without the " ".
This means that if you had a DataRow column named "AccountId," then in order for you to pass it as a parameter to your SQL command you must have a input parameter defined with the name " AccountId" and its data type must be the same as the DataRow column. When you specify the SqlParameter arrays or object arrays as parameters you do not have to pass them to the SqlHelper method as an array. Instead you can specify a variable list of arguments that will convert to an array method parameter.
Look at the example below. The example shows a command string, a connection string, and two parameters of different types being passed into the ExecuteDataSet method of the SqlHelper class.
I could have specified any number of parameters, all of which could be of different data types, because this particular overload accepts the array of parameters as object types. This comes in very handy for writing quick and dirty data access code. The SqlHelperParameterCache class is used in conjunction with the SqlHelper class to support the caching of SqlParameters, thus allowing you to reuse parameters without having to recreate them every time you need to run an SQL command.
This parameter cache class uses a hash table to store parameter caches based on connection string and command text. You can set up a parameter cache in one of two ways: you can specify an array of SqlParameters yourself or you can specify a stored procedure and connection string, and it will build an array of SqlParameter objects using the SqlCommandBuilder class. ExecuteScalar — Fetching single cell value.
ExecuteDataset — Fetching DataSet of records. You will need to import the following namespaces. Imports System. Imports Microsoft. It accepts the following 4 parameters. Connection String — Connection string to the database. CommandParameters Optional — Array of Parameters to be passed. ConnectionStrings[ "constr" ]. Add new SqlParameter " Name" , name ;. Add new SqlParameter " Country" , country ;. ExecuteNonQuery constr, CommandType.
Text, query, parameters. ToArray ;. ConnectionStrings "constr". Add New SqlParameter " Name" , name. Add New SqlParameter " Country" , country. End Sub. ExecuteScalar Example with SqlHelper class. ToInt32 SqlHelper. ExecuteScalar constr, CommandType. RegisterClientScriptBlock this. RegisterClientScriptBlock Me.
ExecuteReader Example with SqlHelper class. It returns the SqlDataReader object, which can be used in loop for reading records one by one and also can be directly assigned to a DataSource control.
0コメント