ADO.NET Parameters Queries

In this post we are exploring more about Parameters Queries. For Example if the user want to view information for one specific employee.To get this information from our Employees table, we need to run the following query, replacing EmployeeID with the ID of the employee in which the user was interested:

For example:

 SELECT EmployeeID, Name, Username, Password 
FROM Employees WHERE EmployeeID = EmployeeID

For This, we have added a Textbox control into which users can enter the ID of the employee whose information they want to see. Now, we could perform this query using the following code:

If the user entered the number into the text box and clicked the button, the following query would be run:

SELECT EmplyeeID, Name, Username, Password 
FROM Employees WHERE EmployeeID = 5

The database would run this query without complaint, and our program would execute as expected. However, if as is perhaps more likely—the user entered an employee’s name, our application would attempt to run the following query:
For example:

 SELECT EmployeeID, Name, Username, Password 
 FROM Employees
 WHERE EmployeeID = abc

This query would cause an error in the database, which would, in turn, cause an exception in our web form. As a safeguard against this eventuality, ADO.NET allows we to define parameters in our query, and give each of those parameters a type. Inserting parameters into our query is a reasonably simple task:
In the following example, a new OracleCommand object named myOracleCommand is set to the OracleCommand object returned by calling the CreateCommand() method of myOracleConnection:

 String query = "select empno,deptno,name,address from emp  WHERE empno=:EmployeeID"; 
 objCmd.Parameters.Add(":EmployeeID", System.Data.SqlDbType.Int); 
 objCmd.Parameters[":EmployeeID"].Value = employeeID;

We have added a placeholder to the query for our parameter; it comprises the : symbol, followed by an identifier for the parameter (in this case, we have used EmployeeID). Next, we need to add this parameter to the OracleCommand object, and give it a value:

 objCmd.Parameters.Add(":EmployeeID", System.Data.SqlDbType.Int);
 objCmd.Parameters[":EmployeeID"].Value = employeeID;

Here, we call the Add method of comm.Parameters, passing in the name of the parameter (EmployeeID) and the parameter’s type; we have told ADO.NET that we are expecting an int to be passed to the database, but we could specify any of the SQL Server data types here.

Code: FrmParameterQuary.aspx.cs

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="FrmParameterQuary.aspx.cs"
            Inherits="LearnAsp.Net.ControlDemo.ADO.FrmParameterQuary" %>
<!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 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>
        Queries Parameters</h1>
    <div class="column">
        <fieldset>
            <legend>Search Employee</legend>
            <asp:Label ID="lblID" Width="100" AssociatedControlID="txtID" runat="server"> 
        Emp ID:</asp:Label><asp:TextBox ID="txtID" runat="server" ValidationGroup="Upload" /><br />
            <br />
            <asp:Button runat="server" ID="btnSearch" Text="Search" ValidationGroup="Upload"
                OnClick="btnSearch_Click" />
            <br />
            <br />
            <asp:Label ID="lblResult" ForeColor="#ef4410" BackColor="#8dd0ab" runat="server" />
        </fieldset>
    </div>
    </form>
</body>
</html>

Code: FrmParameterQuary.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 FrmParameterQuary : System.Web.UI.Page
    {
            protected void Page_Load(object sender, EventArgs e)
        {
        }
            protected void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
            string strConn = ConfigurationManager.ConnectionStrings["LearnAsp"].ToString();
            OracleConnection objConn = new OracleConnection(strConn);
                objConn.Open();
                Int32 employeeID = 0;
                Int32.TryParse(txtID.Text, out employeeID);
            OracleCommand objCmd = new OracleCommand();
                objCmd.Connection = objConn;
                objCmd.CommandType = CommandType.Text;
            String query = "select ename,job,sal from emp  WHERE empno=:EmployeeID";
                objCmd.Parameters.Add(":EmployeeID", System.Data.SqlDbType.Int);
                objCmd.Parameters[":EmployeeID"].Value = employeeID;
                objCmd.CommandText = query;
            DataTable dt = new DataTable();
            OracleDataAdapter oda = new OracleDataAdapter(objCmd);
                oda.Fill(dt);
                objConn.Close();
            if (dt.Rows.Count > 0)
                {
                    lblResult.Text = " Employee Name is " + dt.Rows[0]["ename"].ToString();
                }
            else
                {
                    lblResult.Text = " Employee Not Found" ;
                }
            }
            catch (Exception ex)
            {
                lblResult.Text = " Error : " + ex.Message.ToString();
            }
        }
    }
}

Output:

21 February 2020

ADO.NET Parameters Queries

No comments:

Post a Comment