ADO.NET Stored Procedure

In this post, we saw how to run a Oracle stored procedure using the Query Analyzer tool like SQL Navigator, Toad etc. Specifically, we saw how to run the Ten Most Expensive Products stored procedure. We can also run stored procedure using ADO.NET.

Running a stored procedure using ADO.NET is similar to running any other type of SQL statement. We formulate a string containing the name of the procedure, add any parameters along with their values for the procedure call, and then use the ExecuteNonQuery() method to run the stored procedure.

One difference is that we must set the CommandType property of the SqlCommand object to CommandType.StoredProcedure. This tells ADO.NETthat the SqlCommand is intended to run a stored procedure. For example:

 myCommand.CommandType = CommandType.StoredProcedure;

Code: frmAdoCallStoreProcedure.aspx.cs

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="frmAdoCallStoreProcedure.aspx.cs"
            Inherits="LearnAsp.Net.ControlDemo.ADO.frmAdoCallStoreProcedure" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
    <style>
            html
        {
            background-color: #35e0d8;
        }
        .column
        {
            float: left;
            width: 300px;
            margin-left: 10px;
            background-color: white;
            border: solid 1px black;
            padding: 10px;
            border-radius: 25px;
        }
        fieldset
        {
            background-color: #35e0d8;
            border: solid 2px #d81a72;
            border-radius: 20px;
            color: Black;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <h1>
        Call Store Procedure</h1>
    <div class="column">
        <fieldset>
            <legend>Department Entry</legend>
            <asp:Label ID="lblNo" Width="100" AssociatedControlID="txtDeptNo" runat="server"> 
        Dept No:</asp:Label><asp:TextBox ID="txtDeptNo" runat="server" Style="margin-left: 23px;" /><br />
            <br />
            <asp:Label ID="Label1" Width="100" AssociatedControlID="txtDeptName" runat="server"> 
       Dept. Name:</asp:Label><asp:TextBox ID="txtDeptName" runat="server" />
            <br />
            <br />
            <asp:Label ID="Label2" Width="100" AssociatedControlID="txtLocation" runat="server"> 
       Location:</asp:Label><asp:TextBox ID="txtLocation" runat="server" Style="margin-left: 21px;" />
            <br />
            <br />
            <asp:Button runat="server" ID="btnSubmit" Text="Save"
                OnClick="btnSubmit_Click" />
            <br />
            <br />
            <asp:Label ID="lblResult" ForeColor="#ef4410" BackColor="#8dd0ab" runat="server" />
        </fieldset>
    </div>
    </form>
</body>
</html>

Code: frmAdoCallStoreProcedure.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OracleClient;
using System.Data;
using System.IO;
using System.Configuration;
namespace LearnAsp.Net.ControlDemo.ADO
{
            public partial class frmAdoCallStoreProcedure : System.Web.UI.Page
    {
            protected void Page_Load(object sender, EventArgs e)
        {            
        }
            protected void btnSubmit_Click(object sender, EventArgs e)
        {
            try
            {
            OracleConnection objConn;
                // Retrieve Details from the DB
            string strConn = ConfigurationManager.ConnectionStrings["LearnAsp"].ToString();
                objConn = new OracleConnection(strConn);
                objConn.Open();
                //set command type as stored procedure
            OracleCommand objCmd = new OracleCommand();
                objCmd.Connection = objConn;
                objCmd.CommandType = CommandType.StoredProcedure;
                //pass the stored procedure name
                objCmd.CommandText = "learnasp_dept_ins";
                //pass the parameter to stored procedure
                objCmd.Parameters.Add(new OracleParameter("in_DeptName", OracleType.VarChar)).Value = txtDeptName.Text;
                objCmd.Parameters["in_DeptName"].Direction = ParameterDirection.Input;
                objCmd.Parameters.Add(new OracleParameter("in_DeptNo", OracleType.Number)).Value = Convert.ToDecimal( txtDeptNo.Text);
                objCmd.Parameters["in_DeptNo"].Direction = ParameterDirection.Input;
                objCmd.Parameters.Add(new OracleParameter("in_Loc", OracleType.VarChar)).Value = txtLocation.Text;
                objCmd.Parameters["in_Loc"].Direction = ParameterDirection.Input;
                // Add parameters
                objCmd.Parameters.Add(new OracleParameter("Out_ErrorCode", OracleType.Number, 5));
                objCmd.Parameters["Out_ErrorCode"].Direction = ParameterDirection.Output;
                objCmd.Parameters.Add(new OracleParameter("Out_ErrorMsg", OracleType.VarChar, 500));
                objCmd.Parameters["Out_ErrorMsg"].Direction = ParameterDirection.Output;
                //Execute the query
            int res = objCmd.ExecuteNonQuery();
            String Errcode = objCmd.Parameters["Out_ErrorCode"].Value.ToString();
            String Errmsg = objCmd.Parameters["Out_ErrorMsg"].Value.ToString();
                objConn.Close();
                lblResult.Text = Errmsg;
            }
            catch (Exception ex)
            {
                lblResult.Text = " Error : " + ex.Message.ToString();
            }
        }
    }
}

Output:

21 February 2020

ADO.NET Stored Procedure

No comments:

Post a Comment