public void loadData(DatabaseConnection DBConn, DataSet ds, string tableName, ListInfo info, string select, string from, string distinct) { StringBuilder s = new StringBuilder(); s.Append("SELECT "); if (!string.IsNullOrEmpty(distinct)) { s.Append("DISTINCT "); } s.Append(select).Append(" "); s.Append(from).Append(" "); //s.Append(getWhereClause()); //s.Append(getGroupCluase()); //s.Append(getOrderClause(info)); DbCommand command = DBConn.CreateSelectCommand(s.ToString(), this, info); DbDataAdapter adapter = DBConn.CreateAdapter(); adapter.SelectCommand = command; try { //conn.Open(); if (info != null) { StringBuilder cs = new StringBuilder(); cs.Append("SELECT "); if (!string.IsNullOrEmpty(distinct)) { cs.Append("COUNT(DISTINCT ").Append(distinct).Append(") "); } else { cs.Append("COUNT(*) "); } cs.Append(from); cs.Append(getWhereClause(DBConn)); //cs.Append(getGroupCluase()); DbCommand ccommand = DBConn.CreateCommand(); ccommand.CommandText = cs.ToString(); setParams(ccommand); info.numRecord = Convert.ToInt32(DBConn.ExecuteScalar(ccommand)); if (info.recordPerPage > 0) { info.numPage = (int)Math.Ceiling((double)info.numRecord / info.recordPerPage); if (info.page >= info.numPage) { info.page = info.numPage - 1; } if (info.page < 0) { info.page = 0; } int rowIndex = info.page * info.recordPerPage; adapter.Fill(ds, rowIndex, info.recordPerPage, "table"); } else { info.numPage = 1; info.page = 0; adapter.Fill(ds, tableName); } } else { adapter.Fill(ds, tableName); } //DataTable table=ds.Tables["table"]; //ds.Tables.Clear(); //return table; } catch (Exception ex) { throw new Exception(ex.ToString() + "\r\n" + select + from); } finally { adapter.Dispose(); //conn.Close(); //conn.Dispose(); } }
public DbCommand CreateSelectCommand(string SelectQueryWithoutConstraint, DBFilter dbFilter, ListInfo info) { int commandIndex = 1; int buildSQLIndex = 1; string query = SelectQueryWithoutConstraint; DbCommand command = CreateCommand(); command.CommandType = CommandType.Text; if (dbFilter != null) { query += dbFilter.getWhereClause(this, false, buildSQLIndex, out buildSQLIndex); query += dbFilter.getGroupCluase(); query += dbFilter.getOrderClause(info); dbFilter.setParams(commandIndex, command); } command.CommandText = query; return(command); }
public DataTable loadData(DatabaseConnection DBConn, ListInfo info, string select, string from) { return(loadData(DBConn, info, select, from, null)); }