/// <summary>
/// Gets the data from given table which passes given where clause condition.
/// </summary>
/// <param name="tableSchema">The table schema. Table schema inclues table name, list of columns and where clause conditions</param>
/// <returns></returns>
public ResultSet GetDataFromTable(TableSchema tableSchema)
{
ResultSet tableData = new ResultSet();
try
{
DBHandler dbHandler = new DBHandler();
string connectionString = WebConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
string proiderName = WebConfigurationManager.AppSettings["DBProvider"].ToString();
// Creating connection
// File.AppendAllText(@"D:\Charmi\Test.txt", "ConnectionString: " + connectionString + Environment.NewLine + "providerName: " + proiderName);
DbConnection dbConnection = dbHandler.CreateDbConnection(proiderName, connectionString);
dbConnection.Open();
DbDataReader dataReader = null;
// Generating query string
string selectQuery = QueryGenerator.GenerateSelectQuery(tableSchema);
if (!string.IsNullOrEmpty(selectQuery))
{
DbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = selectQuery;
dbCommand.Connection = dbConnection;
dataReader = dbCommand.ExecuteReader();
List <Record> rows = ConvertToRecord(dataReader, tableSchema.ColumnNames);
tableData.Records = rows;
tableData.IsSuccess = true;
tableData.NumberOfRowsAffected = tableData.Records.Count;
}
else
{
tableData.IsSuccess = false;
tableData.ErrorMessage = "Error while creating query string";
}
dbConnection.Close();
return(tableData);
}
catch (Exception ex)
{
tableData.IsSuccess = false;
tableData.ErrorMessage = ex.Message;
return(tableData);
}
}
/// <summary>
/// Deletes rows from table which matches the given condition.
/// </summary>
/// <param name="tableSchema">The table schema. This includes table name and condition which specifies which row to delete.</param>
/// <returns></returns>
public ResultSet DeleteFromTable(TableSchema tableSchema)
{
ResultSet tableData = new ResultSet();
try
{
DBHandler dbHandler = new DBHandler();
// Creating connection
DbConnection dbConnection = dbHandler.CreateDbConnection(tableSchema.ProviderName, tableSchema.ConnectionString);
dbConnection.Open();
// Generating query string
string deleteQuery = QueryGenerator.GenerateDeleteQuery(tableSchema);
if (!string.IsNullOrEmpty(deleteQuery))
{
DbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = deleteQuery;
dbCommand.Connection = dbConnection;
int rowsAffected = dbCommand.ExecuteNonQuery();
tableData.NumberOfRowsAffected = rowsAffected;
tableData.IsSuccess = true;
}
else
{
tableData.IsSuccess = false;
tableData.ErrorMessage = "Error in generating query string.";
}
dbConnection.Close();
return(tableData);
}
catch (Exception ex)
{
tableData.IsSuccess = false;
tableData.NumberOfRowsAffected = 0;
tableData.ErrorMessage = ex.Message;
return(tableData);
}
}