public static bool TesMySqlConnection(string server, string database, string username, string password, out string pesan)
{
bool isConnect = false;
pesan = string.Empty;
string errMsg = string.Empty;
try
{
POProject.CommandAdapter.MySqlCmdBuilder cmd = new POProject.CommandAdapter.MySqlCmdBuilder(server, database, username, password);
if (cmd.TestConnection(out errMsg))
{
pesan = "Koneksi Berhasil";
isConnect = true;
}
else
{
pesan = "Koneksi Gagal : " + errMsg;
}
}
catch (Exception ex)
{
pesan = "Koneksi Gagal : " + ex.Message;
}
return(isConnect);
}
public static void MySqlDataParser(DataTable dtSource, string queryPajak, string queryLampiran, DateTime LastErrDate, DateTime Serverdate, out DataTable dtPajak, out DataTable dtLampiran)
{
string mySqlServer = dtSource.Select("NAME='tbMySqlServer'").FirstOrDefault().Field <string>("VALUE");
string mySqlDatabase = dtSource.Select("NAME='tbMySqlDatabase'").FirstOrDefault().Field <string>("VALUE");
string mySqlUserId = dtSource.Select("NAME='tbMySqlUsername'").FirstOrDefault().Field <string>("VALUE");
string mySqlPassword = dtSource.Select("NAME='tbMySqlPassword'").FirstOrDefault().Field <string>("VALUE");
//get pajak
POProject.CommandAdapter.MySqlCmdBuilder cmdMySql = new POProject.CommandAdapter.MySqlCmdBuilder(mySqlServer, mySqlDatabase, mySqlUserId, mySqlPassword);
//cmdMySql.Query = "SELECT * FROM (" + queryPajak + ") al WHERE DATE(TGL_TRANSAKSI) BETWEEN ?tglAwal AND ?tglAkhir ";
cmdMySql.Query = queryPajak;
cmdMySql.AddParameter("tglAwal", ParameterDirection.Input, LastErrDate.ToString("yyyy-MM-dd"));
cmdMySql.AddParameter("tglAkhir", ParameterDirection.Input, Serverdate.ToString("yyyy-MM-dd"));
dtPajak = cmdMySql.GetTable();
//get detail
cmdMySql = new POProject.CommandAdapter.MySqlCmdBuilder(mySqlServer, mySqlDatabase, mySqlUserId, mySqlPassword);
//cmdMySql.Query = "SELECT * FROM (" + queryLampiran + ") al WHERE DATE(" + colTglName + ") BETWEEN ?tglAwal AND ?tglAkhir ";
cmdMySql.Query = queryLampiran;
cmdMySql.AddParameter("tglAwal", ParameterDirection.Input, LastErrDate.ToString("yyyy-MM-dd"));
cmdMySql.AddParameter("tglAkhir", ParameterDirection.Input, Serverdate.ToString("yyyy-MM-dd"));
dtLampiran = cmdMySql.GetTable();
}
public static void CekValidQuery(string query, string DBConnector, Dictionary <string, string> dictDbConnector, out DataTable dt)
{
dt = new DataTable();
switch (DBConnector)
{
case "ORACLE":
POProject.CommandAdapter.OracleCmdBuilder cmdOra = new POProject.CommandAdapter.OracleCmdBuilder(dictDbConnector["tbOracleDataSource"].ToString(),
dictDbConnector["tbOracleUsername"].ToString(), dictDbConnector["tbOraclePassword"].ToString());
cmdOra.Query = query;
if (query.Contains("tglAwal") && query.Contains("tglAkhir"))
{
cmdOra.AddParameter("tglAwal", POProject.CommandAdapter.OracleCmdParameterDirection.Input, DateTime.Now.ToString("dd-MMM-yyyy", new CultureInfo("en-US")));
cmdOra.AddParameter("tglAkhir", POProject.CommandAdapter.OracleCmdParameterDirection.Input, DateTime.Now.ToString("dd-MMM-yyyy", new CultureInfo("en-US")));
}
dt = cmdOra.GetTable();
break;
case "SQL":
POProject.CommandAdapter.SqlCmdBuilder cmdSql = new POProject.CommandAdapter.SqlCmdBuilder(dictDbConnector["tbSqlServer"].ToString(), dictDbConnector["tbSqlDatabase"].ToString(),
dictDbConnector["tbSqlUsername"].ToString(), dictDbConnector["tbSqlPassword"].ToString());
cmdSql.Query = query;
if (query.Contains("tglAwal") && query.Contains("tglAkhir"))
{
cmdSql.AddParameter("tglAwal", ParameterDirection.Input, DateTime.Now.ToString("yyyy-MM-dd"));
cmdSql.AddParameter("tglAkhir", ParameterDirection.Input, DateTime.Now.ToString("yyyy-MM-dd"));
}
dt = cmdSql.GetTable();
break;
case "MYSQL":
POProject.CommandAdapter.MySqlCmdBuilder cmdMySql = new POProject.CommandAdapter.MySqlCmdBuilder(dictDbConnector["tbMySqlServer"].ToString(), dictDbConnector["tbMySqlDatabase"].ToString(),
dictDbConnector["tbMySqlUsername"].ToString(), dictDbConnector["tbMySqlPassword"].ToString());
cmdMySql.Query = query;
if (query.Contains("tglAwal") && query.Contains("tglAkhir"))
{
cmdMySql.AddParameter("tglAwal", ParameterDirection.Input, DateTime.Now.ToString("yyyy-MM-dd"));
cmdMySql.AddParameter("tglAkhir", ParameterDirection.Input, DateTime.Now.ToString("yyyy-MM-dd"));
}
dt = cmdMySql.GetTable();
break;
case "ACCESS":
POProject.CommandAdapter.MsAccessCmdBuilder cmdAccess = new POProject.CommandAdapter.MsAccessCmdBuilder(dictDbConnector["tbAccessPath"].ToString(), dictDbConnector["tbAccessPassword"].ToString());
cmdAccess.Query = query;
if (query.Contains("tglAwal") && query.Contains("tglAkhir"))
{
cmdAccess.AddParameter("@tglAwal", ParameterDirection.Input, DateTime.Now.ToString("yyyy-MM-dd"));
cmdAccess.AddParameter("@tglAkhir", ParameterDirection.Input, DateTime.Now.ToString("yyyy-MM-dd"));
}
dt = cmdAccess.GetTable();
break;
default:
break;
}
}