.NETGURU
  Search:  
NamespacesDiscussions.NET v1.1Dot Net Guru's BlogIT Certification
connectivity using oracle
Messages   Related Types
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
Reply to this message...
 
    
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]
Reply to this message...
 
    
saurabh raghave
(Type your message here)
code and procedure of connectivity of vb.net with oracle
--------------------------------
From: saurabh raghave
Reply to this message...
 
 
System.Configuration.ConfigurationSettings
System.Data.CommandBehavior
System.Data.CommandType
System.Data.OleDb.OleDbCommand
System.Data.OleDb.OleDbConnection
System.Data.OleDb.OleDbDataReader
System.Data.OleDb.OleDbParameter
System.Data.OleDb.OleDbType
System.EventArgs
System.Object
System.Web.UI.MobileControls.PagerStyle
System.Web.UI.MobileControls.TextBox
System.Web.UI.Page
System.Web.UI.WebControls.BorderStyle
System.Web.UI.WebControls.Button
System.Web.UI.WebControls.DataGrid
System.Web.UI.WebControls.HorizontalAlign
System.Web.UI.WebControls.TextBox
System.Windows.Forms.BorderStyle
System.Windows.Forms.DataGrid
System.Windows.Forms.TextBox




ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification
Ad


Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews
.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT
Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project
FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.
Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials
 
ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification
 Copyright © ExamGuru, Inc. 2001-2006
Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification