public ActionResult LoadTableDetailDes(string tableName)
{
obj = objFunc.GetDbSessionDes();
objDa = new CommonDA(obj);
string sql = "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = '" + tableName + "' ORDER BY COLUMN_NAME";
DataTable dt = objDa.ExecuteSQL(sql, GlobalStruct.ActionType.ExecuteDataTable, ref errMsg).ToDataTable();
List <ColumnMapping> _lst = dt.ToListObject <ColumnMapping>();
string res = _lst.ToSerializer();
return(Json(res));
}
public ActionResult LoadDb()
{
obj = objFunc.GetDbSessionSrc();
objDa = new CommonDA(obj);
string sql = "SELECT DISTINCT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE, TO_CHAR(CREATED, 'DD/MM/YYYY HH24:MI:SS') CREATED_DATE, TEMPORARY " +
"FROM USER_OBJECTS " +
"WHERE OBJECT_TYPE = 'TABLE' ORDER BY OBJECT_NAME";
DataTable dt = objDa.ExecuteSQL(sql, GlobalStruct.ActionType.ExecuteDataTable, ref errMsg).ToDataTable();
List <ObjectMapping> _lst = dt.ToListObject <ObjectMapping>();
string res = _lst.ToSerializer();
return(Json(res));
}
public ActionResult Execute(string query, string dbActive)
{
if (dbActive == "1")
{
obj = objFunc.GetDbSessionSrc();
}
else
{
obj = objFunc.GetDbSessionDes();
}
objDa = new CommonDA(obj);
DataTable dt = objDa.ExecuteSQL(query, GlobalStruct.ActionType.ExecuteDataTable, ref errMsg).ToDataTable();
if (!string.IsNullOrEmpty(errMsg))
{
return(Json(errMsg));
}
List <Dictionary <string, object> > lst = dt.ToListDictionary();
string res = lst.ToSerializer();
return(Json(res));
}
public ActionResult CountTotal(string tableName, string dbActive)
{
if (dbActive == "1")
{
obj = objFunc.GetDbSessionSrc();
}
else
{
obj = objFunc.GetDbSessionDes();
}
objDa = new CommonDA(obj);
string sql = "SELECT COUNT(1) FROM " + tableName;
object r = objDa.ExecuteSQL(sql, GlobalStruct.ActionType.ExecuteScalar, ref errMsg);
if (!string.IsNullOrEmpty(errMsg))
{
return(Json(errMsg));
}
string total = string.Format("{0:n0}", r.ToInt32());
string res = "Total rows of table " + tableName + ": " + total;
return(Json(res));
}
public ActionResult Sync(DbSync _objDb)
{
obj = objFunc.GetDbSessionSrc();
if (obj == null)
{
return(Json("Please check your connection to source database!"));
}
objDa = new CommonDA(obj);
string where = string.Empty;
if (!string.IsNullOrEmpty(_objDb.WHERE))
{
where += " WHERE ";
where += _objDb.WHERE;
}
string sql = "SELECT " + _objDb.SOURCE_COLUMN + " FROM " + _objDb.DB_SOURCE + where;
DataTable dt = objDa.ExecuteSQL(sql, GlobalStruct.ActionType.ExecuteDataTable, ref errMsg).ToDataTable();
//string[] _columnNames = dt.Columns.Cast<DataColumn>()
// .Select(x => x.ColumnName)
// .ToArray();
if (!string.IsNullOrEmpty(errMsg))
{
return(Json(errMsg));
}
int rowCount = dt.Rows.Count;
if (rowCount == 0)
{
return(Json("Table does not contain any item!"));
}
sql = string.Empty;
string sqlSync = string.Empty;
string sqlInsert = string.Empty;
var colName = _objDb.SOURCE_COLUMN.Split(',');
sql += "DECLARE BLOBVAL RAW(32767); BEGIN ";
string blobVal = string.Empty;
foreach (DataRow item in dt.Rows)
{
sqlSync += " UPDATE " + _objDb.DB_DESTINATION + " SET ";
sqlInsert += "INSERT INTO " + _objDb.DB_DESTINATION + "(" + _objDb.SOURCE_COLUMN + ") SELECT ";
foreach (string cName in colName)
{
sqlSync += cName + " = " + GetColValueByName(item, cName, ref blobVal) + ",";
sqlInsert += GetColValueByName(item, cName, ref blobVal) + ",";
}
sqlSync = sqlSync.Remove(sqlSync.Length - 1);
sqlInsert = sqlInsert.Remove(sqlInsert.Length - 1);
sqlSync += " WHERE " + GetWhereByKey(item, _objDb.KEY.Split(',')) + " 1 = 1; ";
sqlInsert += " FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM " + _objDb.DB_DESTINATION + " WHERE " + GetWhereByKey(item, _objDb.KEY.Split(',')) + " 1 = 1); ";
sqlSync += sqlInsert;
sql += blobVal + sqlSync;
}
sql += " END;";
obj = objFunc.GetDbSessionDes();
if (obj == null)
{
return(Json("Please check your connection to destination database!"));
}
objDa = new CommonDA(obj);
objDa.ExecuteSQL(sql, GlobalStruct.ActionType.ExecuteNonQuery, ref errMsg);
if (!string.IsNullOrEmpty(errMsg))
{
return(Json(errMsg));
}
return(Json(rowCount.ToString() + " items has been synchronized!"));
}