1. ทดสอบการเชื่อมต่อ Informix
2. ExecuteScalar (เรียก Stored Procedure)
3. ExecuteReader
4. ExecuteQuery
5. ExecuteNonQuery

• IBM Informix .NET Provider examples
• Retrieve a single value
• Retrieve multiple rows
• Retrieve data into a DataSet
• Call a stored procedure

Program.cs

using IBM.Data.Informix;
using System;
using System.Data;
using System.Text.RegularExpressions;

namespace ConsoleApp
{
    class Program
    {
        private static string connectionString = "";

        static void Main(string[] args)
        {
            string result = TestConnection();
            Console.WriteLine(result);

            Decimal count = ExecuteScalar("SELECT COUNT(*) FROM tbTest");
            Console.WriteLine(count);

            DataTable dt1 = ExecuteReader("SELECT firstname FROM tbTest");
            Console.WriteLine("ExecuteReader " + dt1.Rows.Count);

            DataTable dt2 = ExecuteQuery("SELECT * FROM tbTest");
            Console.WriteLine("ExecuteQuery " + dt2.Rows.Count);
        }
    }
}

1.ทดสอบการเชื่อมต่อ Informix

ถ้าการเชื่อมต่อสำเร็จจะคืนข้อความ “Connect Success”

public static string TestConnection()
{
    using (var conn = new IfxConnection(connectionString))
    {
        try
        {
            conn.Open();
            return "Connect Success";
        }
        catch (IfxException ex)
        {
            return "Problem with connection attempt: " + ex.Message;
        }
    }
}

2.ExecuteScalar

public static Decimal ExecuteScalar(string query)
{
    try
    {
        // Open a connection
        IfxConnection conn = new IfxConnection(connectionString);
        conn.Open();

        // Create an SQL command
        IfxCommand cmd = new IfxCommand(query, conn);
        Decimal count = (Decimal)cmd.ExecuteScalar();
        //Console.WriteLine("There are " + count + " customers");

        // Close the connection
        conn.Close();

        return count;
    }
    catch (IfxException ex)
    {
        Console.WriteLine(ex.Message);
        return -1;
    }
}

เรียก Stored Procedure ก็ได้เหมือนกัน ใช้คำสั่งเดียวกัน แล้วส่ง query สำหรับรัน store

public static int ExecuteScalar(string query)
{
    try
    {
        // Open a connection
        IfxConnection conn = new IfxConnection(connectionString);
        conn.Open();

        // Create an SQL command
        IfxCommand cmd = new IfxCommand(query, conn);
        int result = (int)cmd.ExecuteScalar();

        // Close the connection
        conn.Close();

        log.Debug(string.Format("  result = '{0}'", result));
        return result;
    }
    catch (Exception ex)
    {
        log.Error(ex.Message);
        return -1;
    }
}

EXECUTE PROCEDURE TestStore('1234567890123', '04/03/2020', 100)

3.ExecuteReader

public static DataTable ExecuteReader(string query)
{
    DataSet ds;
    try
    {
        // Open a connection
        IfxConnection conn = new IfxConnection(connectionString);
        conn.Open();

        // Create an SQL command
        IfxCommand cmd = new IfxCommand(query, conn);
        cmd.CommandTimeout = 30; // set command timeout

        IfxDataReader dr = cmd.ExecuteReader();

        // Write the data to the console
        //while (dr.Read())
        //{
        //    Console.WriteLine(dr["col_name"].ToString());
        //}

        ds = new System.Data.DataSet("Names");
        while (!dr.IsClosed)
            ds.Tables.Add().Load(dr);

        DataTable dt = ds.Tables[0];

        // Close the connection
        conn.Close();

        log.Debug(string.Format("  found {0} record.", dt.Rows.Count));
        return dt;
    }
    catch (IfxException e)
    {
        Console.WriteLine(e.ToString());
        return null;
    }
}

4.ExecuteQuery

public static DataTable ExecuteQuery(string query)
{
    query = Regex.Replace(query, @"\s+", " ");
    Console.WriteLine(query);

    try
    {
        // Open a connection
        IfxConnection conn = new IfxConnection(connectionString);
        conn.Open();

        // Create an SQL command
        IfxDataAdapter da = new IfxDataAdapter(query, conn);
        System.Data.DataSet ds = new System.Data.DataSet("Names");

        //Fill the DataSet
        da.Fill(ds);

        DataTable dt = ds.Tables[0];

        // Close the connection
        conn.Close();

        Console.WriteLine("there is " + dt.Rows.Count + " record.");
        return dt;
    }
    catch (IfxException ex)
    {
        Console.WriteLine(ex.Message);
        return null;
    }
}

5.ExecuteNonQuery

ทำ ExecuteNonQuery แบบมี Transaction

public static string ExecuteNonQuery(string query)
{
    query = Regex.Replace(query, @"\s+", " ");
    Console.WriteLine(query);

    string result = "";
    using (var conn = new IfxConnection(connectionString))
    {
        conn.Open();

        IfxCommand cmd = new IfxCommand();
        IfxTransaction tran;

        // Start a local transaction
        tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
        // Assign transaction object for a pending local transaction
        cmd.Connection = conn;
        cmd.Transaction = tran;

        try
        {
            cmd.CommandText = query;
            cmd.ExecuteNonQuery();

            tran.Commit();
            result = "success";
        }
        catch (Exception ex)
        {
            tran.Rollback();
            result = ex.Message;
        }
        finally
        {
            conn.Close();
        }
    }

    return result;
}