Console เชื่อมต่อ Informix

  1. สร้างโฟลเดอร์ ifx
  2. สร้างคลาส ifx/sql.cs
  3. สร้างคลาส ifx/qry.cs
  4. แก้ไขไฟล์ App.config
  5. แก้ไขไฟล์ Program.cs

1.สร้างโฟลเดอร์ ifx

2.สร้างคลาส ifx/sql.cs

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

namespace ConsoleApp1.ifx
{
    public class sql
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
        private static string connIfx = ConfigurationManager.AppSettings.Get("connIfx");


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

            try
            {
                IfxConnection conn = new IfxConnection(connIfx);
                conn.Open();

                IfxDataAdapter da = new IfxDataAdapter(query, conn);
                System.Data.DataSet ds = new System.Data.DataSet("Names");

                da.Fill(ds);

                DataTable dt = ds.Tables[0];

                conn.Close();

                log.Debug(string.Format("  found {0} record.", dt.Rows.Count));
                return dt;
            }
            catch (IfxException ex)
            {
                log.Error(ex.Message);
                return null;
            }
        }

        public static DataTable ExecuteReader(string query)
        {
            query = Regex.Replace(query, @"\s+", " ");
            log.Debug(query);

            DataSet ds;
            try
            {
                using (IfxConnection conn = new IfxConnection(connIfx))
                {
                    conn.Open();
                    using (IfxTransaction trans = conn.BeginTransaction(IsolationLevel.ReadUncommitted))
                    {
                        using (IfxCommand cmd = new IfxCommand(query, conn))
                        {
                            cmd.Transaction = trans;
                            cmd.CommandTimeout = 5000;

                            using (IfxDataReader dr = cmd.ExecuteReader())
                            {
                                // Write the data to the console
                                //while (dr.Read())
                                //{
                                //    Console.WriteLine(dr["xxx"].ToString());
                                //}
                                ds = new System.Data.DataSet("Names");
                                while (!dr.IsClosed)
                                    ds.Tables.Add().Load(dr);
                                dr.Close();

                                DataTable dt = ds.Tables[0];

                                conn.Close();

                                log.Debug(string.Format("  found {0} record.", dt.Rows.Count));
                                return dt;
                            }
                        }
                    }
                }
            }
            catch (IfxException ex)
            {
                log.Error(ex.Message);
                return null;
            }
        }

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

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

                    using (IfxTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                    {
                        using (IfxCommand cmd = new IfxCommand())
                        {
                            cmd.Connection = conn;
                            cmd.Transaction = trans;
                            cmd.CommandTimeout = 5000;

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

                                trans.Commit();
                                result = "success";
                                log.Info(result);
                            }
                            catch (Exception ex)
                            {
                                trans.Rollback();
                                result = ex.Message;
                                log.Error(ex.Message);
                            }
                        }
                    }
                }
                return result;
            }
            catch (IfxException ex)
            {
                log.Error(ex.Message);
                return null;
            }
        }

        public static string ExecuteNonQuery(string[] arrQuery)
        {
            try
            {
                string result = "";
                using (var conn = new IfxConnection(connIfx))
                {
                    conn.Open();

                    using (IfxTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                    {
                        using (IfxCommand cmd = new IfxCommand())
                        {
                            cmd.Connection = conn;
                            cmd.Transaction = trans;
                            cmd.CommandTimeout = 5000;

                            try
                            {
                                for (int i = 0; i < arrQuery.Length; i++)
                                {
                                    string query = arrQuery[i];
                                    query = Regex.Replace(query, @"\s+", " ");
                                    log.Debug(query);

                                    cmd.CommandText = query;
                                    cmd.ExecuteNonQuery();
                                }

                                trans.Commit();
                                result = "success";
                                log.Info(result);
                            }
                            catch (Exception ex)
                            {
                                trans.Rollback();
                                result = ex.Message;
                                log.Error(ex.Message);
                            }
                        }
                    }
                }
                return result;
            }
            catch (IfxException ex)
            {
                log.Error(ex.Message);
                return null;
            }
        }

    } // end class
}

3.สร้างคลาส ifx/qry.cs

using System.Reflection;

namespace ConsoleApp1.ifx
{
    public class qry
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        public static string QuerySelectData(string param1)
        {
            log.Info(string.Format("Parameter ({0})", param1));

            string query = string.Format(@"", param1);
            return query;
        }

        public static string QuerySelectData(string param1, string param2)
        {
            log.Info(string.Format("Parameter ({0}, {1})", param1, param2));

            string query = string.Format(@"", param1, param2);
            return query;
        }

    } // end class
}

4.แก้ไขไฟล์ App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>

  <appSettings>
    <add key="connIfx"   value="Host=xxx;Database=..." />
  </appSettings>

</configuration>

5.แก้ไขไฟล์ Program.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp1
{
    class Program
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        static void Main(string[] args)
        {
            try
            {
                log.Info("*********************************");
                log.Info("************* BEGIN *************");
                log.Info(AppDomain.CurrentDomain.FriendlyName);


                // Check AppSettings in App.config
                if (!checkConfig())
                {
                    log.Error("Missing some config");
                    return;
                }


                string connIfx = ConfigurationManager.AppSettings.Get("connIfx");
                log.Info(string.Format("  '{0}'", connIfx.Split(';')[0]));

                ifx.sql.ExecuteNonQuery("set isolation to dirty read;");


                log.Info("************** END **************");
                log.Info("");
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                log.Error(ex.ToString());
            }
        } // end main

        private static bool checkConfig()
        {
            string connIfx = ConfigurationManager.AppSettings.Get("connIfx");
            if (string.IsNullOrEmpty(connIfx))
            {
                log.Error("'connIfx' not found in App.config");
                return false;
            }

            return true;
        }
    } // end class
}