/// <summary>
/// คืนค่าจากคำสั่ง SQLQuery เป็น DataTable
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public DataTable Bind(string strSQL)
{
#region Variable
var csSQL = getConnectionString(cs);
var dt = new DataTable();
#endregion
#region Procedure
if (!string.IsNullOrEmpty(csSQL))
{
if (dbType == DBType.SQLServer)
{
#region SQLServer
using (var myConn_SQL = new SqlConnection(csSQL))
using (var myDa_SQL = new SqlDataAdapter(QueryFilterByDatabaseType(strSQL), myConn_SQL))
{
if (myConn_SQL.IsConnected())
{
myDa_SQL.SelectCommand.CommandTimeout = 0;
myDa_SQL.Fill(dt);
myConn_SQL.Dispose();
myDa_SQL.Dispose();
if (dt.Rows.Count > 0 && dt != null)
{
return dt;
}
else
{
dt.Dispose();
return null;
}
}
else
{
return null;
}
}
#endregion
}
else if (dbType == DBType.ODBC)
{
#region ODBC
using (var myConn_ODBC = new OdbcConnection(csSQL))
using (var myDa_ODBC = new OdbcDataAdapter(QueryFilterByDatabaseType(strSQL), myConn_ODBC))
{
myDa_ODBC.Fill(dt);
myConn_ODBC.Dispose();
myDa_ODBC.Dispose();
if (dt.Rows.Count > 0 && dt != null)
{
return dt;
}
else
{
dt.Dispose();
return null;
}
}
#endregion
}
else if (dbType == DBType.MySQL)
{
#region MySQL
using(var myConn_MySQL = new MySql.Data.MySqlClient.MySqlConnection(csSQL))
using (var myDa_MySQL = new MySql.Data.MySqlClient.MySqlDataAdapter(QueryFilterByDatabaseType(strSQL), myConn_MySQL))
{
if (myConn_MySQL.IsConnected())
{
myDa_MySQL.SelectCommand.CommandTimeout = 0;
myDa_MySQL.Fill(dt);
myConn_MySQL.Dispose();
myDa_MySQL.Dispose();
if (dt.Rows.Count > 0 && dt != null)
{
return dt;
}
else
{
dt.Dispose();
return null;
}
}
else
{
return null;
}
}
#endregion
}
else
{
return null;
}
}
else
{
return null;
}
#endregion
}
/// <summary>
/// Execute คำสั่ง SQL ที่ใช้ในการบันทึกข้อมูล โดยสามารถระบุ SQL Parameter ได้
/// </summary>
/// <param name="strSQL">SQL Query</param>
/// <param name="arrParameter">SQL Parameter (new string[,] { { "?ID", txtTest.Text } })</param>
/// <param name="strDBType">ชนิดของฐานข้อมูล เช่น sql,odbc,mysql</param>
/// <param name="appsetting_name">ชื่อตัวแปรที่เก็บ ConnectionString ในไฟล์ AppSetting</param>
/// <param name="outMessage">ข้อความ เมื่อเกิดข้อผิดพลาด</param>
/// <returns>True=รันสำเร็จ , False=รันไม่สำเร็จ</returns>
/// <example>
/// string outMessage;
/// clsSQL.Execute("UPDATE webboard_type SET type_name=?NAME WHERE type_id=?ID", new string[,] { { "?ID", txtTest.Text }, { "?NAME", "ใช้ Array 2 มิติ" } }, clsSQL.DBType.MySQL, "cs",out outMessage);
/// </example>
public bool Execute(string strSQL, string[,] arrParameter, out string outMessage)
{
#region Variable
var csSQL = getConnectionString(cs);
var result = false;
var i = 0;
outMessage = "";
#endregion
#region Procedure
if (!string.IsNullOrEmpty(csSQL) && arrParameter.Rank == 2)
{
if (dbType == DBType.SQLServer)
{
#region SQLServer
var myConn_SQL = new SqlConnection(csSQL);
if (myConn_SQL.IsConnected())
{
var myCmd_SQL = new SqlCommand(QueryFilterByDatabaseType(strSQL), myConn_SQL);
for (i = 0; i < arrParameter.Length / arrParameter.Rank; i++)
{
myCmd_SQL.Parameters.AddWithValue(arrParameter[i, 0], arrParameter[i, 1]);
}
try
{
myConn_SQL.Open();
myCmd_SQL.ExecuteNonQuery();
myConn_SQL.Close();
myCmd_SQL.Dispose();
result = true;
}
catch (Exception ex)
{
outMessage = ex.Message;
result = false;
}
finally
{
myCmd_SQL.Dispose();
myConn_SQL.Close();
}
}
#endregion
}
else if (dbType == DBType.ODBC)
{
#region ODBC
var myConn_ODBC = new OdbcConnection(csSQL);
var myCmd_ODBC = new OdbcCommand(QueryFilterByDatabaseType(strSQL), myConn_ODBC);
for (i = 0; i < arrParameter.Length / arrParameter.Rank; i++)
{
myCmd_ODBC.Parameters.AddWithValue(arrParameter[i, 0], arrParameter[i, 1]);
}
try
{
myConn_ODBC.Open();
myCmd_ODBC.ExecuteNonQuery();
myConn_ODBC.Close();
myCmd_ODBC.Dispose();
result = true;
}
catch (Exception ex)
{
outMessage = ex.Message;
result = false;
}
finally
{
myCmd_ODBC.Dispose();
myConn_ODBC.Close();
}
#endregion
}
else if (dbType == DBType.MySQL)
{
#region MySQL
var myConn_MySQL = new MySql.Data.MySqlClient.MySqlConnection(csSQL);
if (myConn_MySQL.IsConnected())
{
var myCmd_MySQL = new MySql.Data.MySqlClient.MySqlCommand(QueryFilterByDatabaseType(strSQL), myConn_MySQL);
for (i = 0; i < arrParameter.Length / arrParameter.Rank; i++)
{
myCmd_MySQL.Parameters.AddWithValue(arrParameter[i, 0], arrParameter[i, 1]);
}
try
{
myConn_MySQL.Open();
myCmd_MySQL.ExecuteNonQuery();
myConn_MySQL.Close();
myCmd_MySQL.Dispose();
result = true;
}
catch (Exception ex)
{
outMessage = ex.Message;
result = false;
}
finally
{
myCmd_MySQL.Dispose();
myConn_MySQL.Close();
}
}
#endregion
}
else
{
outMessage = "Not found DBType.";
result = false;
}
}
else
{
outMessage = "Not found AppSettingName.";
result = false;
}
return result;
#endregion
}
