Execute Stored Procedures

Using stored procedures in your applications produces faster and more secure data access as compared to running SQL Statements directly against your database. Stored procedures are precompiled SQL statements that can be cached in memory on your database server. Stored procedures are a good way to control what types of queries you allow your users to execute. Stored procedures can be allowed or disallowed based on what type of user is accessing the system. Also, by using stored procedures, you will have all your SQL in one place and not distributed throughout your code. This makes maintenance much easier.

Stored procedures can have zero to many parameters. Parameters enable you to pass data to the stored procedure. This data can be used by the stored procedure in a select statement to filter data with the where clause. This parameter data can also be used in insert, update, or delete statements to modify data in your database. Like the ad hoc queries, you are probably reading a majority of the data for the parameters for the stored procedures from controls on your Web page.

With stored procedures, you can also raise explicit errors if a problem occurs while executing the SQL that is in a stored procedure. It is good practice to inspect for these errors after the stored procedure returns control back to your server-side code.

EXECUTE STORED PROCEDURES

EXECUTE STORED PROCEDURES

D Open the

GenericTemplate.aspx from the Code Templates directory.

0 Add a message to the user.

—Q Add a DataGrid control to the page and set its properties.

^0 Import the System.Data and System.Data. SqlClient namespaces.

^0 Create a SQLConnection object and use a connection string to connect to the database.

Create a SQLDataAdapter and set stored procedure using the SQLConnection object.

—0 Set the command type for the SQLAdapter.

D Open the

GenericTemplate.aspx from the Code Templates directory.

0 Add a message to the user.

—Q Add a DataGrid control to the page and set its properties.

^0 Import the System.Data and System.Data. SqlClient namespaces.

^0 Create a SQLConnection object and use a connection string to connect to the database.

Create a SQLDataAdapter and set stored procedure using the SQLConnection object.

—0 Set the command type for the SQLAdapter.

L-E Add a parameter to the Command object.

ACCESS DATA WITH ASP.NET

You can also read the parameters for your stored procedures from a form control on your ASP.NET Web page. After users complete a form, they can click a Submit button, and the data can be read off the form at that time. The first section of code goes into the body of an HTML page and the second section of code goes into the click event of the button. Please see Chapter06/Code/ASPStoredProcedure_ ai.aspx for the full source.

TYPE THIS:

<FORM RUNAT="Server">

<ASP:DATAGRID ID="datagridTitles" RUNAT="Server" SHOWHEADER="False" VISIBLE="False"/>

Enter the royalty percentage

<ASP:TEXTBOX ID="inputPercentage" TEXTMODE="SingleLine" TEXT="" WIDTH="200px" RUNAT="Server"/><BR/>

<ASP:BUTTON OnClick="SubmitBtn_Clickn TEXT="Submit" RUNAT="Server"/>

sqldataadapterTitles.SelectConimand.Parameters["@Percentage"].Value = inputPercentage.Text;

RESULT:

This produces a page that asks for a royalty percentage. When the percentage is submitted, a DataGrid control that contains a list of author IDs that meet the stored procedures criteria results.

Untitled Notepad

File Edit Format Help

^JffJxj protected void Page_Load(Object sender, EventArgs e) {

SqIConnection sqlconnectionPubs - new SqlConnection("server=(local)\\NetSDK;uid=QSUser;pwd=QSPassword;database=pub s");

SqIDataAdapter sqldataadapterTitles - new SqlDataAdapter("byroyalty", sqlconnectionPubs);

sqldataadapterTitles.SelectCommand.CommandType -CommandType.StoredProcedure;

sqldataadapterTitles.SelectCommand.Parameters. Add(new SqlParameter("@Percentage", SqIDbType.Int, 4));

"3

sqldataadapterl itles.SelectCommand.Parameters[,,i3Percentage,,J.Value =

DataSet datasetTitles = new DataSet(); ï,Ljldalyady|jlel~nileb.FHIidyLaï,el~nileb, "ULIeaUlhül";

datagridTitles.DataSource=datasetTitles.Tables["titleauthor"].DefaultView, datagricmtles.DataBindO:

Set the val ue for the command.

Create a DataSet object.

E Populate the DataSet object.

L-E Set the DataSource and DataBind properties of the DataGrid on the page.

£ Save the file and request it from the Web server.

■ The authors with a royalty percentage of 50% appear.

Was this article helpful?

0 0

Post a comment