//---------------------------------------------------------------------------------------------------------------------------- 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 } }