PolarDB:.NET

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.
      
   3. In the Select the files to reference dialog box, select a driver and click Add.
      
   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.