public List <WFSelectValue> getValues(DatabaseConnection DBConn, DBFilter filter, CultureInfo ci) { string sql = "SELECT "; if (distinct) { sql += "DISTINCT "; } for (int i = 0; i != fields.Length; i++) { if (i > 0) { sql += ","; } sql += fields[i]; } sql += " FROM " + tableName; if (terms.Count > 0) { if (filter == null) { filter = new DBFilter(); } foreach (DBTerm t in terms) { filter.add(t); } } if (!string.IsNullOrEmpty(sortField)) { if (filter == null) { filter = new DBFilter(); } filter.add(sortField, true); } IDbCommand command = DBConn.CreateSelectCommand(sql, filter); DataTable table = DBConn.ExecuteToDataTable(command); List <WFSelectValue> list = new List <WFSelectValue>(); IDataReader reader = DBConn.ExecuteReader(command); while (reader.Read()) { string name = GetText(reader); string val = GetKey(reader); WFSelectValue sv = new WFSelectValue(val, name); list.Add(sv); } reader.Close(); return(list); }
public List <WFSelectValue> getValues(DatabaseConnection DBConn, DBFilter filter, CultureInfo ci) { if (terms.Count > 0) { if (filter == null) { filter = new DBFilter(); } foreach (DBTerm t in terms) { filter.add(t); } } if (!string.IsNullOrEmpty(sortField)) { if (filter == null) { filter = new DBFilter(); } filter.add(sortField, true); } IDbCommand command = DBConn.CreateSelectCommand("SELECT DISTINCT " + keyField + " K, " + nameField + " V," + sortField + " FROM " + tableName, filter); DataTable table = DBConn.ExecuteToDataTable(command); List <WFSelectValue> list = new List <WFSelectValue>(); foreach (DataRow row in table.Rows) { string name = row["V"].ToString().Trim(); string val = row["K"].ToString().Trim(); WFSelectValue sv = new WFSelectValue(val, name); list.Add(sv); } return(list); }
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 DataTable loadData(DatabaseConnection DBConn, string SelectQuery) { DataSet ds = new DataSet(); return(DBConn.ExecuteToDataTable(DBConn.CreateSelectCommand(SelectQuery, this))); }