/// <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 }