public static DataTable GetRecordsFromDSDB(string SqlQuery, DS_SQLInfo DBInfo)
{
try
{
DataSet dataset = new DataSet();
//SQLInfo DBInfo = new SQLInfo();
if (!IsDSDBConnectionAlive(DBInfo))
{
if (!ConnectToDSDB(DBInfo))
{
return(null);
}
}
SqlDataAdapter dataAdaptor = new SqlDataAdapter();
dataAdaptor.SelectCommand = new SqlCommand(SqlQuery, DBInfo.DSDBConnection);
dataAdaptor.SelectCommand.CommandType = CommandType.Text;
dataAdaptor.Fill(dataset, "table");
CloseDSDBConnection(DBInfo);
return(dataset.Tables["table"]);
}
catch (Exception e)
{
CloseDSDBConnection(DBInfo);
return(null);
//fail statement
}
}
public static bool CloseDSDBConnection(DS_SQLInfo DBInfo)
{
//SQLInfo DBInfo = new SQLInfo();
if (IsDSDBConnectionAlive(DBInfo))
{
DBInfo.DSDBConnection.Close();
return(!IsDSDBConnectionAlive(DBInfo));
}
return(false);
}
public static DS_SQLInfo StoreDSSQLInfo()
{
DS_SQLInfo DSDBInfo = new DS_SQLInfo();
DSDBInfo.DSDataBaseServer = ConfigurationHelper.DSDatabaseServerName;
DSDBInfo.DSDataBaseName = ConfigurationHelper.DSDatabaseName;
DSDBInfo.DSDBUserName = ConfigurationHelper.DatabaseUserName;
DSDBInfo.DSDBUserPassword = ConfigurationHelper.DatabaseUserPwd;
DSDBInfo.SQLConnectionString = @"Data Source=" + DSDBInfo.DSDataBaseServer + ";Initial Catalog=" + DSDBInfo.DSDataBaseName + ";User ID=" + DSDBInfo.DSDBUserName + ";Password="******"";
return(DSDBInfo);
//DatabaseUtilities.SQLData(ConfigurationHelper.DatabaseServerName, ConfigurationHelper.DatabaseName, ConfigurationHelper.DatabaseUserName, ConfigurationHelper.DatabaseUserPwd);
}
public static bool IsDSDBConnectionAlive(DS_SQLInfo DBInfo)
{
//SQLInfo DBInfo = new SQLInfo();
SqlConnection ConnObj = DBInfo.DSDBConnection;
if (ConnObj == null || (ConnObj != null && (ConnObj.State == ConnectionState.Closed || ConnObj.State == ConnectionState.Broken)))
{
//fail statement
return(false);
}
return(true);
}
public static bool ConnectToDSDB(DS_SQLInfo DBInfo)
//public static SqlConnection ConnectToDB(string DBServerName, string DBName, string UserID, string Password)
{
try
{
string ConString;
ConString = DBInfo.SQLConnectionString;
//ConString = @"Data Source=ABC;Initial Catalog=Demo;User ID=sa;Password=Datalabs123";
//ConString = @"Data Source=" + DBServerName + ";Initial Catalog=" + DBName + ";User ID=" + UserID + ";Password="******"";
SqlConnection DbCon;
DbCon = new SqlConnection(ConString);
DbCon.Open();
DBInfo.DSDBConnection = DbCon;
return(IsDSDBConnectionAlive(DBInfo));
}
catch (Exception e)
{
//fail statement
return(false);
}
}
public void WhenIExecuteTheQueryForFieldVerification(string QueryToExecute)
{
DS_SQLInfo DSInfo = StoreDSSQLInfo();
DSInfo.DSQueryResultTable = DatabaseUtilities.GetRecordsFromDSDB(QueryToExecute, DSInfo);
if (DSInfo.DSQueryResultTable == null)
{
ScenarioContext.Current["Comments"] = ScenarioContext.Current["Comments"] + "Unable to execute query " + QueryToExecute + " on database. \n";
ScenarioContext.Current["ActResult"] = "Failed";
}
DataTable dtClone = DSInfo.DSQueryResultTable.Clone();
foreach (DataColumn dc in dtClone.Columns)
{
dc.DataType = typeof(string);
}
foreach (DataRow dr in DSInfo.DSQueryResultTable.Rows)
{
dtClone.ImportRow(dr);
}
ScenarioContext.Current["QueryResults"] = dtClone;
}