public ServerDataModel GenerateRemoteConnectionString(int testBenchID)
{
string databaseSource = "";
ServerDataModel model = new ServerDataModel();
try
{
TestBenchDetail _testBenchDetails = GetTestBenchInfo(testBenchID);
string ip = _testBenchDetails.IPAddress;
string port = _testBenchDetails.PortNo.ToString();
string dbname = _testBenchDetails.DBName;
string user = _testBenchDetails.DBUser;
string pass = _testBenchDetails.DBPassword;
string source = ip + ", " + port;
databaseSource = @"Data Source=" + source + ";Initial Catalog=master;User ID=" + user + ";Password="******"";
model.DBName = _testBenchDetails.DBName;
model.DBSource = databaseSource;
}
catch (Exception ex)
{
LoggingService service = new LoggingService("WebJob - Local_TestBenchService/GenerateConnectionString", ex.Message, System.DateTime.Now);
service.LogError();
return(null);
}
return(model);
}
public DataTable GetTestBenchTable(int id)
{
try
{
using (LT_SERVER_DBEntities entity = new LT_SERVER_DBEntities())
{
TestBenchDetail _testBenchDetails = GetTestBenchInfo(id, entity);
string ip = _testBenchDetails.IPAddress;
string port = _testBenchDetails.PortNo.ToString();
string dbname = _testBenchDetails.DBName;
string user = _testBenchDetails.DBUser;
string pass = _testBenchDetails.DBPassword;
string source = ip + ", " + port;
con = new Connection(source, dbname, user, pass);
_query = "SELECT DISTINCT * FROM information_schema.tables;";
con.SqlQuery(_query);
dt = con.QueryEx();
con.closeConnection();
}
//SaveTableDetails(_testBenchDetails.TestBenchID, dt);
}
catch (Exception epx)
{
//LogLibrary.WriteErrorLog("Error in getData Function in From Table Class" + epx);
Debug.WriteLine(epx);
}
return(dt);
}
public TestBenchDetail GetTestBenchInfo(int ID, LT_SERVER_DBEntities entity)
{
TestBenchDetail priorityList = null;
try
{
priorityList = entity.TestBenchDetails.Where(x => x.ID == ID).FirstOrDefault();
}
catch (Exception ex)
{
LoggingService service = new LoggingService("TestBenchService/GetTestBenchInfo", ex.Message, System.DateTime.Now);
service.LogError();
}
return(priorityList);
}
public string GenerateConnectionString(int testBenchID)
{
string databaseSource = "";
try
{
TestBenchDetail _testBenchDetails = GetTestBenchInfo(testBenchID);
string ip = _testBenchDetails.IPAddress;
string port = _testBenchDetails.PortNo.ToString();
string dbname = _testBenchDetails.DBName;
string user = _testBenchDetails.DBUser;
string pass = _testBenchDetails.DBPassword;
string source = ".";
databaseSource = @"Data Source=" + source + ";Initial Catalog=" + dbname + ";Integrated Security=True";
}
catch (Exception ex)
{
LoggingService service = new LoggingService("WebJob - Local_TestBenchService/GenerateConnectionString", ex.Message, System.DateTime.Now);
service.LogError();
}
return(databaseSource);
}
private void GetTableData(List <TablePriority> list)
{
try
{
TestBenchService service = new TestBenchService();
foreach (var item in list)
{
TestBenchDetail _testBenchDetails = _service.GetTestBenchInfo(Convert.ToInt32(item.TestBenchID));
string ip = _testBenchDetails.IPAddress;
string port = _testBenchDetails.PortNo.ToString();
string dbname = _testBenchDetails.DBName;
string user = _testBenchDetails.DBUser;
string pass = _testBenchDetails.DBPassword;
string source = ip + ", " + port;
con = new Connection(source, dbname, user, pass);
if (item.TableName.Trim() == "BBT_HV_Production Report")
{
try
{
serverCon = new ServerConnection();
query = "SELECT convert(varchar, MAX([Date_Time]), 103) AS DATE FROM [BBT_HV_Production Report]";
serverCon.SqlQuery(query);
string MaxDate = serverCon.DataReaderSingleRow();
serverCon.closeConnection();
if (!String.IsNullOrEmpty(MaxDate))
{
LoggingService s114 = new LoggingService("CreatePriorityJob/GetTableData", "Max Date for BBT_HV_Production Report = " + MaxDate, System.DateTime.Now);
s114.LogError();
_query = "SELECT * " +
" FROM [" + item.TableName + "] WHERE Date_Time >='" + MaxDate + "'";
}
else
{
_query = "SELECT * " +
" FROM [" + item.TableName + "]";
}
LoggingService s15 = new LoggingService("CreatePriorityJob/GetTableData", "Select Query for HV " + _query, System.DateTime.Now);
s15.LogError();
con.SqlQuery(_query);
dt = con.QueryEx();
con.closeConnection();
LoggingService s1 = new LoggingService("CreatePriorityJob/GetTableData", "Total Records fetch from remote machine table " + item.TableName + " are " + dt.Rows.Count, System.DateTime.Now);
s1.LogError();
InsertData(dt, item.TableName);
}
catch (Exception ex)
{
LoggingService service1222 = new LoggingService("CreatePriorityJob/GetTableData", "Exception in Connection Remote machine : " + ex.InnerException.Message, System.DateTime.Now);
service1222.LogError();
}
}
if (item.TableName.Trim() == "BBT_IR_Production Report")
{
try
{
serverCon = new ServerConnection();
query = "SELECT convert(varchar, MAX([Date_Time]), 103) AS DATE FROM [BBT_IR_Production Report]";
LoggingService s111 = new LoggingService("CreatePriorityJob/GetTableData", "Query for Max Date = " + query, System.DateTime.Now);
s111.LogError();
serverCon.SqlQuery(query);
string MaxDate = serverCon.DataReaderSingleRow();
serverCon.closeConnection();
LoggingService s1111 = new LoggingService("CreatePriorityJob/GetTableData", "Max Date for BBT_IR_Production Report = " + MaxDate, System.DateTime.Now);
s1111.LogError();
if (!String.IsNullOrEmpty(MaxDate))
{
_query = "SELECT * " +
" FROM [" + item.TableName + "] WHERE Date_Time >='" + MaxDate + "'";
}
else
{
_query = "SELECT * " +
" FROM [" + item.TableName + "]";
}
LoggingService s15 = new LoggingService("CreatePriorityJob/GetTableData", "Select Query for IR " + _query, System.DateTime.Now);
s15.LogError();
con.SqlQuery(_query);
dt = con.QueryEx();
con.closeConnection();
LoggingService s1 = new LoggingService("CreatePriorityJob/GetTableData", "Total Records fetch from remote machine table " + item.TableName + " are " + dt.Rows.Count, System.DateTime.Now);
s1.LogError();
InsertData(dt, item.TableName);
}
catch (Exception ex)
{
LoggingService service1 = new LoggingService("CreatePriorityJob/GetTableData", "Exception in Connection Remote machine : " + ex.InnerException.Message, System.DateTime.Now);
service1.LogError();
}
}
if (item.TableName.Trim() == "BBT_IR_Settings")
{
try
{
_query = "SELECT * " +
" FROM [" + item.TableName + "]";
con.SqlQuery(_query);
dt = con.QueryEx();
con.closeConnection();
LoggingService s1 = new LoggingService("CreatePriorityJob/GetTableData", "Total Records fetch from remote machine table " + item.TableName + " are " + dt.Rows.Count, System.DateTime.Now);
s1.LogError();
InsertData(dt, item.TableName);
}
catch (Exception ex)
{
LoggingService service1 = new LoggingService("CreatePriorityJob/GetTableData", "Exception in Connection Remote machine : " + ex.InnerException.Message, System.DateTime.Now);
service1.LogError();
}
}
}
}
catch (Exception ex)
{
LoggingService service = new LoggingService("CreatePriorityJob/GetTableData", "Exception in Connection Remote machine : " + ex.InnerException.Message, System.DateTime.Now);
service.LogError();
}
}
