PolarDB:.NET

Query data

Use PolarDBCommand and PolarDBDataReader to run a SELECT statement and read each row.

using System;
using System.Data;
using PolarDB.PolarDBClient;

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();

                // Query — read employee records
                PolarDBCommand selectCmd = new PolarDBCommand(
                    "SELECT EMPNO,ENAME,JOB,MGR,HIREDATE FROM EMP", conn);
                PolarDBDataReader reader = selectCmd.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine("Emp No" + " " + reader.GetInt32(0));
                    Console.WriteLine("Emp Name" + " " + reader.GetString(1));
                    if (reader.IsDBNull(2) == false)
                        Console.WriteLine("Job" + " " + reader.GetString(2));
                    else
                        Console.WriteLine("Job" + " null ");
                    if (reader.IsDBNull(3) == false)
                        Console.WriteLine("Mgr" + " " + reader.GetInt32(3));
                    else
                        Console.WriteLine("Mgr" + "null");
                    if (reader.IsDBNull(4) == false)
                        Console.WriteLine("Hire Date" + " " + reader.GetDateTime(4));
                    else
                        Console.WriteLine("Hire Date" + " null");
                    Console.WriteLine("---------------------------------");
                }
                reader.Close();
            }
            catch (PolarDBException exp)
            {
                Console.WriteLine(exp.ToString());
            }
            finally
            {
                conn.Close();
            }
        }
    }
}

Insert data

Use ExecuteScalar() to run an INSERT statement.

// Insert — add a new employee row
PolarDBCommand insertCmd = new PolarDBCommand(
    "INSERT INTO EMP(EMPNO,ENAME) VALUES((SELECT COUNT(EMPNO) FROM EMP),'JACKSON')", conn);
insertCmd.ExecuteScalar();
Console.WriteLine("Record inserted");

Update data

Use ExecuteNonQuery() to run an UPDATE statement.

// Update — rename employees with EMPNO < 100
PolarDBCommand updateCmd = new PolarDBCommand(
    "UPDATE EMP SET ENAME='DOTNET' WHERE EMPNO < 100", conn);
updateCmd.ExecuteNonQuery();
Console.WriteLine("Record has been updated");

Delete data

Use ExecuteScalar() to run a DELETE statement.

// Delete — remove employees with EMPNO < 100
PolarDBCommand deleteCmd = new PolarDBCommand(
    "DELETE FROM EMP WHERE EMPNO < 100", conn);
deleteCmd.CommandType = CommandType.Text;
deleteCmd.ExecuteScalar();
Console.WriteLine("Record deleted");

Call a stored procedure

Set CommandType to StoredProcedure and bind parameters with PolarDBParameter. Error codes 01403 (no data found) and 01422 (multiple rows returned) are handled in the catch block.

// Stored procedure call — query employee details
try
{
    PolarDBCommand callCmd = new PolarDBCommand(
        "emp_query(:p_deptno,:p_empno,:p_ename,:p_job,:p_hiredate,:p_sal)", conn);
    callCmd.CommandType = CommandType.StoredProcedure;

    callCmd.Parameters.Add(new PolarDBParameter("p_deptno",  PolarDBTypes.PolarDBDbType.Numeric, 10, "p_deptno",  ParameterDirection.Input,       false, 2, 2, System.Data.DataRowVersion.Current, 20));
    callCmd.Parameters.Add(new PolarDBParameter("p_empno",   PolarDBTypes.PolarDBDbType.Numeric, 10, "p_empno",   ParameterDirection.InputOutput,  false, 2, 2, System.Data.DataRowVersion.Current, 7369));
    callCmd.Parameters.Add(new PolarDBParameter("p_ename",   PolarDBTypes.PolarDBDbType.Varchar, 10, "p_ename",   ParameterDirection.InputOutput,  false, 2, 2, System.Data.DataRowVersion.Current, "SMITH"));
    callCmd.Parameters.Add(new PolarDBParameter("p_job",     PolarDBTypes.PolarDBDbType.Varchar, 10, "p_job",     ParameterDirection.Output,       false, 2, 2, System.Data.DataRowVersion.Current, null));
    callCmd.Parameters.Add(new PolarDBParameter("p_hiredate",PolarDBTypes.PolarDBDbType.Date,   200, "p_hiredate",ParameterDirection.Output,       false, 2, 2, System.Data.DataRowVersion.Current, null));
    callCmd.Parameters.Add(new PolarDBParameter("p_sal",     PolarDBTypes.PolarDBDbType.Numeric,200, "p_sal",     ParameterDirection.Output,       false, 2, 2, System.Data.DataRowVersion.Current, null));

    callCmd.Prepare();
    callCmd.Parameters[0].Value = 20;
    callCmd.Parameters[1].Value = 7369;

    PolarDBDataReader result = callCmd.ExecuteReader();
    int fc = result.FieldCount;
    for (int i = 0; i < fc; i++)
        Console.WriteLine("RESULT[" + i + "]=" + Convert.ToString(callCmd.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());
    }
}

Note: If you are using the .NET 2.0 driver, modify the catch block as needed.

Use prepared statements

Parameterized queries protect against SQL injection and improve performance through statement reuse. Use named parameters (:Name, :ID) with PolarDBParameter.

// Prepared statement — update employee name by ID
string updateQuery = "update emp set ename = :Name where empno = :ID";
PolarDBCommand preparedCmd = new PolarDBCommand(updateQuery, conn);
preparedCmd.CommandType = CommandType.Text;

preparedCmd.Parameters.Add(new PolarDBParameter("ID",   PolarDBTypes.PolarDBDbType.Integer));
preparedCmd.Parameters.Add(new PolarDBParameter("Name", PolarDBTypes.PolarDBDbType.Text));
preparedCmd.Prepare();

preparedCmd.Parameters[0].Value = 7369;
preparedCmd.Parameters[1].Value = "Mark";
preparedCmd.ExecuteNonQuery();
Console.WriteLine("Record Updated...");

Basic connection

Connection pool

Session and application settings