This topic describes how to use the ADO.NET Data Provider for PolarDB for PostgreSQL(Compatible with Oracle) (PolarDB .NET) driver to connect C# applications to PolarDB for PostgreSQL(Compatible with Oracle) databases.

Prerequisites

Background information

PolarDB .NET is a driver that allows you to use programming languages such as C#, Visual Basic, and F# to connect to PolarDB for PostgreSQL(Compatible with Oracle) databases. The PolarDB .NET driver is compatible with Entity Framework Core and Entity Framework 6.x. You can use PolarDB .NET in conjunction with Entity Framework to develop applications in a quick way.

The PolarDB .NET driver uses version 3.0 of the PostgreSQL protocol. The driver is compatible with .NET Framework 2.0, .NET Framework 4.0, .NET Framework 4.5, and .NET Core 2.0.

In the earlier versions of the PolarDB .NET driver, most class names start with POLARDB. In the latest version of the driver, the class names start with PolarDB. Therefore, you must replace POLARDB with PolarDB in your code. The logic in the driver is not modified, so services are not affected after you upgrade your driver and update your code.

Entity Framework overview

Entity Framework is an object-relational mapping (ORM) framework that is widely adopted in the .NET platform. Entity Framework and Language-Integrated Query (LINQ) technologies provide a quick method for you to develop backend C# applications.

The PolarDB .NET driver provides the .dll files of Entity Framework 5 (EF5) and Entity Framework 6 (EF6), and the .dll files are applicable to PolarDB for PostgreSQL(Compatible with Oracle). This helps you use Entity Framework.

For more information about Entity Framework, visit the official website of Entity Framework.

Install the PolarDB .NET driver

  1. Download the package of the PolarDB .NET driver.
  2. Decompress the package of the PolarDB .NET driver.
    unzip polardb_oracle_.net.zip
  3. Import the driver to a Visual Studio project.
    1. In the left-side navigation pane on the graphical user interface (GUI) of Visual Studio, right-click Projects and click Add Reference.
    2. In the Reference Manager dialog box, click Browse in the left-side navigation pane, and click the Browse... button.
      Visual Studio-1
    3. In the Select the files to reference dialog box, select a driver and click Add.
      Visual Studio-2
    4. Click OK.

Example

The Samples directory stores the PolarDBSample.sql file and multiple sample project files. The following procedure shows how to run the sample projects.

  1. Connect to a database. For more information, see Connect to a cluster.
  2. Execute the following statement to create a database named sampledb.
    CREATE DATABASE sampledb;
  3. Import the tables, the data, and the functions that are required for testing to the sampledb database.
    \i ${your path}/PolarDBSample.sql
  4. After the data is imported, write C# code.

    The following sample code shows how to query, update, and call the stored procedures of the sample projects.

    using System;
    using System.Data;
    using PolarDB.PolarDBClient;
    /*
     * This class provides a simple way to perform DML operation in PolarDB
     *
     * @revision 1.0
     */
    
    namespace PolarDBClientTest
    {
    
        class SAMPLE_TEST
        {
    
            static void Main(string[] args)
            {
                PolarDBConnection conn = new PolarDBConnection("Server=localhost;Port=5432;User Id=polaruser;Password=password;Database=sampledb");
                try
                {
                    conn.Open();
    
                    //Simple select statement using PolarDBCommand object
                    PolarDBCommand PolarDBSeletCommand = new PolarDBCommand("SELECT EMPNO,ENAME,JOB,MGR,HIREDATE FROM EMP",conn);
                    PolarDBDataReader SelectResult =  PolarDBSeletCommand.ExecuteReader();
                    while (SelectResult.Read()) 
                    {
                        Console.WriteLine("Emp No" + " " + SelectResult.GetInt32(0));
                        Console.WriteLine("Emp Name" + " " + SelectResult.GetString(1));
                        if (SelectResult.IsDBNull(2) == false)
                            Console.WriteLine("Job" + " " + SelectResult.GetString(2));
                        else
                            Console.WriteLine("Job" + " null ");
                        if (SelectResult.IsDBNull(3) == false)
                            Console.WriteLine("Mgr" + " " + SelectResult.GetInt32(3));
                        else
                            Console.WriteLine("Mgr" + "null");
                        if (SelectResult.IsDBNull(4) == false)
                            Console.WriteLine("Hire Date" + " " + SelectResult.GetDateTime(4));
                        else
                            Console.WriteLine("Hire Date" + " null");
                        Console.WriteLine("---------------------------------");
                    }
    
                    //Insert statement using PolarDBCommand Object
                    SelectResult.Close();
                    PolarDBCommand PolarDBInsertCommand = new PolarDBCommand("INSERT INTO EMP(EMPNO,ENAME) VALUES((SELECT COUNT(EMPNO) FROM EMP),'JACKSON')",conn);
                    PolarDBInsertCommand.ExecuteScalar();
                    Console.WriteLine("Record inserted");
    
                    //Update  using PolarDBCommand Object
                    PolarDBCommand  PolarDBUpdateCommand = new PolarDBCommand("UPDATE EMP SET ENAME ='DOTNET' WHERE EMPNO < 100",conn);
                    PolarDBUpdateCommand.ExecuteNonQuery();
                    Console.WriteLine("Record has been updated");
                    PolarDBCommand PolarDBDeletCommand = new PolarDBCommand("DELETE FROM EMP WHERE EMPNO < 100",conn);
                    PolarDBDeletCommand.CommandType= CommandType.Text;
                    PolarDBDeletCommand.ExecuteScalar();
                    Console.WriteLine("Record deleted");
    
                    //procedure call example
                    try
                    {
                        PolarDBCommand callable_command = new PolarDBCommand("emp_query(:p_deptno,:p_empno,:p_ename,:p_job,:p_hiredate,:p_sal)", conn);
                        callable_command.CommandType = CommandType.StoredProcedure;
                        callable_command.Parameters.Add(new PolarDBParameter("p_deptno",PolarDBTypes.PolarDBDbType.Numeric,10,"p_deptno",ParameterDirection.Input,false ,2,2,System.Data.DataRowVersion.Current,20));
                        callable_command.Parameters.Add(new PolarDBParameter("p_empno", PolarDBTypes.PolarDBDbType.Numeric,10,"p_empno",ParameterDirection.InputOutput,false ,2,2,System.Data.DataRowVersion.Current,7369));
                        callable_command.Parameters.Add(new PolarDBParameter("p_ename", PolarDBTypes.PolarDBDbType.Varchar,10,"p_ename",ParameterDirection.InputOutput,false ,2,2,System.Data.DataRowVersion.Current,"SMITH"));
                        callable_command.Parameters.Add(new PolarDBParameter("p_job", PolarDBTypes.PolarDBDbType.Varchar,10,"p_job",ParameterDirection.Output,false ,2,2,System.Data.DataRowVersion.Current,null));
                        callable_command.Parameters.Add(new PolarDBParameter("p_hiredate", PolarDBTypes.PolarDBDbType.Date,200,"p_hiredate",ParameterDirection.Output,false ,2,2,System.Data.DataRowVersion.Current,null));
                        callable_command.Parameters.Add(new PolarDBParameter("p_sal", PolarDBTypes.PolarDBDbType.Numeric,200,"p_sal",ParameterDirection.Output,false ,2,2,System.Data.DataRowVersion.Current,null));
                        callable_command.Prepare();
                        callable_command.Parameters[0].Value = 20;
                        callable_command.Parameters[1].Value = 7369;
                        PolarDBDataReader result = callable_command.ExecuteReader();
                        int fc = result.FieldCount;
                        for(int i=0;i<fc;i++)
                            Console.WriteLine("RESULT["+i+"]="+ Convert.ToString(callable_command.Parameters[i].Value));
                        result.Close();
                    }
                    // If the driver version is .NET 2.0, you must modify the following statements:
                    catch(PolarDBException exp)
                    {
                        if(exp.ErrorCode.Equals("01403"))
                            Console.WriteLine("No data found");
                        else if(exp.ErrorCode.Equals("01422"))
                            Console.WriteLine("More than one rows were returned by the query");
                        else
                            Console.WriteLine("There was an error Calling the procedure. \nRoot Cause:\n");
                        Console.WriteLine(exp.Message.ToString());
                    }
    
                    //Prepared statement
                    string updateQuery  = "update emp set ename = :Name where empno = :ID";
                    PolarDBCommand Prepared_command = new PolarDBCommand(updateQuery, conn);
                    Prepared_command.CommandType = CommandType.Text;
                    Prepared_command.Parameters.Add(new PolarDBParameter("ID", PolarDBTypes.PolarDBDbType.Integer));
                    Prepared_command.Parameters.Add(new PolarDBParameter("Name", PolarDBTypes.PolarDBDbType.Text));
                    Prepared_command.Prepare();
                    Prepared_command.Parameters[0].Value = 7369;
                    Prepared_command.Parameters[1].Value = "Mark";
                    Prepared_command.ExecuteNonQuery();
                    Console.WriteLine("Record Updated...");
                }
    
                catch(PolarDBException exp)
                {
                    Console.WriteLine(exp.ToString() );
                }
                finally
                {
                    conn.Close();
                }
    
            }
        }
    }

Connection string parameters

Applications must provide connection strings to connect to databases. The connection strings include the host, username, and password parameters.

Connection strings are in the keyword1=value; keyword2=value; format, and are not case-sensitive. You can use double quotation marks (") to enclose the values that contain special characters, such as semicolons (;).

The current driver supports the following connection string parameters.

Table 1. Parameters related to basic connections
ParameterExampleDescription
HostlocalhostThe endpoint of the PolarDB for PostgreSQL(Compatible with Oracle) cluster. For more information about how to view the endpoint, see View or apply for an endpoint.
Port1521The port of the PolarDB for PostgreSQL(Compatible with Oracle) cluster. Default value: 1521.
DatabasesampledbThe name of the database to be connected.
UsernamepolaruserThe username that is used to connect to the PolarDB for PostgreSQL(Compatible with Oracle) cluster.
PasswordpasswordThe password for the username of the PolarDB for PostgreSQL(Compatible with Oracle) cluster.
Table 2. Parameters related to connection pools
ParameterExampleDescription
PoolingtrueSpecifies whether to enable the connection pool.
Minimum Pool Size0The minimum size of the connection pool.
Maximum Pool Size100The maximum size of the connection pool.
Connection Idle Lifetime300The time-out period. After the time-out period expires, idle connections are closed in the pool if the number of all the connections exceeds the value of the Minimum Pool Size parameter. Unit: second.
Connection Pruning Interval10The interval for deleting idle connections. Unit: second.
Table 3. Other parameters
ParameterDescription
application_nameThe name of the application.
search_pathThe search path of the schema.
client_encodingThe client encoding.
timezoneThe time zone of the session.