public string GetData(string Xml, string AddParams, string Mode)
{
String retXml = string.Empty;
try
{
if (Mode == "S")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(PLABS.Utils.CnvDataSetToXml(objDbHelper.SelectSP("maaddrmast_SEL", Xml, 1, this._objDb), true));
}
else if (Mode == "I" || Mode == "U")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(objDbHelper.insertSP("maaddrmast_IU", Xml, this._objDb));
}
else if (Mode == "D")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(objDbHelper.insertSP("maaddrmast_DEL", Xml, this._objDb));
}
}
catch (Exception ex)
{
return(ex.Message);
}
return("");
}
public string GetData(string Xml, string AddParams, string Mode)
{
String retXml = string.Empty;
try
{
if (Mode == "S")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(PLABS.Utils.CnvDataSetToXml(objDbHelper.SelectSP("tr_slrypstng_sel", Xml, 3, this._objDb), true));
}
else if (Mode == "I" || Mode == "U")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(objDbHelper.insertSP("tr_slrypstng_iu", Xml, this._objDb));
}
else if (Mode == "L")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(PLABS.Utils.CnvDataSetToXml(objDbHelper.SelectSP("tr_leave_dtls_sel", Xml, 3, this._objDb), true));
}
}
catch (Exception ex)
{
return(ex.Message);
}
return("");
}
public string GetData(string Xml, string AddParams, string Mode)
{
String retXml = string.Empty;
try
{
if (Mode == "S")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(PLABS.Utils.CnvDataSetToXml(objDbHelper.SelectSP("get_pl_capital", Xml, 1, this._objDb), true));
}
else if (Mode == "I" || Mode == "U")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(objDbHelper.insertSP("rpt_total_capital_iu", Xml, this._objDb));
}
//else if (Mode == "D")
//{
// PLABS.DAL objDbHelper = new PLABS.DAL();
// return objDbHelper.insertSP("gefrmmast_DEL", Xml, this._objDb);
//}
}
catch (Exception ex)
{
return(ex.Message);
}
return("");
}
public string GetData(string Xml, string AddParams, string Mode)
{
String retXml = string.Empty;
try
{
if (Mode == "S")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(PLABS.Utils.CnvDataSetToXml(objDbHelper.SelectSP("ge_bckups_sel", Xml, 1, this._objDb), true));
}
else if (Mode == "I" || Mode == "U")
{
}
else if (Mode == "R")
{
return(this.Restore(Xml));
}
else if (Mode == "D")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(objDbHelper.insertSP("ge_bckups_del", Xml, this._objDb));
}
}
catch (Exception ex)
{
return(ex.Message);
}
return("");
}
public string GetData(string Xml, string AddParams, string Mode)
{
String retXml = string.Empty;
try
{
if (Mode == "S")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(PLABS.Utils.CnvDataSetToXml(objDbHelper.SelectSP("trsmthissue_sel", Xml, 2, this._objDb), true));
}
else if (Mode == "I" || Mode == "U")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(objDbHelper.insertSP("trsmthissue_iu", Xml, this._objDb));
}
else if (Mode == "D")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(objDbHelper.insertSP("trsmthissue_del", Xml, this._objDb));
}
else
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(PLABS.Utils.CnvDataSetToXml(objDbHelper.SelectSP("rptsmithissue_sel", Xml, 2, this._objDb), true));
}
}
catch (Exception ex)
{
return(ex.Message);
}
return(retXml);
}
public string GetData(string Xml, string AddParams, string Mode)
{
String retXml = string.Empty;
try
{
if (Mode == "S")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(PLABS.Utils.CnvDataSetToXml(objDbHelper.SelectSP("dbgldregister_sel", Xml, 1, this._objDb), true));
}
else if (Mode == "I" || Mode == "U")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(objDbHelper.insertSP("dbgldregister_iu", Xml, this._objDb));
}
else if (Mode == "D")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(objDbHelper.insertSP("dbgldregister_del", Xml, this._objDb));
}
else if (Mode == "F")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(objDbHelper.insertSP("pr_opnfrwd", Xml, this._objDb));
}
else if (Mode == "LA")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(PLABS.Utils.CnvDataSetToXml(objDbHelper.SelectSP("tr_slrypstng_sel", Xml, 1, this._objDb), true));
}
else if (Mode == "LU")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(objDbHelper.insertSP("tr_slrypstng_iu", Xml, this._objDb));
}
else if (Mode == "PI")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(objDbHelper.insertSP("ma_macaprvl_iu", Xml, this._objDb));
}
else if (Mode == "PS")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(PLABS.Utils.CnvDataSetToXml(objDbHelper.SelectSP("ma_macaprvl_sel", Xml, 1, this._objDb), true));
}
else if (Mode == "PD")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(objDbHelper.insertSP("gemnumast_DEL", Xml, this._objDb));
}
}
catch (Exception ex)
{
return(ex.Message);
}
return("");
}
public string GetCreateTableSql(string table)
{
String xmlData = this.CreateXml("A", table, "", "", "", "");
String Data = string.Empty;
PLABS.DAL objDbHelper = new PLABS.DAL();
Data = PLABS.Utils.CnvDataSetToXml(objDbHelper.SelectSP("BKP_PRO", xmlData, 1, this._objDb), true);
DataSet ds = PLABS.Utils.CnvXMLToDataSet(Data);
DataTable dt = PLABS.Utils.GetDataTable(ds, 0);
return(dt.Rows[0][1].ToString().Replace("CREATE TABLE", "CREATE TABLE") + ";");
}
public string GetData(string Xml, string AddParams, string mode)
{
String retXml = string.Empty;
try
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(PLABS.Utils.CnvDataSetToXml(objDbHelper.SelectSP("loadmenu", Xml, 1, this._objDb), true));
}
catch (Exception ex)
{
return(ex.Message);
}
return("");
}
public string GetData(string Xml, string AddParams, string Mode)
{
String retXml = string.Empty;
try
{
if (Mode == "S")
{
PLABS.DAL objDbHelper = new PLABS.DAL();
return(PLABS.Utils.CnvDataSetToXml(objDbHelper.SelectSP("dbcshbkpndng_sel", Xml, 1, this._objDb), true));
}
}
catch (Exception ex)
{
return(ex.Message);
}
return("");
}
private string Restore(string rstrId)
{
string restoreFile = string.Empty;
int status = 0;
PLABS.DAL objDbHelper = new PLABS.DAL();
String xmlData = this.CreateXml("RD", "", "", "", rstrId, "");
System.Data.DataSet ds = objDbHelper.SelectSP("BKP_PRO", xmlData, 1, this._objDb);
if (ds.Tables.Count != 0 && ds.Tables[0].Rows.Count > 0)
{
restoreFile = PLABS.Utils.Decompress(PLABS.Utils.CnvToStr(PLABS.Utils.GetDataTable(ds, 0).Rows[0]["dump"]));
// restoreFile = PLABS.Utils.CnvToStr(PLABS.Utils.GetDataTable(ds, 0).Rows[0]["dump"]);
}
MySqlConnection connection = new MySqlConnection();
connection.ConnectionString = "server=173.248.142.18; User Id=baburajps;password=babups@501;Persist Security Info=True;database = baburaj";
connection.Open();
MySqlScript script = new MySqlScript(connection, restoreFile);
try
{
status = script.Execute();
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
connection.Close();
connection.Dispose();
}
return("1");
}
private String GetTables(String DatabaseName, String BackupName)
{
try
{
String xmlData = this.CreateXml("S", "", "", "", "", "");
String Data = string.Empty;
PLABS.DAL objDbHelper = new PLABS.DAL();
Data = PLABS.Utils.CnvDataSetToXml(objDbHelper.SelectSP("BKP_PRO", xmlData, 1, _objDb), true);
DataSet ds = PLABS.Utils.CnvXMLToDataSet(Data);
DataTable dt = PLABS.Utils.GetDataTable(ds, 0);
return(this.Backup(dt, DatabaseName, BackupName));
}
catch (Exception ex)
{
return(ex.ToString());
}
return("");
}
private string GetTableNames()
{
try
{
StringBuilder Xml = new StringBuilder();
Xml.Append("<root>");
Xml.Append("<dtls><arg>as_mode</arg><val>T</val></dtls>");
Xml.Append("<dtls><arg>ai_bckup_id</arg><val>1</val></dtls>");
Xml.Append("</root>");
PLABS.DAL objDbHelper = new PLABS.DAL();
System.Data.DataSet ds = objDbHelper.SelectSP("ge_bckups_sel", Xml.ToString(), 1, this._objDb);
if (ds.Tables.Count != 0 && ds.Tables[0].Rows.Count > 0)
{
return(PLABS.Utils.CnvToStr(PLABS.Utils.GetDataTable(ds, 0).Rows[0]["nam"]));
}
return("");
}
catch (Exception ex)
{
}
return("");
}
private string Restore(string rstrId)
{
try
{
StringBuilder Xml = new StringBuilder();
Xml.Append("<root>");
Xml.Append("<dtls><arg>as_mode</arg><val>RD</val></dtls>");
Xml.Append("<dtls><arg>ai_bckup_id</arg><val>" + rstrId + "</val></dtls>");
Xml.Append("</root>");
PLABS.DAL objDbHelper = new PLABS.DAL();
System.Data.DataSet ds = objDbHelper.SelectSP("ge_bckups_sel", Xml.ToString(), 1, this._objDb);
if (ds.Tables.Count != 0 && ds.Tables[0].Rows.Count > 0)
{
string restoreFile = PLABS.Utils.Decompress(PLABS.Utils.CnvToStr(PLABS.Utils.GetDataTable(ds, 0).Rows[0]["dump"]));
string mydocpath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
using (StreamWriter outfile = new StreamWriter(mydocpath + @"\AllTxtFiles.txt"))
{
outfile.Write(restoreFile);
}
string temp = PLABS.CreateProcessXml.FormatString(restoreFile);
Xml = new StringBuilder();
Xml.Append("<root>");
Xml.Append("<dtls><arg>as_mode</arg><val>R</val></dtls>");
Xml.Append("<dtls><arg>as_TblNam</arg><val></val></dtls>");
Xml.Append("<dtls><arg>as_Restore_data</arg><val>" + temp + "</val></dtls>");
Xml.Append("</root>");
String status = objDbHelper.insertSP("BKP_PRO", Xml.ToString(), this._objDb);
return(status);
}
}
catch (Exception ex)
{
return(ex.ToString());
}
return("");
}
void btn_drop_Click(object sender, EventArgs e)
{
PLABS.DAL objdal = new PLABS.DAL();
objdal.insertSP("dropdb", this.getBlankXml(), PLABS.DataBase.MySql);
}
public String Backup(DataTable dt, String DatabaseName, String BackupName)
{
try
{
dicTableCustomSql = GetTablesAndSQL(dt);
// saltSize = methods.GetSaltSize(EncryptKey);
#region Document Header
//BACKUPSTRING.Append("-- MySQL Administrator dump 1.4");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("-- Dump time: " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("-- ------------------------------------------------------");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("-- Server version 5.1.63-community");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("");
//BACKUPSTRING.Append("");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("/*!40101 SET NAMES utf8 */;");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("");
//BACKUPSTRING.Append("/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("");
//BACKUPSTRING.Append("");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("--");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("-- Create schema " + DatabaseName);
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("--");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("");
BACKUPSTRING.Append("\n");
BACKUPSTRING.Append("CREATE DATABASE IF NOT EXISTS " + DatabaseName + ";");
BACKUPSTRING.Append("\n");
BACKUPSTRING.Append("USE " + DatabaseName + ";");
BACKUPSTRING.Append("\n");
BACKUPSTRING.Append("SET FOREIGN_KEY_CHECKS=0;");
#endregion
foreach (KeyValuePair <string, string> kv in dicTableCustomSql)
{
string table = kv.Key;
#region Table Header
if (BackupTableStructure)
{
BACKUPSTRING.Append("");
BACKUPSTRING.Append("");
BACKUPSTRING.Append("\n");
BACKUPSTRING.Append("-- ");
BACKUPSTRING.Append("\n");
BACKUPSTRING.Append("-- Definition of table `" + table + "` -- ;");
BACKUPSTRING.Append("\n");
BACKUPSTRING.Append("\n");
BACKUPSTRING.Append("\n");
BACKUPSTRING.Append("");
BACKUPSTRING.Append(" DROP TABLE IF EXISTS `" + table + "`;");
BACKUPSTRING.Append("\n");
BACKUPSTRING.Append(GetCreateTableSql(table));
}
if (BackupRows)
{
BACKUPSTRING.Append("");
BACKUPSTRING.Append("\n");
BACKUPSTRING.Append("-- ");
BACKUPSTRING.Append("\n");
BACKUPSTRING.Append("-- Dumping data for table `" + table + "`");
BACKUPSTRING.Append("\n");
BACKUPSTRING.Append("--");
BACKUPSTRING.Append("\n");
BACKUPSTRING.Append("");
BACKUPSTRING.Append("\n");
BACKUPSTRING.Append("/*!40000 ALTER TABLE `" + table + "` DISABLE KEYS */;");
BACKUPSTRING.Append("\n");
}
#endregion
if (BackupRows)
{
BackupTable(table);
}
#region Table Footer
if (BackupRows)
{
BACKUPSTRING.Append("/*!40000 ALTER TABLE `" + table + "` ENABLE KEYS */;");
BACKUPSTRING.Append("\n");
}
#endregion
}
#region Document Footer
BACKUPSTRING.Append("SET FOREIGN_KEY_CHECKS=1;");
//BACKUPSTRING.Append("");
//BACKUPSTRING.Append("");
//BACKUPSTRING.Append("");
//BACKUPSTRING.Append("");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;");
//BACKUPSTRING.Append("\n");
//BACKUPSTRING.Append("/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;");
#endregion
#region Triggers
String xml = this.CreateXml("T", "", "", "", "", "");
String Data = string.Empty;
PLABS.DAL objDbHelper = new PLABS.DAL();
Data = PLABS.Utils.CnvDataSetToXml(objDbHelper.SelectSP("BKP_PRO", xml, 1, _objDb), true);
DataSet ds = PLABS.Utils.CnvXMLToDataSet(Data);
DataTable triggers = PLABS.Utils.GetDataTable(ds, 0);
for (int i = 0; i < triggers.Rows.Count; i++)
{
BACKUPSTRING.Append("\n");
BACKUPSTRING.AppendLine(" DROP TRIGGER /*!50032 IF EXISTS */ " + "`" + triggers.Rows[i]["db"] + "`" + "." + "`" + triggers.Rows[i]["nam"] + "`" + ";");
BACKUPSTRING.AppendLine("");
BACKUPSTRING.AppendLine("DELIMITER $$");
BACKUPSTRING.AppendLine("");
BACKUPSTRING.AppendLine(" CREATE ");
BACKUPSTRING.AppendLine(" TRIGGER " + " `" + triggers.Rows[i]["nam"] + "` " + triggers.Rows[i]["evnt"] + " " + triggers.Rows[i]["event_action"] + " ON " + " " + "`" + triggers.Rows[i]["tble"] + "`");
BACKUPSTRING.AppendLine(" FOR EACH " + triggers.Rows[i]["orint"]);
BACKUPSTRING.AppendLine(triggers.Rows[i]["statmnt"] + ";");
BACKUPSTRING.AppendLine(" $$ ");
BACKUPSTRING.AppendLine("DELIMITER ; ");
}
#endregion
String xmlData = this.CreateXml("I", "", BackupName, PLABS.Utils.Compress(BACKUPSTRING.ToString()), "", "");
string status = objDbHelper.insertSP("BKP_PRO", xmlData, this._objDb);
return(status);
}
catch (Exception ex)
{
return(ex.Message);
}
}
private void BackupTable(string table)
{
string InsertStatementHeader = null;
int W = 0;
String xmlData = this.CreateXml("B", table, "", "", "", "");
String Data = string.Empty;
PLABS.DAL objDbHelper = new PLABS.DAL();
DataSet ds = objDbHelper.SelectSP("BKP_PRO", xmlData, 1, this._objDb);
DataTable dt = PLABS.Utils.GetDataTable(ds, 0);
StringBuilder sb = new StringBuilder();
long rowchek = 0;
while (W < dt.Rows.Count)
{
if (InsertStatementHeader == null)
{
int fc = dt.Columns.Count;
string[] ColumnNames = new string[fc];
for (int ci = 0; ci < dt.Columns.Count; ci++)
{
ColumnNames[ci] = dt.Columns[ci].ToString();
}
InsertStatementHeader = GetInsertStatementHeader(table, ColumnNames);
}
object[] objectArray = new object[dt.Columns.Count];
for (int i = 0; i < dt.Columns.Count; i++)
{
if (dt.Rows[W][i] == System.DBNull.Value)
{
objectArray[i] = System.DBNull.Value;
}
else
{
Type tp = dt.Rows[W][i].GetType();
if (tp == typeof(DateTime))
{
objectArray[i] = PLABS.Utils.CnvToDate(dt.Rows[W][i]).ToString("yyyy-MM-dd hh:mm:ss");
}
else
{
objectArray[i] = dt.Rows[W][i].ToString();
}
}
}
string ValueString = GetSqlValueString(objectArray);
if (sb.Length == 0 || rowchek == 950)
{
if (rowchek == 950)
{
sb.Append("\n" + InsertStatementHeader + "\r\n" + ValueString);
rowchek = 0;
}
else
{
sb.Append(InsertStatementHeader + "\r\n" + ValueString);
}
}
else
{
if (rowchek == 949)
{
sb.Append(",\r\n" + ValueString + ";");
}
else
{
sb.Append(",\r\n" + ValueString);
}
}
//else if (((long)sb.Length + ValueString.Length + 10) < max_allowed_packet)
//{
//}
rowchek = rowchek + 1;
W++;
}
if (sb.Length > 0 || rowchek != 1)
{
sb.Append(";");
}
BACKUPSTRING.Append(sb.ToString());
}