This topic describes how to use the ADO.NET Data Provider for PolarDB (PolarDB .NET) driver to connect a C# application to an ApsaraDB for PolarDB database.

Prerequisites

  • You have created an account for an ApsaraDB for PolarDB cluster. For more information about how to create an account, see Create a database account.
  • 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 .NET is a driver used to connect to ApsaraDB for PolarDB by using a programming language, including C#, Visual Basic, and F #. The driver is compatible with Entity Framework Core and Entity Framework 6.x. You can use this driver with Entity Framework to quickly develop applications.

The current driver uses the PostgreSQL 3.0 protocol and is compatible with .NETFramework 4.x and .NET Core 2.x.

Entity Framework overview

Entity Framework is a popular object-relational mapper (O/RM) on the .NET platform. It works with Language-Integrated Query (LINQ) technologies to greatly accelerate the development of backend applications if the C# language is used.

The PolarDB .NET driver provides the PolarDB Entity Framework 5 and 6 dlls to help you use Entity Framework.

For more information about Entity Framework, visit its official website at https://docs.microsoft.com/en-au/ef/.

Download the PolarDB .NET driver

Download the PolarDB .NET driverDownload the PolarDB .NET driver.

Install the PolarDB .NET driver

  1. Decompress the PolarDB .NET driver.
    unzip POLARDB-for-Oracle-.net_installer.zip
  2. Import the driver to the Visual Studio project.

    Add the following content to the <Project> node of sample. csproj or the GUI of Visual Studio.

    <Project>
      ...
      <ItemGroup>
        <Reference Include="POLARDB.POLARDBClient, Version=4.0.4.1, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7">
          <HintPath>${your path}\POLARDB.POLARDBClient.dll</HintPath>
        </Reference>
      </ItemGroup>
      ...
    </Project>

Example

In the Samples folder, you can see the polardb-sample.sql file and multiple sample project files. The following procedure shows how to run these sample projects.

  1. Connect to a database. For more information, see Connect to a PolarDB-O cluster.
  2. Run the following command to create a project namedsampledb.
    CREATE DATABASE sampledb;
  3. Import the databases, tables, data, and functions that are required for testing to database sampledb.
    \i ${your path}/polardb-sample.sql
  4. After the data is imported, write the C # code.

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

    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=1521;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();
                    }
                    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();
                }
    
            }
        }
    }

    Where, the code string Server=localhost;Port=1521;User Id=polaruser;Password=password;Database=sampledb is a connection string used to connect to the database.

    The connection string consists of theServer,Port,User Id,Password, andDatabase parameters, as described in the following table.

    Parameter Example Description
    Server localhost The endpoint of the ApsaraDB for PolarDB cluster. For information about how to query the endpoint, see View endpoints.
    Port 1521 The port of the ApsaraDB for PolarRDB cluster. Default value: 1521.
    User Id polaruser The username for connecting to the ApsaraDB for PolarDB cluster.
    Password password The password of the username.
    Database sampledb The name of the database to be connected.