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)));
}