Oracle Recipe #2: How to execute a query that returns a Scalar result with OracleCommand.

  1. Microsoft ADO.NET command objects have an ExecuteScalar method, which enables you to execute a query that returns a single result.
  2. Open a database connection.
  3. Create and initialize a command object.
  4. Call the ExecuteScalar method on the command object.
  5. Convert the return value from ExecuteScalar into an appropriate data type.
  6. Dispose the command object.
  7. Close the database connection.

The following example, show how to execute a query that determines the average salary from the table employees on the HR schema provided by Oracle Database XE.
The example assume that the query does not return a NULL result.

Fig 1. OracleCommand ExecuteScalar method code example.

Download source code

How to connect an Oracle Data Source by using ADO.NET

Step 1: Provide a connection string

To connect to a database, you must provide a connection string to identify the database. The following list describes several common parameters of connection strings.

  • Persist Security Info: if is false, the data source does not return security-sensitive if the connection is open.
  • User ID and Password: The data source login and password to use if you are not using integrated security.
  • Integrated Security or Trusted_Connection: if true, the data source uses the current account credentials for authentication, if false, you must specify the User ID and Password in the connection string.
  • Data Source: The name or network address of the data source instance.
  • Initial Catalog or Database: the name of the database.
  • Connection Timeout: The length of time in seconds to wait for a connection to the server before the data source terminates the attempt and returns an error. The default timeout is 15 seconds.
Fig 1. Provide a connection string.

Step 2: Retrieve a connection string from an application configuration file.

You can either store connection strings in an application configuration file or you can hard-code them directly in your application. If you store connection strings in the configuration file, you can modify them easily, without having to edit the source code and recompile the application.
Each connection string that is stored in an application configuration file has its own assigned name. In an application, you can access a connection string by its programmatic name.

Fig 2. Create and return an OracleConnection object.

Step 3: Handle connection events

ADO.NET connection objects have two events that you can use to retrieve informational messages from a data source or to determine if the state of a connection has changed.

  • InfoMessage: Occurs when a data source returns an informational message. Informational messages are messages from a data source that do not result in an exception being thrown.
  • StateChage: Occurs when a connection changes from the closed state to the open state or from the open state to the closed state.

If an error occurs at the data source, the data provider throws an exception. However, if the data source returns an informational message, the data provider raises an InfoMessage event instead.

Step 4: Handle connection exceptions

The .NET Data provider for Oracle throws an OracleException when it encounters an error or warning generated by an Oracle database. OracleException objects have a Code property that gets the code portion of the error as an integer and message property that gets a description of the error.

Fig 3. Step 3 and 4: Handle connection events and exceptions, you can see the example below.

The following code show how to test each step in C# code.

Fig 4. The code to test our classes.

Fig 5. We run the program and verify the events in the log.

Fig 6. The program wrote the events in the log.

Download source code.

Oracle Recipe #1: How to generate an IDENTITY column using a sequence

Primary keys can be created using either numeric or character data types, with one exception: Oracle does not have the same concept of identity columns as MS SQL Server.

What is an Identity Column?

It is a property that automatically generates a unique sequential value when it is assigned to a numeric data type. Oracle handles this concept using a database object called sequence.

The Sequence database object enables you to generate a unique sequence number. Each user of the sequence can increment it and obtain numbers for their use. Because multiple users can obtain sequence numbers, there is no guarantee that the numbers you get will not have gaps.

A sequence does not have to be related to a single table, and therefore could be used to provide unique numbers to multiple tables. As a general rule, having one sequence per table or at least one for each major table results in easier diagnostics and a better overall experience.
The following examples show how you can add an identity column for two tables: categories and publishers.

Each table will use a IDENTITY column as PRIMARY KEY.
Fig 1. The categories and publishers catalog.

Fig 2. Script to create both tables.

The following code shows an example of how to select the NEXTVAL from a sequence. Upon executing this trigger, the sequence value is incremented by 1.

Fig 3. Script to create database objects: triggers and sequences.

Each time we add a new row, the trigger will automatically create a new unique number for that row.
With this concept, the first row ID would be 1, and the next ID would be last ID number plus one.

Fig 4. Inserting rows without primary key.

Fig 5.Querying the tables.

Download Scripts

Understanding delegates with C#

A delegate is similar to a function pointer in C or C++ except that delegates are type-safe. The term type-safe means that code is specified in a well-defined manner that can be recognized by a compiler. In this case it means that an incorrect use of a delegate is a compile-time error. This is quite different than in C++, where an incorrect use of a function pointer may not cause an error until the program is running.

Delegates allow you to write code that can dynamically change the methods that it calls.

A delegate contains a reference to a method rather than the method name.

By using delegates, you can invoke a method without knowing its name. Calling the delegate will actually execute the method referenced by the delegate.

To use a delegate, you must follow these steps:

  1. First, define it using the reserved keyword delegate.
  2. Second, instantiate it.
  3. Third, write the implementation with the same return value and signature of your delegate.
Fig 1. Steps to use a delegate.

A delegate is similar to an interface. It specifies a contract between a caller and an implementer.

The following code shows how to define, create and call delegates, It creates an array of delegates with instances of delegates that refers to the methods that represent each arithmetic operation.
We execute each delegate with a foreach keyword that iterates through this array.

Fig 2. Sample using delegates.

Using delegates is a solution much simpler than using function pointer.

Fig 3. Testing the program.

Fig 4. Another test.

Download source code

Fetching JSON Data with Angular $http.get() function.

Angular has built-in support for communication with remote HTTP servers and includes some low-level methods of fetching and posting the data. Angular comes with the $http service which includes a few methods we can utilize with all verbs of the REST protocol.

We’ll look at a example using the .$http.get() request. The $http.get() method accepts two parameters: URL and config object.

The first parameter URL is always required and the config is optional, the shortcut $http.get() method initiates a GET request to the server to retrieve data from the server.
This example has the following files:

  • books.js: it contains the data in JSON format.
  • app.js: it contains the functional logic for the example
  • getexample.html: contains the front for the example
Fig.1. The source code for the JSON file

Fig 2. The source code for the app.js

In the previous example the controller defines a dependency to the $scope and the $http module. An HTTP GET request to the data “books.json” endpoint is carried out with the get method. It returns a $promise object with a success and error method.

Fig 3. The code for the web page

Fig 4. Running the example

If you open the web page up in your browser, you’ll see a standard HTML button created, when you press the button the $http service makes an ajax call and set response to the scope’s property books. Thus books can be used to draw a list in the HTML page.

Download the project for Visual Studio