Setting up a Subversion Server under Windows

A) Download SubversionYou’ll need the latest version of..

• the Windows binaries
• the “run Subversion as a windows service” wrapper
• the TortoiseSVN shell integration utility

B) Install Subversion

1. Unzip the Windows binaries to a folder of your choice. I chose c:\program files\subversion\ as my path.
2. Now, add the subversion binaries to the path environment variable for the machine. I used %programfiles%\subversion\bin\
   
3. You’ll also need another environment variable, SVN_EDITOR, set to the text editor of your choice. I used c:\windows\notepad.exe
   

C) Create a Repository

1. Open a command prompt and type

   svnadmin create "c:\Documents and Settings\Subversion Repository"

2. Navigate to the folder we just created. Within that folder, uncomment the following lines in the /conf/svnserve.conf file:

   [general]
   anon-access = read
   auth-access = write
   password-db = passwd

   Next, uncomment these lines in the /conf/passwd file:

   [users]
   harry = harryssecret
   sally = sallyssecret

D) Verify that everything is working

1. Start the subversion server by issuing this command in the command window:

   svnserve --daemon --root "C:\Documents and Settings\Subversion Repository"

2. Create a project by opening a second command window and entering this command:

   svn mkdir svn://localhost/myproject

   It’s a standard Subversion convention to have three folders at the root of a project:

   /trunk
   /branches
   /tags

3. At this point, Notepad should launch:

   Enter any comment you want at the top of the file, then save and exit.

4. You’ll now be prompted for credentials. In my case I was prompted for the administrator credentials as well:

   Authentication realm:  0f1a8b11-d50b-344d-9dc7-0d9ba12e22df
   Password for 'Administrator': *********
   Authentication realm:  0f1a8b11-d50b-344d-9dc7-0d9ba12e22df
   Username: sally
   Password for 'sally': ************ 
   
   Committed revision 1.

   Congratulations! You just checked a change into Subversion!

E) Start the server as a service

1. Stop the existing command window that’s running svnserve by pressing CTRL+C.
2. Copy the file SVNService.exe from the zip file of the same name to the subversion\bin folder.
3. Install the service by issuing the following commands:

   svnservice -install --daemon --root "C:\Documents and Settings\Subversion Repository"
   sc config svnservice start= auto
   net start svnservice

4. Test the new service by listing all the files in the repository:

   svn ls svn://localhost/

   You should see the single project we created earlier, myproject/

F) Set up the shell extension

1. Run the TortoiseSVN installer. It will tell you to restart, but you don’t need to.
2. Create a project folder somewhere on your hard drive. Right click in that folder and select “SVN Checkout…”

   type svn://localhost/myproject/ for the repository URL and click OK.

   

3. Create a new file in that directory. Right click the file and select “TortoiseSVN, Add”
4. The file hasn’t actually been checked in yet. Subversion batches any changes and commits them as one atomic operation. To send all your changes to the server, right click and select “SVN Commit”:

And we’re done! You now have a networked Subversion server and client set up on your machine. Note that the default port for svnserve is 3690.

For more tips on using subversion, take a look at the free O’Reilly Subversion book.

Encrypt/Decrypt string VB.Net

Imports System.Security.Cryptography

Encryption Coding Is:

/*
* [strText]: string is that you need to encrypt
*[strEncrKey]: is the key to decrypt the string that has encryption

*/

Private Shared Function Encrypt(ByVal strText As String, ByVal strEncrKey As String) As String
Dim IV() As Byte = {&H12, &H34, &H56, &H78, &H90, &HAB, &HCD, &HEF}
Try
Dim bykey() As Byte = System.Text.Encoding.UTF8.GetBytes(Left(strEncrKey, 8))
Dim InputByteArray() As Byte = System.Text.Encoding.UTF8.GetBytes(strText)
Dim des As New DESCryptoServiceProvider
Dim ms As New MemoryStream
Dim cs As New CryptoStream(ms, des.CreateEncryptor(bykey, IV), CryptoStreamMode.Write)
cs.Write(InputByteArray, 0, InputByteArray.Length)
cs.FlushFinalBlock()
Return Convert.ToBase64String(ms.ToArray())
Catch ex As Exception
Return ex.Message
End Try
End Function

Decryption Code Is:
/*
* [strText]: a string that has been encrypt with the above method
*[sDecrKey]: string is the key needed to decrypt
*/
Private Shared Function Decrypt(ByVal strText As String, ByVal sDecrKey As String) As String
Dim IV() As Byte = {&H12, &H34, &H56, &H78, &H90, &HAB, &HCD, &HEF}
Dim inputByteArray(strText.Length) As Byte
Try
Dim byKey() As Byte = System.Text.Encoding.UTF8.GetBytes(Left(sDecrKey, 8))
Dim des As New DESCryptoServiceProvider
inputByteArray = Convert.FromBase64String(strText)
Dim ms As New MemoryStream
Dim cs As New CryptoStream(ms, des.CreateDecryptor(byKey, IV), CryptoStreamMode.Write)
cs.Write(inputByteArray, 0, inputByteArray.Length)
cs.FlushFinalBlock()
Dim encoding As System.Text.Encoding = System.Text.Encoding.UTF8
Return encoding.GetString(ms.ToArray())
Catch ex As Exception
Return ex.Message
End Try
End Function

Call Function:
Return  Encrypt(“string is that you need to encrypt“, “abc123″)
Return  Decrypt(“string that has been encrypt with the above method
“, “abc123″)

Ankh Subversion Plugin for Visual Studio Final Release

The flippin’ awesome Ankh Subversion plug-in for Visual Studio has its 1.0 Final release the other day.  Official post at http://arildf.spaces.live.com/blog/cns!E99F8B43533149B0!221.entry.  Download from http://ankhsvn.tigris.org/servlets/ProjectDocumentList?folderID=7315, you want the 2727-Final build (list isn’t in order).

Congrats and thanks to Arild for a great tool.

Oracle Connection – ADO.NET

Info

• ADO.NET Data Provider for Oracle (http://www.oracle.com/) databases

• Exists in namespace System.Data.OracleClient and assembly System.Data.OracleClient.dll

• Works on Windows, Linux x86, and Solaris x86. Can you help us get it to work on other systems, such as, Solaris SPARC and Mac OS X?

• Works with Oracle 8i, 9i and 10g databases

• Uses OCI (Oracle call-level interface) which is a C library for accessing Oracle databases

Current Status for System.Data.OracleClient

• You have two ways to connect to Oracle: using a User ID and Password or using external authentication using “Integrated Security=true” in your connection string

• OracleCommandBuilder has been implemented and can let you do simple Inserts/Updates/Deletes with a DataSet and OracleDataAdapter. However, OracleDataReader needs more implementation for getting KeyInfo and other meta data correctly. The Oracle SQL parser is very simple and will not be able to handle complex queries. Will need a real Oracle SQL parser which would use the jay compiler-compiler. Jay is used by mcs to parse c# files and System.Data to parse expressions in a DataColumn.

• REF CURSOR can be returned from a stored procedure as an OracleDataReader via an output parameter defined as OracleType.Cursor

• Connection pooling working

• OracleConnection can connect to Oracle 8i, 9i, and 10g databases via OCI (Oracle Call Level Interface)

• Input and Output parameters (char, varchar2, number, date, raw, long varchar, blob, clob, and timestamp) can now be used in SQL queries, non-queries, PL/SQL Blocks, and stored procedures.

• Input/Output and Return parameters have not been tested.

• OracleException and Error handling exists.

• Message handling needs to be added for non-critical messages received from Oracle (IN PROGRESS)

• Handling of various data types need to be added [IN PROGRESS]

• Data Adapter exists, and a DataSet can be filled using it.

• May not work on non-X86 platforms such as Mac OS X nor Solaris SPARC. Does not work due to byte-ordering or alignment issues with different processors. We accept patches to get these platforms working.

Action plan

• Parameters support needs to be Cleaned up (Input and Output parameters WORKING). Input/Output and Return parameters are a TODO)

• Support for Oracle 8i on Linux and Windows (WORKING)

• Support for Oracle 9i on Linux and Windows (WORKING. Still need to add features for 9i).

• Support for Oracle 10g on Linux and Windows (WORKING. Still need to add features for 10g)

• Support for Oracle 10g on Mac OS X. This requires resolving byte-ordering and alignment issues (TODO).

• Support for Oracle 10g on Solaris x86. (WORKING on Solaris 10 x86 and Solaris Express x86)

• Support for Oracle 10g on Solaris SPARC. This requires resolving byte-ordering and alignment issues (TODO)

• Support for Oracle 10g Express Edition on Windows and Linux (WORKING)

• Support Large Objects like LONG VARCHAR, BLOB, CLOB (WORKING)

• Support all the data types (STILL IMPLEMENTING). Still need to implement INTERVAL. Many of the Oracle .net types like OracleDateTime internally use a System .net type like DateTime. This is insufficient for supporting Oracle data types like TIMESTAMP.

• Security Audit (TODO)

• NLS / Unicode support / Multi-byte character sets (NEEDS IMPROVEMENT)

Testing

Prerequisites

• Have a working mono and mcs. Recommend at least Mono 1.1.10 since there has been lots of bug fixes.

• Have access to an Oracle 8i, 9i, 10g, or 10g Express database or download it from Oracle Downloads (http://www.oracle.com/technology/software/index.html). If you are connecting remotely to an Oracle database, you need the Oracle client software. Registration to the Oracle Technology Network (http://www.oracle.com/technology/index.html) is free. If installing on Linux, I suggest you do a lot of searching to see how others installed Oracle on Linux. Werner Puschitz’s Oracle on Linux page (http://www.puschitz.com/OracleOnLinux.shtml) contains great step by step guides on installing Oracle on Linux.

• Oracle Database 10g Express Edition (http://www.oracle.com/technology/software/products/database/xe/index.html) is free to download and redistribute. It works on Windows and Linux. It is also much easier to install the Express edition compared to the Enterprise and Standard editions.

• If you do not have any Oracle software installed on the computer you are running Mono but you need to access an Oracle database remotely, may I suggest using the Oracle Instant Client (http://www.oracle.com/technology/tech/oci/instantclient/index.html). The Oracle Instant Client is far more easier to install than the normal Oracle client software. You can also re-distribute the Oracle Instant Client for free.

• Installation on Debian/Ubuntu Linux is a breeze via apt-get. See Christian Heindel’s Blog on Oracle (http://www.christian-heindel.de/tags/oracle/).

 Basically, you add to your /etc/apt/sources.list

    deb http://oss.oracle.com/debian unstable main non-free 

 And then run the following:

    sudo apt-get update
    sudo apt-get install oracle-xe-universal
    sudo /etc/init.d/oracle-xe configure

• Oracle 9i and 10g is available for Mac OS X even though it may not work with Mono’s Oracle provider. Patches are welcomed though. Patches may include solving byte ordering and alignment issues with the OCI handling code.

• In the mcs source code, you can find tests at mcs/class/System.Data.OracleClient/Test

• The Data Source is an Oracle TNS Name

Connection String Format

• Has a connection string format:

 "Data Source=tnsname;User ID=userid;Password=password"

Connection String Format for Integrated Security

• Has a connection string format:

 "Data Source=tnsname;Integrated Security=true"

• Connection String Parameters:

Parameter Definition	Description	Example
Server or Data Source	TNS Name or Network Address of the Oracle instance to connect	Server=TESTDB
User ID	name of Oracle database user	User ID=scott
Password	password for Oracle database user	Password=mypass12
Integrated Security	To connect using external authentication or not. Valid values for Integrated Security are: YES or TRUE to use external authentication. NO or FALSE to not use external authentication. The default is false.	Integrated Security=true
Min Pool Size	Specifies the minimum number of connections in the connection pool. Default is 0.	Min Pool Size=0
Max Pool Size	Specifies the maximum number of connections in the connection pool. Default is 100.	Max Pool Size=100
Pooling	Specifies whether or not to use connection pooling. Valid values are TRUE or YES to use connection pooling or FALSE or NOT to not use connection pooling. Default is TRUE.	Pooling=true

• If you use Integrated Security, make sure you have a user created externally. Also, make sure you know what you are doing if you use external authentication – there are security implications. Read Oracle® Database Advanced Security Administrator’s Guide (http://download-east.oracle.com/docs/cd/B19306_01/network.102/b14268/asoauth.htm#sthref731) for more info about external authentication.

• How-To create a Windows user named SomeUser on Windows NT/2000 Domain MyNTDomain for external authentication in Oracle: If you’re not on a Windows domain, then your MachineName is the Domain. Note how the domain user is in double quotes and is upper case.

CREATE USER “MYNTDOMAIN\SOMEUSER” IDENTIFIED EXTERNALLY;

• How-To create a Linux user named someuser in Oracle which gets authenticated externally:

CREATE USER someuser IDENTIFIED EXTERNALLY;

Examples

C# Example 1a – Basic Connection and Retrieval

 using System;
 using System.Data;
 using System.Data.OracleClient;

 public class Test
 {
    public static void Main (string[] args)
    {
       string connectionString =
          "Data Source=testdb;" +
          "User ID=scott;" +
          "Password=tiger;";
       OracleConnection dbcon = null;
       dbcon = new OracleConnection (connectionString);
       dbcon.Open ();
       OracleCommand dbcmd = dbcon.CreateCommand ();
       string sql = "SELECT ename, job FROM scott.emp";
       dbcmd.CommandText = sql;
       OracleDataReader reader = dbcmd.ExecuteReader ();
       while (reader.Read ()) {
          string employeeName = (string) reader["ename"];
          string job = (string) reader["job"];
          Console.WriteLine ("Employee Name: {0}  Job: {1}",
		                    employeeName, job);
       }
       // clean up
       reader.Close ();
       reader = null;
       dbcmd.CommandText = sql;
       dbcmd.ExecuteNonQuery ();
       dbcmd.Dispose ();
       dbcmd = null;
       dbcon.Close ();
       dbcon = null;
    }
 }

C# Example 1b – Basic Connection and Retrieval using Integrated Security

 using System;
 using System.Data;
 using System.Data.OracleClient;

 public class Test
 {
    public static void Main (string[] args)
    {
       string connectionString =
          "Data Source=testdb;" +
          "Integrated Security=true";
       OracleConnection dbcon = null;
       dbcon = new OracleConnection (connectionString);
       dbcon.Open ();
       OracleCommand dbcmd = dbcon.CreateCommand ();
       string sql = "SELECT ename, job FROM scott.emp";
       dbcmd.CommandText = sql;
       OracleDataReader reader = dbcmd.ExecuteReader ();
       while (reader.Read ()) {
          string employeeName = (string) reader["ename"];
          string job = (string) reader["job"];
          Console.WriteLine ("Employee Name: {0}  Job: {1}",
		                    employeeName, job);
       }
       // clean up
       reader.Close ();
       reader = null;
       dbcmd.CommandText = sql;
       dbcmd.ExecuteNonQuery ();
       dbcmd.Dispose ();
       dbcmd = null;
       dbcon.Close ();
       dbcon = null;
    }
 }

C# Example 2 – Calling Stored Procedure

 using System;
 using System.Data;
 using System.Data.OracleClient;

 public class Test
 {
    public static void Main (string[] args)
    {
       string connectionString =
          "Data Source=testdb;" +
          "User ID=scott;" +
          "Password=tiger;";
       OracleConnection dbcon = null;
       dbcon = new OracleConnection (connectionString);
       dbcon.Open ();

       Console.WriteLine("  Drop table MONO_TEST_TABLE2...");
       OracleCommand dbcmd = dbcon.CreateCommand ();

       try {
              dbcmd.CommandText = "DROP TABLE MONO_TEST_TABLE2";
              dbcmd.ExecuteNonQuery ();
       }
       catch(OracleException oe1) {
              // ignore if table already exists
       }

       Console.WriteLine("  Drop procedure SP_TEST2...");
       try {
              dbcmd.CommandText = "DROP PROCEDURE SP_TEST2";
              dbcmd.ExecuteNonQuery ();
       }
       catch(OracleException oe1) {
              // ignore if table already exists
       }

       Console.WriteLine("  Create table MONO_TEST_TABLE2...");

       dbcmd.CommandText = "CREATE TABLE MONO_TEST_TABLE2 (" +
              " COL1 VARCHAR2(8), "+
              " COL2 VARCHAR2(32))";
       dbcmd.ExecuteNonQuery ();

       Console.WriteLine("  Create stored procedure SP_TEST2...");
       dbcmd.CommandText =
              "CREATE PROCEDURE SP_TEST2(parm1 VARCHAR2,parm2 VARCHAR2) " +
              " IS " +
              " BEGIN " +
              "	    INSERT INTO MONO_TEST_TABLE2 (COL1,COL2) VALUES (parm1,parm2);" +
              "	    COMMIT;" +
              " END;";

              cmd2.ExecuteNonQuery ();

       Console.WriteLine("  COMMIT...");
       dbcmd.CommandText = "COMMIT";
       dbcmd.ExecuteNonQuery ();

       Console.WriteLine("  Call stored procedure SP_TEST2 with two parameters...");
       OracleCommand dbcmd = con.CreateCommand ();
       dbcmd.CommandType = CommandType.StoredProcedure;
       dbcmd.CommandText = "sp_test2";

       OracleParameter myParameter1 = new OracleParameter("parm1", OracleType.VarChar);
       myParameter1.Value = "myval";
       myParameter1.Size = 8;
       myParameter1.Direction = ParameterDirection.Input;

       OracleParameter myParameter2 = new OracleParameter("parm2", OracleType.VarChar);
       myParameter2.Value = "another";
       myParameter2.Size = 32;
       myParameter2.Direction = ParameterDirection.Input;

       dbcmd.Parameters.Add (myParameter1);
       dbcmd.Parameters.Add (myParameter2);

       dbcmd.ExecuteNonQuery ();

       dbcmd.CommandText = sql;
       dbcmd.ExecuteNonQuery ();
       dbcmd.Dispose ();
       dbcmd = null;
       dbcon.Close ();
       dbcon = null;
    }
 }

C# Example 3 – Executing PL/SQL Block

Why would I want to do that? Let’s say I need to execute a stored procedure, yet there is no support for Boolean data type in OCI. I can do this via a PL/SQL block.

 using System;
 using System.Data;
 using System.Data.OracleClient;

 public class Test
 {
    public static void Main (string[] args)
    {
       string connectionString =
          "Data Source=testdb;" +
          "User ID=scott;" +
          "Password=tiger;";
       OracleConnection con = null;
       con = new OracleConnection (connectionString);
       con.Open ();
		OracleCommand cmd = con.CreateCommand();
		Console.WriteLine("  Drop procedure SP_TESTPLSQLBLOCK1...");
		try
		{
			cmd.CommandText = "DROP PROCEDURE SP_TESTPLSQLBLOCK1";
			cmd.ExecuteNonQuery ();
		}
		catch(OracleException oe1)
		{
			// ignore if table already exists
		}

		Console.WriteLine("  Create stored procedure SP_TESTPLSQLBLOCK1...");
		// stored procedure add two number depending on a boolean

		cmd.CommandText =
			"CREATE OR REPLACE PROCEDURE SP_TESTPLSQLBLOCK1(PARM1 IN NUMBER,PARM2 IN BOOLEAN,PARM3 OUT NUMBER)\n" +
			"IS\n" +
			"BEGIN\n" +
			"   IF PARM2 = TRUE THEN\n" +
			"	   PARM3 := PARM1 + 7;\n" +
			"	ELSE\n" +
			"	   PARM3 := PARM1 + 13;\n" +
			"   END IF;\n" +
			"END;";

		cmd.ExecuteNonQuery ();

		Console.WriteLine("  COMMIT...");
		cmd.CommandText = "COMMIT";
		cmd.ExecuteNonQuery ();

		Console.WriteLine("  Call stored procedure SP_TESTPLSQLBLOCK1 with 3 parameters...");
		cmd.CommandType = CommandType.Text;
		cmd.CommandText =
			"DECLARE " +
			"   addnum BOOLEAN := sys.diutil.int_to_bool(:P2); " +
			"BEGIN " +
			"	SP_TESTPLSQLBLOCK1(:P1,addnum,:P3); " +
			"END;";
		OracleParameter myParameter1 = new OracleParameter("P1", OracleType.Number);
		myParameter1.Value = 5;
		myParameter1.Direction = ParameterDirection.Input;

		OracleParameter myParameter2 = new OracleParameter("P2", OracleType.Number);
		myParameter2.Value = 1;
		myParameter2.Direction = ParameterDirection.Input;

		OracleParameter myParameter3 = new OracleParameter("P3", OracleType.Number);
		myParameter3.Direction = ParameterDirection.Output;

		cmd.Parameters.Add (myParameter1);
		cmd.Parameters.Add (myParameter2);
		cmd.Parameters.Add (myParameter3);

		cmd.ExecuteNonQuery ();
		decimal d = (decimal) myParameter3.Value;
		Console.WriteLine ("    Out Value should be: 12");
		Console.WriteLine ("    Out Value: {0}", d);
		cmd.Dispose();
		cmd = null;
		con.Close();
		con = null;
    }
 }

C# Example 4 – ASP.NET DataGrid Data Binding to an Oracle database table

First, you will need to make sure there is a table named CUSTOMERS with some data.

 CREATE TABLE CUSTOMERS (
	person varchar2(256) NOT NULL,
	email varchar2(256) NOT NULL
 );

 INSERT INTO CUSTOMERS
 (PERSON, EMAIL)
 VALUES('Bob Jones','bjones@nowhere.edu');

 INSERT INTO CUSTOMERS
 (PERSON, EMAIL)
 VALUES('John Smith','jswmith@somewhere.com');

 COMMIT;

<%@ Page Language="C#" %>
<%@ import namespace="System.Data" %>
<%@ import namespace="System.Data.Common" %>
<%@ import namespace="System.Data.SqlTypes" %>
<%@ import namespace="System.Data.OracleClient" %>
<%@ import namespace="System.Reflection" %>
<%@ assembly Name="System.Data.OracleClient" %>

<html>
<head>
<title>ASP.NET DataGrid Data Binding to an Oracle database table</title>

<!-- Source code modified from samples in xsp was originally made by Gonzalo Paniagua Javier -->

<script runat="server">

	static Type cncType = null;

	void GetConnectionData (out string providerAssembly,
                               out string cncTypeName, out string cncString)
	{
		providerAssembly = null;
		cncTypeName = null;
		cncString = null;
		NameValueCollection config =
                       ConfigurationSettings.AppSettings as NameValueCollection;
		if (config != null) {
			foreach (string s in config.Keys) {
				if (0 == String.Compare ("DBProviderAssembly", s, true)) {
					providerAssembly = config [s];
				} else if (0 == String.Compare ("DBConnectionType", s, true)) {
					cncTypeName = config [s];
				} else if (0 == String.Compare ("DBConnectionString", s, true)) {
					cncString = config [s];
				}
			}
		}

		if (providerAssembly == null || providerAssembly == "")
			providerAssembly = "System.Data.OracleClient";

		if (cncTypeName == null || cncTypeName == "")
			cncTypeName = "System.Data.OracleClient.OracleConnection";

		if (cncString == null || cncString == "")
			cncString = "Data Source=TESTDB;User ID=scott;Password=tiger";
	}

	IDbConnection cnc;
	void Page_Load (object o, EventArgs e)
	{
		string connectionTypeName;
		string providerAssemblyName;
		string cncString;

		GetConnectionData (out providerAssemblyName,
                       out connectionTypeName, out cncString);
		if (cncType == null) {
			Assembly dbAssembly = Assembly.LoadWithPartialName (
                                                      providerAssemblyName);
			cncType = dbAssembly.GetType (connectionTypeName, true);
			if (!typeof (IDbConnection).IsAssignableFrom (cncType))
				throw new ApplicationException (
                                        "The type '" + cncType +
					"' does not implement IDbConnection.\n" +
					"Check 'DbConnectionType' in web.config");
		}

		cnc = (IDbConnection) Activator.CreateInstance (cncType);
		cnc.ConnectionString = cncString;

		cnc.Open ();

		OracleCommand cmd = (OracleCommand) cnc.CreateCommand();
		cmd.CommandText = "select * from CUSTOMERS";
		DbDataAdapter adapter = new OracleDataAdapter(cmd);
		DataTable table = new DataTable("CUSTOMERS");
		adapter.Fill(table);
		grid.DataSource = table;
		grid.DataBind ();

		cmd.Dispose();
		cmd = null;
		cnc.Close ();
	}

	void Page_PreRender (object sender, EventArgs e)
	{
		if (cnc == null)
			return;
	}

 </script>
 </head>
 <body>
 <h3>DataGrid sample</h3>
 <form runat="server">
 	<asp:datagrid id="grid" border="1"
		EnableViewState="false" runat="server">
	</asp:datagrid>
 </form>
 </body>
 </html>

C# Example 5 – Returning a REF CURSOR from an Oracle stored procedure via an OracleDataReader from an out parameter

 using System;
 using System.Data;
 using System.Data.OracleClient;

 public class Test
 {
    public static void Main (string[] args)
    {
        string connectionString =
          "Data Source=testdb;" +
          "User ID=scott;" +
          "Password=tiger;";
        OracleConnection connection = null;
        connection = new OracleConnection (connectionString);
        connection.Open ();

	Console.WriteLine("Setup test package and data...");
	OracleCommand cmddrop = connection.CreateCommand();

	cmddrop.CommandText = "DROP TABLE TESTTABLE";
	try {
		cmddrop.ExecuteNonQuery();
	}
	catch(OracleException e) {
		Console.WriteLine("Ignore this error: " + e.Message);
	}
	cmddrop.Dispose();
	cmddrop = null;

	OracleCommand cmd = connection.CreateCommand();

	// create table TESTTABLE
	cmd.CommandText =
		"create table TESTTABLE (\n" +
		" col1 numeric(18,0),\n" +
		" col2 varchar(32),\n" +
		" col3 date)";
	cmd.ExecuteNonQuery();

	// insert some rows into TESTTABLE
	cmd.CommandText =
		"insert into TESTTABLE\n" +
		"(col1, col2, col3)\n" +
		"values(45, 'Mono', sysdate)";
	cmd.ExecuteNonQuery();

	cmd.CommandText =
		"insert into TESTTABLE\n" +
		"(col1, col2, col3)\n" +
		"values(136, 'Fun', sysdate)";
	cmd.ExecuteNonQuery();

	cmd.CommandText =
		"insert into TESTTABLE\n" +
		"(col1, col2, col3)\n" +
		"values(526, 'System.Data.OracleClient', sysdate)";
	cmd.ExecuteNonQuery();

	cmd.CommandText = "commit";
	cmd.ExecuteNonQuery();

	// create Oracle package TestTablePkg
	cmd.CommandText =
		"CREATE OR REPLACE PACKAGE TestTablePkg\n" +
		"AS\n" +
		"	TYPE T_CURSOR IS REF CURSOR;\n" +
		"\n" +
		"	PROCEDURE GetData(tableCursor OUT T_CURSOR);\n" +
		"END TestTablePkg;";
	cmd.ExecuteNonQuery();

	// create Oracle package body for package TestTablePkg
	cmd.CommandText =
		"CREATE OR REPLACE PACKAGE BODY TestTablePkg AS\n" +
		"  PROCEDURE GetData(tableCursor OUT T_CURSOR)\n" +
                "  IS\n" +
		"  BEGIN\n" +
		"    OPEN tableCursor FOR\n" +
		"    SELECT *\n" +
		"    FROM TestTable;\n" +
		"  END GetData;\n" +
		"END TestTablePkg;";
	cmd.ExecuteNonQuery();

	cmd.Dispose();
	cmd = null;

	Console.WriteLine("Set up command and parameters to call stored proc...");
	OracleCommand command = new OracleCommand("TestTablePkg.GetData", connection);
	command.CommandType = CommandType.StoredProcedure;
	OracleParameter parameter = new OracleParameter("tableCursor", OracleType.Cursor);
	parameter.Direction = ParameterDirection.Output;
	command.Parameters.Add(parameter);

	Console.WriteLine("Execute...");
	command.ExecuteNonQuery();

	Console.WriteLine("Get OracleDataReader for cursor output parameter...");
	OracleDataReader reader = (OracleDataReader) parameter.Value;

	Console.WriteLine("Read data...");
	int r = 0;
	while (reader.Read()) {
		Console.WriteLine("Row {0}", r);
		for (int f = 0; f < reader.FieldCount; f ++) {
			object val = reader.GetValue(f);
			Console.WriteLine("    Field {0} Value: {1}", f, val);
		}
		r ++;
	}
	Console.WriteLine("Rows retrieved: {0}", r);

	Console.WriteLine("Clean up...");
	reader.Close();
	reader = null;
	command.Dispose();
	command = null;

        connection.Close();
        connection = null;
    }
 }

Simple ways to View Connections in the pool created by ADO.NET

Table of Contents

• ADO.NET Connection Pooling at a Glance
• Connection Pool Creation
• Connection Pool Deletion / Clearing Connection Pool
• Controlling Connection Pool through Connection String

  • Sample Connection String with Pooling related keywords

• Simple ways to View Connections in the pool created by ADO.NET
• Common Issues/Exceptions/Errors with Connection Pooling
• Points to Ponder
• Other Useful Reads/References on Connection Pooling
• Wrapping up

ADO.NET Connection Pooling at a Glance

Establishing a Connection with a database server is a hefty and high resource consuming process. If any application needs to fire any query against any database server, we need to first establish a connection with server and then execute the query against that database server.

Not sure whether you felt like this or not, when you are writing any stored proc Or a query, the query returns the results with better response time than the response time, when you execute that same query from your any client application. I believe, one of the reasons for such behavior is the overheads involved in getting the desired results from the database server to the client application; and one of such overheads is establishing the Connection between the ADO.

Web applications frequently establish the database connection and close them as soon as they are done. Also notice how most of us write the database driven client applications. Usually, we have a configuration file specific to our application and keep the static information like Connection String in it. That intern means that most of the time we want to connect to same database server, same database, and with same user name and password, for every small and big data.

ADO.NET with IIS uses a technique called Connection Pooling, which is very helpful in applications with such designs. What it does is, on first request to database, it serves the database call. Once it is done and when client application requests for closing the connection, ADO.NET does not destroy the complete connection, rather it creates a connection pool and puts the released connection object in the pool and holds the reference to it. And next time when the request to execute any query/stored proc comes up, it bypasses the hefty process of establishing the connection and just picks up the connection from the connection pool and uses that for this database call. This way, it can return the results faster comparatively.

Let us see Connection Pooling Creation Mechanism in more detail.

Connection Pool Creation

Connection Pool and Connection String goes hands in hands. Every connection pool is associated with distinct connection string and that too, it is specific to the application. What in turn means is – a separate connection pool is maintained for every distinct process, app domain and connection string.

When any database request is made through ADO.NET, ADO.NET searches for the pool associated with exact match for the connection string, in the same app domain and process. If such pool is not found, ADO.NET creates a new one for it, however, if it is found, it tries to fetch the usable connection from that pool. If no usable free connection is found in the pool, new connection is created and added to the pool. This way, new connections keeps on adding to the pool till Max Pool Size is reached, after that when ADO.NET gets request for further connection, it waits for Connection Timeout time and then errors out.

Now the next question arises is – How any connection is released to pool to be available for such occasions? Once any connection has served and is closed/disposed, the connection goes to the connection pool and becomes usable. At times, connections are not closed/disposed explicitly, these connections do not go to the pool immediately. We can explicitly close the connection by using Close() or Dispose() method of connection object Or by using the “using” statement in C# to instantiate the connection object. It is highly recommended that we close or dispose(don’t wait for GC or connection pooler to do it for you) the connection once it has served the purpose.

Connection Pool Deletion / Clearing Connection Pool

Connection Pool is removed as soon as the associated app domain is unloaded. Once the app domain is unloaded, all the connections from the connection pool becomes invalid and are thus removed. Say for example, if you have an ASP.NET application, the connection pool gets created as soon as you hit the database very first time, and connection pool is destroyed as soon as we do iisreset. We’ll see it later with example. Note that connection pooling has to do with IIS Web Server and not with the Dev Environment, so do not expect the connection pool to be cleared automatically by closing your visual studio .Net dev environment.

ADO.NET 2.0 introduces two new methods to clear the pool: ClearAllPools and ClearPool. ClearAllPools clears the connection pools for a given provider, and ClearPool clears the connection pool that is associated with a specific connection. If there are connections in use at the time of the call, they are marked appropriately. When they are closed, they are discarded instead of being returned to the pool.

Refer to the section “Simple ways to View Connections in the pool created by ADO.NET” for details of how to determine the status of the pool.

Controlling Connection Pool through Connection String

Connection string plays a vital role in connection pooling. The handshake between ADO.NET and database server happens on the basis of this connection string only. Below is the table with important Connection Pooling specific keywords of the connection strings with their description.

Name	Default	Description
Connection Lifetime	0	When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime.A value of zero (0) causes pooled connections to have the maximum connection timeout.
Connection Timeout	15	Maximum Time (in Secs) to wait for a free connection from the tool
Enlist	‘true’	When true, the pooler automatically enlists the connection in the creation thread’s current transaction context. Recognized values are true, false, yes, and no.Set Enlist = “false” to ensure that connection is not context specific.
Max Pool Size	100	The maximum number of connections allowed in the pool.
Min Pool Size	0	The minimum number of connections allowed in the pool.
Pooling	‘true’	When true, the SQLConnection object is drawn from the appropriate pool, or if it is required, is created and added to the appropriate pool. Recognized values are true, false, yes, and no.
Incr Pool Size	5	Controls the number of connections that are established when all the connections are used.
Decr Pool Size	1	Controls the number of connections that are closed when an excessive amount of established connections are unused.

* Some Table contents are extracted from Microsoft MSDN Library for reference

Other than the above mentioned keywords, one important thing to note here. If you are using Integrated Security, then the connection pool is created for each user accessing the client system, whereas, when you use user id and password in the connection string, single connection pool is maintained across for the application. In the later case, each user can use the connections of the pool created and then released to the pool by other users. Thus using user id and password is recommended for better end user performance experience.

Sample Connection String with Pooling related Keywords

The connection string with the Pooling related keywords would look somewhat like this

initial catalog=Northwind; Data Source=localhost; Connection Timeout=30; User Id=MYUSER; Password=PASSWORD; Min Pool Size=20; Max Pool Size=200; Incr Pool Size=10; Decr Pool Size=5;

Simple ways to View Connections in the pool created by ADO.NET

We can keep a watch on the connections in the pool by determining the active connections in the database after closing the client application. This is a database specific stuff, so to see the active connections in the database server we must have to use database specific queries. This is with the exception that connection pool is perfectly valid and none of the connection in the pool is corrupted.

For MS SQL Server: Open the Query Analyser and execute the query : EXEC SP_WHO

For Oracle : Open the SQL Plus or any other editor like PL/SQL Developer or TOAD and execute the following query — SELECT * FROM V$SESSION WHERE PROGRAM IS NOT NULL

All right, let us do it with SQL Server 2000

1. Create a Sample ASP.NET Web Application

2. Open an instance of Query Analyzer and run the EXEC SP_WHO query. Note the loginname column, and look for MACHINENAME\ASPNET. If you have not run any other ASP.NET application, you will get no rows with loginname as “MACHINENAME\ASPNET”.

3. On Page load of default startup page, add a method that makes a database call. Say your connection string is “initial catalog=Northwind; Min Pool Size=20;Max Pool Size=500; data source=localhost; Connection Timeout=30; Integrated security=sspi”

4. Run your ASP.NET application

5. Now repeat Step 2 and observe that there are exactly 20 (Min Pool Size) connections in the results. Note that you made the database call only once.

6. Close the web page of your web application and repeat step 2. Observe that even after you closed the instance of the web page connections persists.

7. Now Reset the IIS. You can do that by execute the command “iisreset” on the Run Command.

8. Now Repeat Step 2 and observe that all the 20 connections are gone. This is because your app domain has got unloaded with IIS reset.

Common Issues/Exceptions/Errors with Connection Pooling

1. You receive the exception with the message: “Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached” in your .NET client application.

This occurs when you try using more than Max Pool Size connections. By default, the max pool size is 100. If we try to obtain connection more than max pool size, then ADO.NET waits for Connection Timeout for the connection from the pool. If even after that connection is not available, we get the above exception.

Solution(s):

1. Very first step that we should do is – Ensure that every connection that is opened, is Closed explicitly. At times what happens is, we open the connection, performs the desired database operation, but we do not close the connection explicitly. Internally it cannot be used as available valid connection from pool. The application would have to wait for GC to claim it, until then it is not marked as available from pool. In such case, even though you are not using max pool size number of connection simultaneously, you may get this error. This is the most probable cause of this issue.

2. Increase Max Pool Size value to a sufficient Max value. You can do so by including “Max Pool Size = N;” in the connection string, where N is the new Max Pool size.

3. Set the Pooling Off. Well, this indeed is not a good idea as Connection Pooling puts a positive performance effect but it definitely is better that getting any such exceptions.

2. You receive the exception with the message: “A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 – Shared Memory Provider: )” in your ASP.NET application with MS SQL Server
This occurs when MS SQL Server 2000 encounter some issues and has to refresh all the connections and ADO.NET still expects the connection from the pool. Basically, it occurs when connection pool gets corrupted. What in turn happens is, ADO.NET thinks that the valid connection exists with database server, but actually, due to database server getting restarted it has lost all the connections.

Solution(s) :

1. If you are working with .NET and Oracle using ODP.NET v 9.2.0.4 or above, you can probably try adding “Validate Connection=true” in the connection string. Well, in couple of places, I noticed people saying use “validcon=true” works for them for prior versions on ODP.NET. See which works for you. With ODP.NET v 9.2.0.4, “validcon=true” errors out and “Validate Connection=true” works just fine.

2. If you are working with .NET 2.0 and MS SQL Server, You can clear a specific connection pool by using the static (shared in Visual Basic .NET) method SqlConnection.ClearPool or clear all of the connection pools in an appdomain by using the SqlConnection.ClearPools method. Both SqlClient and OracleClient implement this functionality.

3. If you are working with .NET 1.1 and MS SQL Server,

a. In the connection string at the run time append a blank space and try establishing the connection again. What in turn it would do is, a new connection pool would be created and will be used by your application, In the meantime the prior pool will get removed if it’s not getting used.

b. Do exception handling, and as soon as you get this error try connection afresh repeatedly in the loop. With time, ADO.NET and database server will automatically get in sync.

Well, I am not totally convinced with either approach, but frankly speaking, I could not get any better workable solution for this so far.

3. Leaking Connections

When we do not close/dispose the connection, GC collects them in its own time, such connections are considered as leaked from pooling point of view. There is a strange possibility that we reach max pool size value and at that given moment of time without actually using all of them, having couple of them leaked and waiting for GC to work upon them. This would actually lead to the exception mentioned above, even if we are not using max pool size number of connections.

Solution(s):

1. Ensure that we Close/Dispose the connections once its usage is over.

Other Useful Reads/References on Connection Pooling

1. http://samples.gotdotnet.com/QuickStart/howto/doc/adoplus/connectionpooling.aspx

2. ADO.NET Connection Pooling Explained :::: http://www.ondotnet.com/pub/a/dotnet/2004/02/09/connpool.html

3. The .NET Connection Pool Lifeguard :::: http://msdn2.microsoft.com/en-us/library/aa175863(SQL.80).aspx