How To Access an Oracle Database by Using the OLE DB .NET Data Provider and Visual C# .NET

SUMMARY

This article demonstrates how to use the ADO.NET OLE DB managed provider to access an Oracle database.

 

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

•	Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
•	Oracle Client tools (installed on the computer)
•	Microsoft Visual Studio .NET

This article assumes that you are familiar with the following topics:

•	Visual Studio .NET
•	ADO.NET fundamentals and syntax
•	Oracle connectivity

 

Steps to Access an Oracle Database

1.	In Oracle, create a table named TestTable as follows: Create Table TestTable (c1 char(5));
2.	Insert data into TestTable as follows: Insert into TestTable c1 values('Test1'); Insert into TestTable c1 values('Test2'); Insert into TestTable c1 values('Test3');
3.	Start Visual Studio .NET.
4.	Create a new Windows Application project in Visual C# .NET.
5.	Make sure that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not.
6.	Drag a Button control to Form1, and change its Name property to btnTest.
7.	Use the using statement on the System, System.Data, and System.Data.OleDb namespaces so that you are not required to qualify declarations in those namespaces later in your code. using System; using System.Data; using System.Data.OleDb;
8.	Switch to Form view, and double-click btnTest to add the click event handler. Add the following code to the handler: String sConnectionString = "Provider=MSDAORA.1;User ID=myUID;password=myPWD; Data Source=myOracleServer;Persist Security Info=False"; String mySelectQuery = "SELECT * FROM TestTable where c1 LIKE ?"; OleDbConnection myConnection = new OleDbConnection(sConnectionString); OleDbCommand myCommand = new OleDbCommand(mySelectQuery, myConnection); myCommand.Parameters.Add("@p1", OleDbType.Char, 5).Value = "Test%"; myConnection.Open(); OleDbDataReader myReader = myCommand.ExecuteReader(); int RecordCount=0; try { while (myReader.Read()) { RecordCount = RecordCount + 1; MessageBox.Show(myReader.GetString(0).ToString()); } if (RecordCount == 0) { MessageBox.Show("No data returned"); } else { MessageBox.Show("Number of records returned: " + RecordCount); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { myReader.Close(); myConnection.Close(); }
9.	Save your project.
10.	On the Debug menu, click Start to run your project.
11.	Click the button to display the data.

HOW TO Create a Local Web Server ASP.NET Application

Create a Local Web Server ASP.NET Application

1.	Start Microsoft Visual Studio .NET.
2.	On the File menu, point to New, and then click Project.
3.	In the New Project dialog box, click Visual Basic Projects under Project Types, and then click ASP.NET Web Application under Templates to create the project in Visual Basic. NOTE: Alternatively, you can click Visual C# Project under Project Types, and then click ASP.NET Web Application under Templates to create the project in Visual C#.
4.	In the IIS Web folder (which is typically /wwwroot), create the essential project references and files to use as a starting point for your application: • AssemblyInfo (.vb file for Visual Basic or .cs file for Visual C#): Use this file to describe the assembly and to specify version information. using System.Reflection; using System.Runtime.CompilerServices; [assembly: AssemblyTitle("")] [assembly: AssemblyDescription("")] [assembly: AssemblyConfiguration("")] [assembly: AssemblyCompany("")] [assembly: AssemblyProduct("")] [assembly: AssemblyCopyright("")] [assembly: AssemblyTrademark("")] [assembly: AssemblyCulture("")] [assembly: AssemblyVersion("1.0.*")] [assembly: AssemblyDelaySign(false)] [assembly: AssemblyKeyFile("")] [assembly: AssemblyKeyName("")] • Global.asax: The basic file Global.asax.cs that contains code for responding to application-level events that ASP.NET raises. <%@ Application Codebehind="Global.asax.cs" Inherits="WebApplication2.Global" %> NOTE: The source of Global.asax.cs is not included in this document. • Styles.css: This file contains the default HTML style settings. • Web.config: This is an application configuration file that contains settings that are specific to an application. This file contains configuration settings that the common language runtime reads (such as assembly binding policy, remoting objects, and so on), and settings that the application can read. • Projectname.vsdisco: This is an XML-based file that the ASP.NET dynamic XML Web service discovery process uses to identify searchable paths on the Web server. • WebForm1.aspx: This file contains the portion of the default Web Forms page that contains user interface elements (controls), similar to an HTML page. <%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false" Inherits="WebApplication2.WebForm1" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <html> <head> <title>WebForm1</title> <meta name="GENERATOR" Content="Microsoft Visual Studio 7.0"> <meta name="CODE_LANGUAGE" Content="C#"> <meta name=vs_defaultClientScript content="JavaScript"> <meta name=vs_targetSchema content="http://schemas.microsoft.com/intellisense/ie5"> </head> <body MS_POSITIONING="GridLayout"> <form id="Form1" method="post" runat="server"> </form> </body> </html> • Webform1 (.vb file for Visual Basic or .cs file for Visual C#): This file contains a class file for the default Web Forms page that contains the system-generated and user code for the page.
5.	After you create the project, you see an empty Web Form with that is named "WebForm1.aspx." This is the workspace of the first page in a project, in which you can place WebForms, HTML forms, Components, Data objects, and Clipboard elements from ToolBox.

How To Disable ASP Session State in ASP.NET

SUMMARY

This step-by-step article demonstrates how to disable session state in ASP.NET.

When session state is enabled, ASP.NET creates a session for every user who accesses the application, which is used to identify the user across pages within the application. When session state is disabled, user data is not tracked, and you cannot store information in the Session object or use the Session_OnStart or Session_OnEnd events. By disabling session state, you can increase performance if the application or the page does not require session state to enable it.

In ASP.NET, if you do not use the Session object to store any data or if any of the Session events (Session_OnStart or Session_OnEnd) is handled, session state is disabled. A new Session.SessionID is created every time a single page is refreshed in one browser session.

Developing Microsoft® .NET Controls with Microsoft Visual Basic® .NET

Customize the intrinsic controls in the .NET Framework—or build your own—for better usability

 

Every click on a menu item or a dialog box—every interaction with a control—shapes the user's experience and satisfaction with your software. Learn how to maximize the usability and impact of your Visual Basic .NET–based solutions by using the powerful intrinsic controls in the .NET Framework right out of the box—or by building complex controls from scratch. Expert developer and teacher John Connell shares a wealth of code samples and techniques to illustrate how .NET controls operate as well as how to integrate even the most complex controls into Windows Forms and Microsoft ASP.NET Web Forms. Whether you're developing in-house or commercial software, you'll learn how to build custom controls and other interface components that make your programs easy—and enjoyable—to use.

- Discover how to use encryption technologies, isolated storage, and serialization in the .NET Framework in your own programs and controls
- Build custom .NET type editors, type converters, and designers to add and manage sophisticated properties in your custom controls
- Learn how to inherit and enhance .NET controls—from handling events to building custom designers
- Aggregate multiple .NET intrinsic controls into one user control for simpler, custom functionality
- Use GDI+ to perform hit tests, create dynamic bitmaps, and add graphics to Windows Forms and ASP.NET controls
- Add licensing to your controls, including custom validation of authorized users
- Manipulate the built-in ASP.NET Web server controls, from basic HTML wrappers to the powerful DataGrid control
- Design ASP.NET custom server controls to create Web page hit counters, wireless applications, and more

HOW TO: Validate XML Fragments Against an XML Schema in Visual Basic .NET

SUMMARY

This step-by-step article describes how to use XmlValidatingReader and XMLSchemaCollection objects to validate an Extensible Markup Language (XML) fragment against an XML schema.

XmlValidatingReader implements the XmlReader class and provides support for XML data validation. The Schemas property of XmlValidatingReader connects the reader to the schema files cached in an XmlSchemaCollection. The ValidationType property of XmlValidatingReader specifies the type of validation the reader should perform. If you set the property to ValidationType.None, you create a nonvalidating reader.

You can only add XML Schema Definition Language (XSD) schemas and XML-Data Reduced (XDR) schemas to XmlSchemaCollection. Use the Add method with a namespace URI to load schemas. For XML schemas, the typical namespace URI is the targetNamespace property of the schema.

 

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you will need:

•	Microsoft Visual Studio .NET installed on a compatible Microsoft Windows operating system

This article assumes that you are familiar with the following topics:

•	Visual Basic .NET
•	Basic XML standards
•	XSD schemas

 

Create an XSD Schema

Paste the following code in a new text file named C:\Books.xsd:

 

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:bookstore-schema" elementFormDefault="qualified" targetNamespace="urn:bookstore-schema">
<xsd:element name="bookstore" type="bookstoreType" />
<xsd:element name="comment" type="xsd:string" />
<xsd:element name="author" type="authorName"/>
<xsd:complexType name="authorName">
<xsd:sequence>
<xsd:element name="first-name" type="xsd:string" />
<xsd:element name="last-name" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="bookstoreType">
<xsd:sequence maxOccurs="unbounded">
<xsd:element name="book" type="bookType" />
<xsd:element ref="comment" minOccurs="0" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="bookType">
<xsd:sequence>
<xsd:element name="title" type="xsd:string" />
<xsd:element ref="author" />
<xsd:element name="price" type="xsd:decimal" />
</xsd:sequence>
<xsd:attribute name="genre" type="xsd:string" />
</xsd:complexType>

</xsd:schema>

 

Create a Visual Basic .NET Application

1. Create a new Visual Basic .NET Windows application

 

2. Drag Button1 to Form1. Paste the following code to add a private member variable to Class Form1:

 

Dim m_success As Boolean

3. Paste the following sub procedure to create a ValidationEventHandler that raises validation errors in the XMLValidatingReader object:     

 

Public Sub ValidationEventHandle(ByVal sender As Object, ByVal args As ValidationEventArgs)
        m_success = False
        Console.WriteLine((ControlChars.CrLf & ControlChars.Tab & "Validation error: " & args.Message))
    End Sub 'ValidationEventHandle

 

NOTE: You must include an event handler to receive information about validation errors in the Data Type Definition (DTD), the XML-Data Reduced (XDR) schema, and the XML schema definition language (XSD) schema. The event handler receives an argument of type ValidationEventArgs that contains data related to this event.

The callback handler can use the ValidationEventArgs.Severity property to guarantee that an XML instance document is validated against a schema. The Severity property enables you to distinguish between a validation error (Severity is equal to XmlSeverityType.Error) which indicates a fatal error, and a validation warning (Severity is equal to XmlSeverityType.Warning) which indicates that no schema information is available.

 

4. Paste the following code in the Button1_Click event procedure:

 

        Dim reader As XmlValidatingReader = Nothing
        Dim myschema As New XmlSchemaCollection()

        Try
            'Create the XML fragment to be parsed.
            Dim xmlFrag As String = "<author  xmlns='urn:bookstore-schema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>" & _
                            "<first-name>Herman</first-name>" & _
                           "<last-name>Melville</last-name>" & _
                         "</author>"

            'Create the XmlParserContext.
            Dim context As New XmlParserContext(Nothing, Nothing, "", XmlSpace.None)
          
            'Implement the reader.
            reader = New XmlValidatingReader(xmlFrag, XmlNodeType.Element, context)
            'Add the schema.
            myschema.Add("urn:bookstore-schema", "Books.xsd")

            'Set the schema type and add the schema to the reader.
            reader.ValidationType = ValidationType.Schema
            reader.Schemas.Add(myschema)

            'Add the handler to raise the validation event.
            AddHandler reader.ValidationEventHandler, AddressOf Me.ValidationEventHandle

            While reader.Read

            End While
            Console.WriteLine("Completed validating xmlfragment")

        Catch XmlExp As XmlException
            Console.WriteLine(XmlExp.Message)
        Catch XmlSchExp As XmlSchemaException
            Console.WriteLine(XmlSchExp.Message)
        Catch GenExp As Exception
            Console.WriteLine(GenExp.Message)
        End Try
    End Sub
 

5.When the following message is displayed in the output window, the XML fragment is a valid element:

 

Completed validating xmlfragment

 

NOTE: The XMLValidatingReader object validates only the type declarations and the top level elements in the XML Schema. XML fragments, such as sub elements, are considered to be local. You cannot pass XML fragments to XmlValidatingReader for direct validation unless you declare the XML fragments as top-level elements and set the reference at the required level.

 

APPLIES TO

-	Microsoft Visual Basic .NET 2003 Standard Edition
-	Microsoft Visual Basic .NET 2002 Standard Edition
-	Microsoft .NET Framework 1.1
-	Microsoft .NET Framework 1.0

How to connect to an Oracle database by using ASP and ADO

 

INTRODUCTION

This article discusses how to connect to an Oracle database by using a Microsoft Active Server Pages (ASP) page and Microsoft ActiveX Data Objects (ADO).

 

MORE INFORMATION

To connect to an Oracle database, you can create an ASP page that contains the following code.

 

Note

 

Make sure that the connect string has a valid user ID and password and that the SQL statement references a valid table.

 

<%@ Language=VBScript %>
   <html>
   <head>
   <title>Oracle Test</title>
   </head>
   <body>
   <center>
   <%
     Set objConn = Server.CreateObject("ADODB.Connection")
     objConn.Open "Provider=MSDAORA;Data Source=<Your_TNSNames_Alias>;User Id=<userid>;Password=<password>;"

     Set objRs = objConn.Execute("SELECT * FROM DEMO.EMPLOYEE")

     Response.Write "<table border=1 cellpadding=4>"
     Response.Write "<tr>"

     For I = 0 To objRS.Fields.Count - 1
       Response.Write "<td><b>" & objRS(I).Name & "</b></td>"
     Next

     Response.Write "</tr>"

     Do While Not objRS.EOF
       Response.Write "<tr>"

       For I = 0 To objRS.Fields.Count - 1
         Response.Write "<td>" & objRS(I) & "</td>"
       Next

       Response.Write "</tr>"

       objRS.MoveNext
     Loop

     Response.Write "</table>"

     objRs.Close
     objConn.Close
   %>
   </center>
   </body>
   </html>

 

APPLIES TO

-	Microsoft Active Server Pages 4.0
-	Microsoft Internet Information Services 6.0
-	Microsoft Data Access Components 2.8

Applied Microsoft .NET Framework Programming

Applied Microsoft .NET Framework Programming is a tutorial. It's meant for programmers who already know an object-oriented language and want to apply their knowledge in the standardized environment provided by the Microsoft .NET Framework. The book, written by Jeffrey Richter, a programmer and the .NET columnist at Microsoft's magazine for its developer community, takes a more or less language-agnostic approach to the run-time environment (though many illustrative examples are in C#). It aims to untangle the Common Language Runtime (CLR) and some of the Framework Class Library (FCL), and generally succeeds, particularly at the former. Richter shares his knowledge of the key classes you can instantiate in the CLR, and the kinds of operations you can perform on and with them.

You can read this book, or individual chapters, from beginning to end. You'll probably find it more helpful, though, if you read individual sections as you encounter problems or develop an interest in specific aspects of the CLR (ideal for those middle-of-the-night "I wonder how it does..." questions). Richter typically lets his code do most of the talking, and he'll often introduce a section with a prose summary of the CLR way of doing something (sometimes with a supplementary diagram) before unleashing a string of quick examples that illustrate variations on the theme. In an unusual and helpful tutorial move, he makes heavy use of the ILDASM utility to show what goes on at compile time.

Topics covered: How the Microsoft .NET Framework--in other words, the Common Language Runtime (CLR) and parts of the Framework Class Library (FCL)--runs Microsoft .NET applications, and how to write software for the framework. Shared assemblies, characteristics of CLR types (including their properties, methods, fields, and events), and object orientation all get ample coverage. There's particularly detailed information on text manipulation (including internationalization and localization), arrays, custom interfaces, and the managed environment (garbage collection) in the CLR environment.

Book Description
This title takes advanced developers and software designers under the covers of .NET to provide them with an in-depth understanding of its structure, functions, and operational components so they can create high-performance applications for .NET more easily and efficiently. Developers learn to program .NET applications while gaining a solid understanding of fundamental .NET design tenets. This title not only covers the infrastructure and architecture of .NET in-depth but also shows developers the most practical ways to apply that knowledge.

Make Your ASP Work With An Oracle Database

Oracle is one of the most popular databases in the world, also Active Server Pages (ASP) is a powerful server-side scripting language widely used to build dynamic Web pages. There are many ASP developers who wonder if they can use the ASP technology with Oracle database to build a web application, E-commerce and E-business web sites or internet management systems. The answer is YES! You can access Oracle using VB to create Oracle Applications as well. Here, I will discuss with you how to use ASP dealing with Oracle data.

Before we start, there are a few things you need to know. The Oracle Objects for OLE (OO4O) method is one of them. The OO4O is an Oracle middleware that allows native access to Oracle from client applications using the Microsoft Object Linking and Embedding (OLE) standard. Some of you may think that the ODBC can be used. Surely, you can use the standard database access method developed by Microsoft Corporation to access Oracle, but in my opinion the OO4O is better than ODBC because the OO4O is thread safe and provides full support for PL/SQL. PL/SQL stands for Procedural Language/SQL. It is an Oracle extension of the SQL statement set which allows the developer to impose flow control and logic design onto unstructured SQL command blocks. If you have fully installed Oracle8i, I am sure the OO4O is there for use already. If not, you can download it from Oracle web site.

The other thing is, we need know two objects and one interface that Oracle developed for Visual Basic Development, OraSession and OraDynaset objects, and OraDatabase interface. The OraSession object manages collections of OraDatabase, OraConnection, and OraDynaset used within an application. It is the object created by the CreateObject ASP and not by an OO4O method. The OraDatabase interface represents a user session to an Oracle database and provides methods for SQL and PL/SQL execution. Each of them has some of properties, and methods. For instance, the OraDynaset has some of properties, such as BOF, EOF, Bookmark, Connection, and so on, and ten methods, such as AddNew, Update, Delete, Edit, Refresh, Clone, and so on.

Now, let's start working on Oracle data using ASP technology.

Source: ASP 101

How To Troubleshoot an ASP-to-Oracle Connectivity Problem

This article outlines some of the common steps to take when you troubleshoot a problem with a connection to an Oracle Database from an Active Server Pages (ASP) application. Some of the more common error messages are:

Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Oracle][ODBC][Ora]ORA-12154: TNS:Could not resolve service name /vdir/filename.asp, line xxx.

-and-

The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3 (or greater) client software installation.

You will be unable to use this driver until these components have been installed.

Source: Microsoft

How to connect to an Oracle database by using ASP and ADO

This article discusses how to connect to an Oracle database by using a Microsoft Active Server Pages (ASP) page and Microsoft ActiveX Data Objects (ADO).

Source: Microsoft