public DataTable BuildTable()
{
StringBuilder sqlCommandString = null;
StringBuilder sqlSelectClause = null;
string orderByColumnName = string.Empty;
char[] parms = { ',' };
List <string> tlist = null;
string wherePhrase = string.Empty;
databaseConnection.Open();
if (databaseConnection.State == ConnectionState.Open)
{
databaseCommand = new System.Data.SqlClient.SqlCommand();
sqlSelectClause = new StringBuilder();
sqlSelectClause.Append("SELECT Count(*) FROM ");
sqlSelectClause.Append(databaseTableName);
databaseCommand.CommandText = sqlSelectClause.ToString();
databaseCommand.Connection = databaseConnection;
_totalNumberOfTableRows = (int)databaseCommand.ExecuteScalar();
sqlSelectClause.Clear();
_numberOfCompletePages = _totalNumberOfTableRows / NumberRowsToDisplay;
sqlSelectClause.Append("SELECT ");
foreach (DatabaseRowObject.DatabaseColumnObject column in columnDictionary.Values)
{
DataColumn dataColumn = new DataColumn();
dataColumn.ColumnName = column.DataGridColumnName;
if (column.DataType == MyDataTypes.INTEGER)
{
dataColumn.DataType = System.Type.GetType("System.Int32");
}
if (column.DataType == MyDataTypes.STRING)
{
dataColumn.DataType = System.Type.GetType("System.String");
}
if (column.DataType == MyDataTypes.GUID)
{
dataColumn.DataType = System.Type.GetType("System.Guid");
}
dataGridTable.Columns.Add(column.DataGridColumnName, dataColumn.DataType);
sqlSelectClause.Append(column.DataBaseTableColumnName);
sqlSelectClause.Append(",");
if (column.OrderByColumn)
{
orderByColumnName = column.DataBaseTableColumnName;
}
}
sqlCommandString = new StringBuilder();
sqlCommandString.Append(sqlSelectClause.ToString().TrimEnd(parms));
sqlCommandString.Append(" FROM ");
sqlCommandString.Append(databaseTableName);
if (whereClause.Length > 0)
{
wherePhrase = whereClause.ToString().TrimEnd(new char[] { ' ', 'A', 'N', 'D' });
sqlCommandString.Append(" WHERE ");
sqlCommandString.Append(wherePhrase);
}
if (String.IsNullOrEmpty(orderByColumnName) == false)
{
sqlCommandString.Append(" ORDER BY ");
sqlCommandString.Append(orderByColumnName);
}
if (Sort == MyDataSort.ASC)
{
sqlCommandString.Append(" ASC ");
}
else
{
sqlCommandString.Append(" DESC ");
}
if (NumberRowsToDisplay > 0)
{
sqlCommandString.Append(" OFFSET ");
sqlCommandString.Append(((PageNumber - 1) * NumberRowsToDisplay).ToString());
sqlCommandString.Append(" ROWS ");
sqlCommandString.Append(" FETCH NEXT ");
sqlCommandString.Append(NumberRowsToDisplay.ToString());
sqlCommandString.Append(" ROWS ONLY ");
}
//= "SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY";=
databaseCommand.CommandText = sqlCommandString.ToString();
databaseCommand.Connection = databaseConnection;
databaseReader = databaseCommand.ExecuteReader();
string fieldData = string.Empty;
tlist = new List <string>();
DataRow row = null;
object[] itemArray = null;
int itemCount = 0;
if (databaseReader.HasRows)
{
while (databaseReader.Read())
{
row = dataGridTable.NewRow();
itemCount = 0;
itemArray = new object[columnDictionary.Count];
foreach (DatabaseRowObject.DatabaseColumnObject column in columnDictionary.Values)
{
switch (column.DataType)
{
case MyDataTypes.INTEGER:
itemArray[itemCount] = Utilities.ParseInt32(databaseReader, itemCount);
break;
case MyDataTypes.STRING:
itemArray[itemCount] = Utilities.ParseStr(databaseReader, itemCount);
break;
case MyDataTypes.GUID:
itemArray[itemCount] = Utilities.ParseGuid(databaseReader, itemCount);
break;
}
itemCount++;
}
row.ItemArray = itemArray;
dataGridTable.Rows.Add(row);
}
databaseReader.Close();
databaseConnection.Close();
}
if (dataGridTable.Rows.Count < NumberRowsToDisplay)
{
while (NumberRowsToDisplay != dataGridTable.Rows.Count)
{
itemArray = new object[columnDictionary.Count];
row = dataGridTable.NewRow();
row.ItemArray = itemArray;
dataGridTable.Rows.Add(row);
}
}
}
return(dataGridTable);
}
public DataTable BuildTable(DataGridObjects.GridRowObject gridInfo = null)
{
int _totalNumberOfTableRows = 0;
StringBuilder sqlCommandString = null;
StringBuilder sqlSelectClause = null;
string orderByColumnName = string.Empty;
char[] parms = { ',' };
List <string> tlist = null;
string wherePhrase = string.Empty;
databaseConnection.Open();
if (databaseConnection.State == ConnectionState.Open)
{
databaseCommand = new System.Data.SqlClient.SqlCommand();
sqlSelectClause = new StringBuilder();
sqlSelectClause.Append("SELECT Count(*) FROM ");
sqlSelectClause.Append(databaseTableName);
databaseCommand.CommandText = sqlSelectClause.ToString();
databaseCommand.Connection = databaseConnection;
_totalNumberOfTableRows = (int)databaseCommand.ExecuteScalar();
sqlSelectClause.Clear();
NumberOfCompletedPages = _totalNumberOfTableRows / Math.Max(NumberRowsToDisplay, 10);
sqlSelectClause.Append("SELECT ");
IEnumerator <DatabaseRowObject.DatabaseColumnObject> iter = DatabaseRowObject.GetEnumerator();
DataColumn dataColumn = null;
DataGridObjects.DatabaseRowObject.DatabaseColumnObject column = null;
while (iter.MoveNext())
{
dataColumn = new DataColumn();
column = iter.Current;
dataColumn.ColumnName = column.DataGridColumnName;
if (column.DataType == MyDataTypes.INTEGER)
{
dataColumn.DataType = System.Type.GetType("System.Int32");
}
if (column.DataType == MyDataTypes.STRING)
{
dataColumn.DataType = System.Type.GetType("System.String");
}
if (column.DataType == MyDataTypes.GUID)
{
dataColumn.DataType = System.Type.GetType("System.Guid");
}
dataGridTable.Columns.Add(column.DataGridColumnName, dataColumn.DataType);
sqlSelectClause.Append(column.DataBaseTableColumnName);
sqlSelectClause.Append(",");
if (column.OrderByColumn)
{
orderByColumnName = column.DataBaseTableColumnName;
}
}
sqlCommandString = new StringBuilder();
sqlCommandString.Append(sqlSelectClause.ToString().TrimEnd(parms));
sqlCommandString.Append(" FROM ");
sqlCommandString.Append(databaseTableName);
///////// now check for joins
iter.Reset();
while (iter.MoveNext())
{
if (string.IsNullOrEmpty(iter.Current.JoinTable) == false)
{
sqlCommandString.Append(" ");
sqlCommandString.Append(" INNER JOIN ");
sqlCommandString.Append(iter.Current.JoinTable);
sqlCommandString.Append(" ON ");
sqlCommandString.Append(iter.Current.JoinColumnMainTable);
sqlCommandString.Append(" = ");
sqlCommandString.Append(iter.Current.JoinColumnJoinTable);
}
}
////////
if (whereClause.Length > 0)
{
wherePhrase = whereClause.ToString().TrimEnd(new char[] { ' ', 'A', 'N', 'D' });
sqlCommandString.Append(" WHERE ");
sqlCommandString.Append(wherePhrase);
}
if (String.IsNullOrEmpty(orderByColumnName) == false)
{
sqlCommandString.Append(" ORDER BY ");
sqlCommandString.Append(orderByColumnName);
}
if (Sort == MyDataSort.ASC)
{
sqlCommandString.Append(" ASC ");
}
else
{
sqlCommandString.Append(" DESC ");
}
if (NumberRowsToDisplay > 0)
{
sqlCommandString.Append(" OFFSET ");
sqlCommandString.Append(((PageNumber - 1) * NumberRowsToDisplay).ToString());
sqlCommandString.Append(" ROWS ");
sqlCommandString.Append(" FETCH NEXT ");
sqlCommandString.Append(NumberRowsToDisplay.ToString());
sqlCommandString.Append(" ROWS ONLY ");
}
databaseCommand.CommandText = sqlCommandString.ToString();
databaseCommand.Connection = databaseConnection;
databaseReader = databaseCommand.ExecuteReader();
string fieldData = string.Empty;
tlist = new List <string>();
DataRow row = null;
object[] itemArray = null;
int itemCount = 0;
DatabaseRowParser rowParser = null;
rowParser = new DatabaseRowParser(DatabaseRowObject, dataEncryptionAlgorithm);
DataRowDisplay rowToDisplay = null;
if (databaseReader.HasRows)
{
while (databaseReader.Read())
{
// for each row in the grid info object
// iterate through the columns and find that column in the the row from the database
// place that value of the column in the row for the data grid object
// else a row from the database is a row in the datagridview
row = dataGridTable.NewRow();
itemCount = 0;
itemArray = new object[DatabaseRowObject.NumberColumns];
iter = DatabaseRowObject.GetEnumerator();
column = null;
rowParser = new DatabaseRowParser(DatabaseRowObject, dataEncryptionAlgorithm);
rowParser.sqlReader = databaseReader;
while (iter.MoveNext())
{
column = iter.Current;
itemArray[itemCount] = rowParser.GetValue(column.DataBaseTableColumnName);
itemCount++;
}
row.ItemArray = itemArray;
dataGridTable.Rows.Add(row);
}
databaseReader.Close();
databaseConnection.Close();
}
if (FillToCompletePage)
{
if (dataGridTable.Rows.Count < NumberRowsToDisplay)
{
while (NumberRowsToDisplay != dataGridTable.Rows.Count)
{
itemArray = new object[DatabaseRowObject.NumberColumns];
row = dataGridTable.NewRow();
row.ItemArray = itemArray;
dataGridTable.Rows.Add(row);
}
}
}
}
return(dataGridTable);
}
