This post explains an sample of implementing strongly typed data set using ASP.Net and SQL server as back-end database.
for an example, we create a DAL class using class library template and add the required strongly typed data set and the code behind to access.
Finally to access these DAL methods, we create a reference of this in our web application and then call the required methods.
Now to implement DAL, the steps are as follows:
1. Create a Class library project and name it as DAL.
2. Include Strongly typed Data set:
- right click on the project and give Add - > new item.
- Under Data templates, select DATASET template and specify the name as Sample, and finally give add.
- Now it includes a new data set (Sample.xsd) to the project.
3. Create connection to SQL server and include the table, queries, stored procedures.
- To include a store procedure or a query for a corresponding table in sql server, we need to create a data table and table adapter.
- All attribute details gets stored in the data table and the methods which define the query execution or the stored procedure execution are defined in the table adapter.
- To add a new table adapter, right click on the xsd, and give Add-> Table Adapter.
- Now specify the Connection string to get connected with the data base. The already used connection string gets populated in the Connection string drop down. If the required connection string does not exist in the drop down list, then click on the "New Connection" and make a new connection. Then click on next.
- Need to choose the command type. Here we have three options. One - to create a new query, second - to create a new stored procedure, and finally use the existing stored procedure defined in the corresponding data base server.Select the required one. For sample, i select the Use existing stored procedure.Then click on Next.
- Now we need to specify the stored procedures to the commands. Here we have 4 types of command types. Those are the Select, Insert, Update, Delete. And the stored procedures that exist in the given server gets binded to the drop down list of each command. Every table adapter should contain at least one select command. i.e No table adapter exist without an select command.
For this sample i just select one of the read stored procedure for the select command.
- Specify the methods to be generated. Also give the method name. By default we have Fill and Get methods. For our example, i select only the Get method, and give the method name as "ReadEmployees". And click on next.
- In the final wizard just click on Finish. This will generate the specified get method, also the default select and update methods. One can access these methods using the object of table adapter.
- This results in creating a new data table and table adapter along with the methods defined.
4.Include a class to access the methods defined in the table adapter.
- Right click the project and give Add - > New item. And select Class under Code template. Name this class as Datahelper.cs
- Define this class as public as it could be accessible from other class as well.
- Declare user defined static methods which executes the methods defined in the table adapter.
- To access the methods, we need to declare an object of the table adapter and this object contains all the methods defined. The returned value is fetched and passed.
- A sample code snippet resembles as follows
Similarly it could be done for insert, update and delete.
Thus this makes DAL ready. And it could be used from any class by having a reference to it.
No comments:
Post a Comment