//----------------------------------------------------------------------------------------------------------------------------
private string GetSQLRowsCount()
{
StringBuilder sbSql = new StringBuilder();
FatcaXmlBean dto = new FatcaXmlBean();
sbSql.Append("Select count(*) from HARM.dbo.FATCA_CRS ");
sbSql.Append(" where ").Append(ReflectPropertyInfo.GetSearchFields(dto)).Append(" like @searchParam ");
return(sbSql.ToString());
}
//----------------------------------------------------------------------------------------------------------------------------
private string GetSQL(string sortKey, string asceding)
{
StringBuilder sbSql = new StringBuilder();
FatcaXmlBean dto = new FatcaXmlBean();
sbSql.Append("Select * from HARM.dbo.FATCA_CRS ");
sbSql.Append(" where ").Append(ReflectPropertyInfo.GetSearchFields(dto)).Append(" like @searchParam ");
sbSql.Append(" ORDER BY ");
sbSql.Append(sortKey.Equals("null") ? " Datum " : sortKey + (asceding.Equals("true") ? " ASC " : " DESC "));
sbSql.Append(" OFFSET @pageSize*(@pageIndex-1) ROWS FETCH NEXT @pageSize ROWS ONLY;");
return(sbSql.ToString());
}
//----------------------------------------------------------------------------------------------------------------------------
public IEnumerable <FatcaXmlBean> GetData(string query, int pageIndex, int pageSize, string sortKey, string asceding)
{
SqlConnection connection = new SqlConnection();
try
{
List <FatcaXmlBean> result = new List <FatcaXmlBean>();
connection = new SqlConnection(ConfigurationManager.ConnectionStrings["hekata_conn"].ConnectionString);
using (connection)
{
connection.Open();
SqlCommand cmd;
using (cmd = new SqlCommand(this.GetSQL(sortKey, asceding), connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@searchParam", "%" + query + "%");
cmd.Parameters.AddWithValue("@pageSize", pageSize);
cmd.Parameters.AddWithValue("@pageIndex", pageIndex);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
result.Add(ReflectPropertyInfo.ReflectType <FatcaXmlBean>(reader));
}
}
}
}
return(result);
}
catch (Exception ex)
{
Console.WriteLine(ex);
throw;
}
finally
{
//connection.Close(); //tega ne rabimo če uporabimo using
}
}