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