- สร้างโฟลเดอร์ ifx
- สร้างคลาส ifx/sql.cs
- สร้างคลาส ifx/qry.cs
- แก้ไขไฟล์ App.config
- แก้ไขไฟล์ 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
}
