For This You Will have to make A Package Beacause in Oracle Store Procedures are composed in Packages.
and Acces By Also With It’s Name
I give you basic simple example of It and How can U Can Use to it with Asp.net
In Vb.
Step1 : For this first of all install oracle and Toad Software for this purpose of If Oracle have 10g or 11i then too good
Then you have no need of Toad
This time i am telling it with use of toad
Run to Toad and Connect it with your Schema(Database)
then click on red rounded option
Then Select your Package as given below in Picture
Create a New Package With This option
One is Body
Second is header or Specification
Body of Your Package
Specificatio of Package
CREATE OR REPLACE PACKAGE SYSTEM.PKG_MCFMS ASPROCEDURE Proc_Authentication(UserId IN VARCHAR2,UserPassword IN VARCHAR2,RET OUT NUMBER,USERROLE OUT VARCHAR2,UserName OUT VARCHAR2);END PKG_MCFMS;
/
Now Start to your visual studio and Make new WebSite and Make Connection With Oracle define this code in web config file into connectionString Section
<connectionStrings>
<!–section of connection with oracle database–>
<add name=”cn” connectionString=”user id=system;password=manageowm;data source=M2″/>
<!–section of connection end with oracle database–>
</connectionStrings>
For connectivity In oracle you will be make a service it’s not connect as MSSqlServer
M2 is a Service Name
Now Make a Class As Given Below in Your web Site Into App_code Folder
Imports Microsoft.VisualBasic
Imports System.Data.OracleClient
Imports System.Data
Imports System.Configuration
Namespace Deam_BLL
”’ <summary>
”’ User Properties Interface
”’ </summary>
”’ <remarks></remarks>
Interface ifaceUser
Property strUserName() As String
Property strUserId() As String
Property strUserPassword() As String
Property strLoginId() As String
Property strRole() As String
End Interface
”’ <summary>
”’ User Property Class
”’ </summary>
”’ <remarks></remarks>
Public Class clsUserprp : Implements ifaceUser
Dim _strLoginId, _strUserName, _strUserId, _strUserPassword, _strRole As String
Public Property strLoginId() As String Implements ifaceUser.strLoginId
Get
Return _strLoginId
End Get
Set(ByVal value As String)
_strLoginId = value
End Set
End Property
Public Property strUserId() As String Implements ifaceUser.strUserId
Get
Return _strUserId
End Get
Set(ByVal value As String)
_strUserId = value
End Set
End Property
Public Property strUserName() As String Implements ifaceUser.strUserName
Get
Return _strUserName
End Get
Set(ByVal value As String)
_strUserName = value
End Set
End Property
Public Property strUserPassword() As String Implements ifaceUser.strUserPassword
Get
Return _strUserPassword
End Get
Set(ByVal value As String)
_strUserPassword = value
End Set
End Property
Public Property strRole() As String Implements ifaceUser.strRole
Get
Return _strRole
End Get
Set(ByVal value As String)
_strRole = value
End Set
End Property
End Class
”’ <summary>
”’ Login Class
”’ </summary>
”’ <remarks></remarks>
Public Class clsLogin : Inherits clsOracleConn
Public Function Login(ByVal _UserId As String, ByVal _UserPassword As String) As Integer
Dim cmd As New OracleCommand
Try
If (conn.State = ConnectionState.Closed) Then
conn.Open()
End If
cmd.CommandText = “PKG_MCFMS.Proc_Authentication”
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = conn
cmd.Parameters.AddWithValue(“UserId”, _UserId)
cmd.Parameters.AddWithValue(“UserPassword”, _UserPassword)
cmd.Parameters.Add(“RET”, OracleType.Number).Direction = ParameterDirection.Output
cmd.Parameters.Add(“USERROLE”, OracleType.VarChar, 5).Direction = ParameterDirection.Output
cmd.Parameters.Add(“UserName”, OracleType.VarChar, 30).Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()
Dim K As Int32
Dim OutStatus As String
Dim OutUserName As String
OutStatus = cmd.Parameters(“USERROLE”).Value.ToString()
OutUserName = cmd.Parameters(“UserName”).Value.ToString()
K = cmd.Parameters(“RET”).Value
If (K = 2) Then
HttpContext.Current.Session(“LoginId”) = _UserId.ToUpper().Trim()
HttpContext.Current.Session(“UserId”) = _UserId.ToUpper().Trim()
HttpContext.Current.Session(“Password”) = _UserPassword.ToUpper().Trim()
HttpContext.Current.Session(“UserName”) = OutUserName.ToUpper().Trim()
HttpContext.Current.Session(“UserRole”) = OutStatus.ToUpper().Trim()
End If
Return K
Catch ex As Exception
Return ex.Message
End Try
End Function
End Class
For Connectivity Make also a Inheritable Class
”’ <summary>
”’ Main Connection Class
”’ </summary>
”’ <remarks></remarks>
Public MustInherit Class clsOracleConn
Public conn As OracleConnection
Public Sub New()
conn = New OracleConnection(ConfigurationManager.ConnectionStrings(“cn”).ConnectionString)
End Sub
End Class
Public MustInherit Class clsSapConn
Public Shared connSap As OracleConnection
Public Sub New()
connSap = New OracleConnection(ConfigurationManager.ConnectionStrings(“cn”).ConnectionString)
End Sub
End Class
End Namespace
Now Access this Class on your Login Page on Button Click As Given Below
Imports Deam_BLL
Partial Class Masters_Default2
Inherits System.Web.UI.Page
Dim objUserLogin As New clsLogin
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim OutPutFunction As Integer
Dim OutUserLogin As Integer
txtUserName.TextMode = TextBoxMode.SingleLine
txtPassword.TextMode = TextBoxMode.Password
Try
OutPutFunction = objUserLogin.Login(txtUserName.Text.Trim().ToUpper(), txtPassword.Text.Trim().ToUpper())
Dim ticket As New FormsAuthenticationTicket(1, Session(“LoginId”), Date.Now, Date.Now.AddHours(12), True, Session(“UserRole”), FormsAuthentication.FormsCookiePath)
Dim hash As String = FormsAuthentication.Encrypt(ticket)
Dim tempCookie As New HttpCookie(FormsAuthentication.FormsCookieName, hash)
Response.Cookies.Add(tempCookie)
If OutPutFunction = 2 Then
‘Call objUserLogin.User_LoggedIn_History(Session(“LoginId”), OutUserLogin)
If Session(“UserRole”) = “ADMIN” Or Session(“UserRole”) = “SEMI” Then
Response.Redirect(“Authenticate_Selection.aspx”)
Else
Response.Redirect(“User/Home.aspx”)
End If
End If
Catch ex As Exception
Dim message As String
message = ex.Message.Trim()
DivException.Attributes.Add(“display”, “block”)
DivException.InnerText = message
End Try
End Sub
End Class
Now Go into source of Your Page and Write down given tags
<div style=”width: 100%; color: White; text-align: left; vertical-align: top” runat=”server”>
<div id=”FirstDiv” style=”width: 100%; color: White;” runat=”server”>
<fieldset style=”background-color: #0065a2;”>
<legend style=”color: White; padding-top: 15px; font-size: medium; font-weight: bold;”>
Member Login</legend>
<table align=”left” cellpadding=”0″ cellspacing=”0″ class=”Table”>
<tr>
<td width=”100%” style=”font-size: small; font-weight: bold; padding-left: 15px;”
colspan=”2″>
<asp:Literal ID=”ltrlMsg” runat=”server”></asp:Literal>
</td>
</tr>
<tr>
<td width=”50%” style=”font-size: small; font-weight: bold; padding-left: 15px;”>
Portal Id
</td>
<td style=”padding-left: 5px;”>
<asp:TextBox ID=”txtUserName” runat=”server” class=”validate[required] text-input”></asp:TextBox>
</td>
</tr>
<tr>
<td width=”50%” style=”font-size: small; font-weight: bold; padding-left: 15px;”>
Password
</td>
<td style=”padding-left: 5px;”>
<asp:TextBox ID=”txtPassword” runat=”server” class=”validate[required] text-input”></asp:TextBox>
</td>
</tr>
<tr>
<td style=”vertical-align: bottom; text-align: center; padding-top: 15px; font-size: small;”>
<asp:LinkButton ID=”lnkForgetPass” SkinID=”lnkForgetPass” runat=”server” ToolTip=”click here to retrieve your password”>Forget Password</asp:LinkButton>
</td>
<td style=”text-align: center; font-size: small; padding-top: 15px;”>
<%–<img id=”btnLogin” title=”Sign In” alt=”Click Me” src=”Images/signin.jpg” runat=”server” onclick=”return btnLogin_onclick()” />–%>
<asp:Button ID=”Button1″ runat=”server” Text=”Login” ToolTip=”click here for login”
SkinID=”btnLogin” />
</td>
</tr>
</table>
</fieldset>
</div>
</div>
<div id=”DivException” style=”background-color: Yellow; color: Red; border-color:Gray; border-style:hidden;”
runat=”server”>
<asp:Label Text=”” ID=”lblException” runat=”server” ></asp:Label>
</div>
then Double click on your page and Go into page load event
Protected Sub Page_PreLoad(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreLoad
If (Page.IsPostBack = False) Then
FirstDiv.Style.Add(“display”, “block”)
DivException.Attributes.Add(“display”, “none”)
End If
End Sub
Run to your site page and test
Be Happy and Enjoy in Programming World!!!