PolarDB:.NET

Kueri data

Gunakan PolarDBCommand dan PolarDBDataReader untuk menjalankan pernyataan SELECT dan membaca setiap baris.

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

                // Kueri — baca catatan karyawan
                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();
            }
        }
    }
}

Memasukkan data

Gunakan ExecuteScalar() untuk menjalankan pernyataan INSERT.

// Insert — tambahkan baris karyawan baru
PolarDBCommand insertCmd = new PolarDBCommand(
    "INSERT INTO EMP(EMPNO,ENAME) VALUES((SELECT COUNT(EMPNO) FROM EMP),'JACKSON')", conn);
insertCmd.ExecuteScalar();
Console.WriteLine("Record inserted");

Memperbarui data

Gunakan ExecuteNonQuery() untuk menjalankan pernyataan UPDATE.

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

Menghapus data

Gunakan ExecuteScalar() untuk menjalankan pernyataan DELETE.

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

Panggil prosedur tersimpan

Atur CommandType ke StoredProcedure dan bind parameter dengan PolarDBParameter. Kode error 01403 (data tidak ditemukan) dan 01422 (beberapa baris dikembalikan) ditangani dalam blok catch.

// Panggilan prosedur tersimpan — kueri detail karyawan
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());
    }
}

Catatan: Jika Anda menggunakan driver .NET 2.0, sesuaikan blok catch sesuai kebutuhan.

Gunakan prepared statement

Kueri berparameter melindungi dari SQL injection dan meningkatkan performa melalui penggunaan ulang pernyataan. Gunakan parameter bernama (:Name, :ID) dengan PolarDBParameter.

// Prepared statement — perbarui nama karyawan berdasarkan 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...");

Koneksi dasar

Kolam koneksi

Pengaturan sesi dan aplikasi