This topic describes how to use the ADO.NET Data Provider for PolarDB-O (PolarDB-O .NET) driver to connect C# applications to PolarDB-O databases.

Prerequisites

  • You have created an account for an ApsaraDB for PolarDB cluster. For more information about how to create an account, see Create database accounts.
  • You have added the IP address of the host that you want to connect to the ApsaraDB for PolarDB cluster to the whitelist. For more information, see Configure a whitelist for a cluster.

Background information

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

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

In the earlier versions of the PolarDB-O .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-O .NET driver provides the .dll files of Entity Framework 5 (EF5) and Entity Framework 6 (EF6), and the .dll files are applicable to PolarDB-O. This helps you use Entity Framework.

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

Install the PolarDB-O .NET driver

  1. Download the package of the PolarDB-O .NET driver.
  2. Decompress the package of the PolarDB-O .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 PolarDB-O 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 1.1 or .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
Parameter Example Description
Host localhost The endpoint of the PolarDB-O cluster. For more information about how to view the endpoint, see View endpoints.
Port 1521 The port of the PolarDB-O cluster. Default value: 1521.
Database sampledb The name of the database to be connected.
Username polaruser The username that is used to connect to the PolarDB-O cluster.
Password password The password for the username of the PolarDB-O cluster.
Table 2. Parameters related to connection pools
Parameter Example Description
Pooling true Specifies whether to enable the connection pool.
Minimum Pool Size 0 The minimum size of the connection pool.
Maximum Pool Size 100 The maximum size of the connection pool.
Connection Idle Lifetime 300 The 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 Interval 10 The interval for deleting idle connections. Unit: second.
Table 3. Other parameters
Parameter Description
application_name The name of the application.
search_path The search path of the schema.
client_encoding The client encoding.
timezone The time zone of the session.