public int GetTotalRowsClients(Connections connections, ClientSearch clientSearch)
{
QueryWarehouseClient _query = new QueryWarehouseClient();
int totalRows = 0;
string connectionString = $"Server={connections.ServerName};Database={connections.DatabaseName};User Id={connections.DatabaseUsername};Password={connections.DatabasePassword};";
string queryString = _query.CrateQueryStringCountRowsClients(clientSearch);
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
command = _query.EmbedParameters(command, clientSearch);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
totalRows = Convert.ToInt32(reader[0]);
}
reader.Close();
return(totalRows);
}
catch (Exception ex)
{
connection.Close();
Console.WriteLine(ex.ToString());
return(0);
}
}
}
public List <ClientResult> GetSearchClients(Connections connections, ClientSearch clientSearch, Paging paging)
{
QueryWarehouseClient _query = new QueryWarehouseClient();
string connectionString = $"Server={connections.ServerName};Database={connections.DatabaseName};User Id={connections.DatabaseUsername};Password={connections.DatabasePassword};";
string queryString = _query.CrateQueryStringClient(clientSearch, paging);
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
command = _query.EmbedParameters(command, clientSearch);
try
{
List <ClientResult> results = new List <ClientResult>();
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
ClientResult clientResult = new ClientResult();
clientResult.SetValuesFromReader(reader);
results.Add(clientResult);
}
reader.Close();
connection.Close();
return(results);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return(null);
}
finally
{
connection.Close();
}
}
}