public bool internalExecute(DSQuery query)
{
using (SqlConnection connection = new SqlConnection(cs))
{
int roDS_affected = 0;
try
{
connection.Open();
SqlCommand command = new SqlCommand(query.query, connection);
SetParametersToCommand(query, command);
roDS_affected = command.ExecuteNonQuery();
}
catch (Exception exception)
{
Console.Write(exception);
throw (exception);
}
finally
{
connection.Close();
connection.Dispose();
}
return(roDS_affected != 0);
}
}
public DataSet internalExecuteSelectQueryAndReturnDataSet(DSQuery query)
{
using (SqlConnection connection = new SqlConnection(cs))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = query.query;
SetParametersToCommand(query, command);
DataSet dataSet = new DataSet();
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
MapSqlCommandToDataSetSync(command, dataSet);
}
catch (Exception exception)
{
Console.Write(exception);
throw (exception);
}
finally
{
connection.Close();
connection.Dispose();
}
return(dataSet);
}
}
internal bool Delete(long employeeID)
{
bool result = false;
DatabaseManager DBManager = DatabaseManager.sharedDatabaseManagerinstance;
DSQuery dbquery = new DSQuery();
dbquery.query = " DELETE FROM Employee where Id=@EmployeeID";
dbquery.parameters.Add("EmployeeID", employeeID);
result = DBManager.internalExecute(dbquery);
return(result);
}
protected void SetParametersToCommand(DSQuery query, SqlCommand command)
{
foreach (var key in query.parameters.Keys)
{
var paramkey = "@" + key;
if (this.isPrimptive(query.parameters[key]))
{
if (query.parameters[key] == null)
{
command.Parameters.AddWithValue(paramkey, DBNull.Value);
}
else
{
command.Parameters.AddWithValue(paramkey, query.parameters[key]);
}
}
}
}
internal bool AddEmployee(Employee emp)
{
bool result = false;
DatabaseManager DBManager = DatabaseManager.sharedDatabaseManagerinstance;
DSQuery dbquery = new DSQuery();
dbquery.query = @" insert into Employee
([Name]
,[Age]
,[Salary]
,[MaritalStatus]
,[Location]) values(@Name,@Age,@Salary,@MaritalStatus,@Location);";
dbquery.parameters.Add("Name", emp.Name);
dbquery.parameters.Add("Age", emp.Age);
dbquery.parameters.Add("Salary", emp.Salary);
dbquery.parameters.Add("MaritalStatus", emp.MaritalStatus);
dbquery.parameters.Add("Location", emp.Location);
result = DBManager.internalExecute(dbquery);
return(result);
}
internal DataSet getEmployeeList(DataTableFilter filter)
{
QueryBuilderHelper QBManager = new QueryBuilderHelper();
string searchQuery = string.Empty;
if (!string.IsNullOrEmpty(filter.sSearch))
{
searchQuery = QBManager.generateSearchQuery(filter);
}
string SortAndPaginationquery = QBManager.generateSortAndPaginationQuery(filter);
DataSet ds = null;
DatabaseManager DBManager = DatabaseManager.sharedDatabaseManagerinstance;
DSQuery dbquery = new DSQuery();
dbquery.query = " SELECT Id,Name,Age,Salary,MaritalStatus,Location FROM Employee EMPLOYEE";
dbquery.query = dbquery.query + " " + searchQuery + " " + SortAndPaginationquery + "; ";
dbquery.query = dbquery.query + (getCountDBQuery() + searchQuery);
dbquery.parameters.Add("SearchString", filter.sSearch);
ds = DBManager.internalExecuteSelectQueryAndReturnDataSet(dbquery);
return(ds);
}