Database Independent Development using ASP.NET 2.0: Dealing with Stored Procedures - Testing the execution of stored procedures (in ASP.NET) using DAL methods
(Page 6 of 6 )
To execute the methods in the DAL, you need to create an object as follows:
Dim db As DALib.DataAccess
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim ProvName As String = ConfigurationManager.AppSettings("Provider-
Type").ToString
Dim ConnString As String = ConfigurationManager.ConnectionStrings
("ConnectionString").ConnectionString.ToString
db = New DALib.DataAccess(ProvName, ConnString)
End Sub
Prior to the above, make sure that your web.config file is equipped with the proper provider type and connection string as follows:
<appSettings>
<add key="Provider-Type" value="System.Data.SqlClient"/>
<!-- <add key="Provider-Type" value="Oracle.DataAccess.Client"/> -->
</appSettings>
<connectionStrings>
<add name="ConnectionString" connectionString="Data
Source=.sqlexpress;initial catalog=northwind;user
id=sa2;password=eXpress2005"/>
<!--<add name="ConnectionString" connectionString="Data Source=xe;user
id=northwind;password=tiger"/> -->
</connectionStrings>
Please be aware that you may have to modify the above configurations according to your requirements.
To execute a stored procedure using the DAL object, the following single line would be enough:
db.SPExecute("StoredProcedureName")
To execute a stored procedure with two parameters, the following three lines would be enough:
db.SPParameterAdd("@parametername1", "value1")
db.SPParameterAdd("@parametername2", "value2")
db.SPExecute("StoredProcedureName")
You can even provide much more parameter information as follows:
db.SPParameterAdd("@parametername1", "value1",
Data.ParameterDirection.Input, Data.DbType.Int16)
To receive a data table and present the same using GridView, you can make it two lines as follows:
Me.GridView1.DataSource = db.SPgetDataTable("StoredProcedureName")
Me.GridView1.DataBind()
And finally, you can retrieve a single value using the following:
Me.Label1.Text = db.SPgetGetValue("StoredProcedure")
You can expect the third (and final) part of this series soon. I hope you enjoyed the article and any comments, suggestions, feedback, bugs, errors, enhancements etc. are highly appreciated at http://jagchat.spaces.live.com
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
