public static bool TesSQLConnection(string server, string database, string username, string password, out string pesan) { bool isConnect = false; pesan = string.Empty; string errMsg = string.Empty; try { POProject.CommandAdapter.SqlCmdBuilder cmd = new POProject.CommandAdapter.SqlCmdBuilder(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 SQLDataParser(DataTable dtSource, string queryPajak, string queryLampiran, DateTime LastErrDate, DateTime ServerDate, out DataTable dtPajak, out DataTable dtLampiran) { string SqlServer = dtSource.Select("NAME='tbSqlServer'").FirstOrDefault().Field <string>("VALUE"); string SqlDatabase = dtSource.Select("NAME='tbSqlDatabase'").FirstOrDefault().Field <string>("VALUE"); string SqlUserId = dtSource.Select("NAME='tbSqlUsername'").FirstOrDefault().Field <string>("VALUE"); string SqlPassword = dtSource.Select("NAME='tbSqlPassword'").FirstOrDefault().Field <string>("VALUE"); //get Pajak POProject.CommandAdapter.SqlCmdBuilder cmdSql = new POProject.CommandAdapter.SqlCmdBuilder(SqlServer, SqlDatabase, SqlUserId, SqlPassword); //cmdSql.Query = "SELECT * FROM (" + queryPajak + ") WHERE " + colTglName + " BETWEEN @tglAwal AND @tglAkhir "; cmdSql.Query = queryPajak; cmdSql.AddParameter("tglAwal", ParameterDirection.Input, LastErrDate.ToString("yyyy-MM-dd") + " 00:01:00"); cmdSql.AddParameter("tglAkhir", ParameterDirection.Input, ServerDate.ToString("yyyy-MM-dd") + " 23:59:59"); dtPajak = cmdSql.GetTable(); //get detail cmdSql = new POProject.CommandAdapter.SqlCmdBuilder(SqlServer, SqlDatabase, SqlUserId, SqlPassword); //cmdSql.Query = "SELECT * FROM (" + queryLampiran + ") WHERE " + colTglName + " BETWEEN @tglAwal AND @tglAkhir "; cmdSql.Query = queryLampiran; cmdSql.AddParameter("tglAwal", ParameterDirection.Input, LastErrDate.ToString("yyyy-MM-dd") + " 00:01:00"); cmdSql.AddParameter("tglAkhir", ParameterDirection.Input, ServerDate.ToString("yyyy-MM-dd") + " 23:59:59"); dtLampiran = cmdSql.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; } }