This message was discovered on ASPFriends.com 'ngfx-oracle' list.
| mihir pathak |
-- Moved from [winforms-vb] to [ngfx-oracle] by Charles M. Carroll <Click here to reveal e-mail address> --
dear Friends
can u send me the code for connection vb.net with oracle database.
thanx
Mihir Pathak
_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com
|
|
| |
| |
| Scott Sargent |
Hello, Someone sent this to me a few months back, Also Microsoft just =
released a beta of a Managed Provider for Oracle. It uses OCI under the =
covers so it should be pretty speedy. You might take a look at their =
site for that. Also on otn.oracle.com there are some c# code examples =
that might be helpful as well.
I think this is the url:=20
http://www.bbkdotnet.com/PortalVSVB/DesktopDefault.aspx?tabindex=3D1&tabi=
d=3D3
and this is the article:
This article was written using Oracle 8.1.7 and VS.NET Beta2 Visual =
Basic.
One of the great things about using Microsoft SQL Server, is how simple
it is to return a cursor from a stored procedure. You simply say, =
"select
xyz from abc" in the proc and bingo - data! Only problem is, you can't
always
decide which database you will have to use. Many of my more misguided
customers use Oracle. So how can you get Oracle to return a cursor? This
example demonstrates creating a resultset in an Oracle stored
procedure, and displaying it in a .NET grid.
In Oracle create a package declaration like:
Package DOTNET_DEMO_PKG AS
type dnCursor is REF cursor;
procedure DateRangeReport
(
brand_in in varchar2,
start_date_in in varchar2,
end_date_in in varchar2,
p_cursor OUT dnCursor
);
end;
The package has a type definition of REF cursor that is uses as the type
of the output parameter in the procedure header. I'm using varchar2
parameters for the dates to keep it simple. Note that doing this will =
make
your query run slower because of the 'to_date' function calls in the =
proc.
The package body implements the proc from the package declaration. It =
opens
the cursor and selects the data. I have used column aliases to make =
labeling
the grid columns easier.
Package Body DOTNET_DEMO_PKG
AS
Procedure DateRangeReport (
brand_in in varchar2,
start_date_in in varchar2,
end_date_in in varchar2,
p_cursor OUT dnCursor
) IS
Begin
open p_cursor for
select
c.site "Site",
sum(s.CLICK_COUNT) "Click Thrus",
sum(s.CART_COUNT) "Carts",
sum(s.TRANS_COUNT) "Transactions",
sum(s.SALES) "Sales",
min(s.LOG_DATE) "Start Date",
max(s.LOG_DATE) "End Date"
from
ad_track_summary_new s,
orweb.or_ad_codes c
where
s.brand =3D brand_in
and s.AD_ID =3D c.AD_ID
and s.LOG_DATE >=3D to_date(start_date_in,'DD-Mon-YYYY')
and s.LOG_DATE <=3D to_date(end_date_in,'DD-Mon-YYYY')
group by
c.site;
end;
END;
Now for the VB.NET part...
In web.config create a connection string...
<appSettings>
<add key=3D"connstr" =
value=3D"Provider=3DMSDAORA.1;Password=3Dyourpass;User
ID=3Dyouruid;Data Source=3Dyourds" />
</appSettings>
obviously you have to fill in your own username, password and data =
source.
The aspx file contains a form with a grid for the results to be =
displayed,
and 3 text boxes from
which to retrieve the parameters for the query.
<%@ Page Language=3D"vb" Codebehind=3D"WebRpts.aspx.vb"
Inherits=3D"elwebdotnet.WebRpts" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title></title>
<meta name=3D"GENERATOR" content=3D"Microsoft Visual Studio.NET 7.0">
<meta name=3D"CODE_LANGUAGE" content=3D"Visual Basic 7.0">
<meta name=3Dvs_defaultClientScript content=3D"JavaScript">
<meta name=3Dvs_targetSchema
content=3D"http://schemas.microsoft.com/intellisense/ie5";>
</HEAD>
<body >
<form id=3D"Form1" method=3D"post" runat=3D"server">
<asp:DataGrid id=3DDataGrid1
runat=3D"server"
Width=3D"338px"
Height=3D"179px"
BorderStyle=3D"None"
BorderWidth=3D"1px"
BorderColor=3D"#CCCCCC"
BackColor=3D"White"
CellPadding=3D"3"
Font-Names=3D"Arial"
>
<FooterStyle ForeColor=3D"#000066" BackColor=3D"White">
</FooterStyle>
<HeaderStyle Font-Size=3D"Smaller" Font-Bold=3D"True" =
Wrap=3D"False"
ForeColor=3D"White" BackColor=3D"#006699">
</HeaderStyle>
<PagerStyle HorizontalAlign=3D"Left" ForeColor=3D"#000066"
BackColor=3D"White" Mode=3D"NumericPages">
</PagerStyle>
<SelectedItemStyle Font-Bold=3D"True" ForeColor=3D"White"
BackColor=3D"#669999">
</SelectedItemStyle>
<ItemStyle Font-Size=3D"Smaller" Wrap=3D"False" =
ForeColor=3D"#000066">
</ItemStyle>
</asp:DataGrid>
<br><br>
<asp:TextBox id=3DtxtBrand runat=3D"server"></asp:TextBox>
<asp:TextBox id=3DtxtFromDate runat=3D"server"></asp:TextBox>
<asp:TextBox id=3DtxtToDate runat=3D"server"></asp:TextBox>
<br><br>
<asp:Button id=3DButton1 runat=3D"server"
Width=3D"112px" Height=3D"29px"
Text=3D"Button"></asp:Button>
</form>
</body>
</HTML>
The code behind follows. Note that the oledbcommand that is executed is
'dotnet_demo_pkg.daterangereport.'
That is, the packagename.procname. Other than that, it's pretty straight
foreward. Hope this helps!
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration
Public Class WebRpts
Inherits System.Web.UI.Page
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents txtBrand As System.Web.UI.WebControls.TextBox
Protected WithEvents txtFromDate As System.Web.UI.WebControls.TextBox
Protected WithEvents txtToDate As System.Web.UI.WebControls.TextBox
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
dim dr as OleDbDataReader =3D GetRpt()
datagrid1.DataSource =3D dr
datagrid1.DataBind
End Sub
Public Function GetRpt() As OleDbDataReader
Dim myConnection As New
OleDbConnection(ConfigurationSettings.AppSettings("connstr"))
Dim myCommand As New =
OleDbCommand("dotnet_demo_pkg.daterangereport",
myConnection)
myCommand.CommandType =3D CommandType.StoredProcedure
Dim parmBrand As New OleDbParameter("@brand_in", =
OleDbType.VarChar,
15)
parmBrand.Value =3D txtBrand.text
myCommand.Parameters.Add(parmBrand)
Dim parmStDate As New OleDbParameter("@start_date_in",
OleDbType.VarChar, 15)
parmStDate.Value =3D txtFromDate.text
myCommand.Parameters.Add(parmStDate)
Dim parmEndDate As New OleDbParameter("@end_date_in",
OleDbType.VarChar, 15)
parmEndDate.Value =3D txtToDate.Text
myCommand.Parameters.Add(parmEndDate)
myConnection.Open()
Dim result As OleDbDataReader =3D
myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Return result
End Function
End Class
Bob Levittan
BBK Consulting, Inc.
----- Original Message -----
From: "mihir pathak" <Click here to reveal e-mail address>
To: Click here to reveal e-mail address
Sent: 4/29/2002 10:39:00 PM
Subject: connectivity using oracle
>-- Moved from [winforms-vb] to [ngfx-oracle] by Charles M. Carroll =
<Click here to reveal e-mail address> --
[Original message clipped]
|
|
| |
|
| |
| saurabh raghave |
(Type your message here)
code and procedure of connectivity of vb.net with oracle
--------------------------------
From: saurabh raghave
|
|
| |
|
|
|
|
|
