public override void LoadColumns(Table table)
{
// Halt Loadcolumns if there are critical errors.
if (table.m_LoadedTableSchema || string.IsNullOrEmpty(table.DataSourceId) ||
table.m_Grid.SystemMessage.CriticalCount > 0)
{
return;
}
if (table.m_Grid.Trace.IsTracing)
{
table.m_Grid.Trace.Trace("{0}: LoadColumns( {1})", table.m_Grid.ID, table.DataSourceId);
}
string sql = String.Format("SELECT * FROM [{0}]", table.DataSourceId);
if (table == table.m_Grid.MasterTable && string.IsNullOrEmpty(table.m_Grid.Sql) == false)
{
sql = table.m_Grid.Sql;
}
Query q = SqlConnectionQuery.ExecuteReader(sql, table.ConnectionString, CommandBehavior.KeyInfo);
LoadColumnSettings(q.DataReader.GetSchemaTable(), table);
q.Close();
}
/// <summary>
/// Loads one or more rows from the SQL Server database into the specified Table.
/// </summary>
/// <param name="table">The Table object to populate with rows.</param>
public override void GetData(Table table)
{
if (table.m_Grid.SystemMessage.CriticalCount > 0 || (table.DataSourceId == null && table.Sql == null))
{
return;
}
string sql = table.Sql;
if (sql == null)
{
if (table.m_Grid.Trace.IsTracing)
{
table.m_Grid.Trace.Trace("Start SqlConnection-GetData({0});", table.DataSourceId);
}
sql = SqlBuilder(table);
if (table.m_Grid.Trace.IsTracing)
{
table.m_Grid.Trace.Trace("Finish SqlConnection-GetData({0});", table.DataSourceId);
}
}
// FAEN FAEN NY HACK..
// if (sql.StartsWith("SELECT FROM", StringComparison.OrdinalIgnoreCase))
// sql = sql.Replace("SELECT FROM", "SELECT * FROM");
if (table.m_Grid.Debug)
{
table.m_Grid.m_DebugString.AppendFormat("<b>{0}: SqlConnection.GetData({1})</b> - {2}<br/>", table.m_Grid.ID,
table.DataSourceId, sql);
}
Query q = null;
try
{
q = table.Sql == null?SqlConnectionQuery.ExecuteReader(sql, table.ConnectionString) : SqlConnectionQuery.ExecuteReader(sql, table.ConnectionString, CommandBehavior.KeyInfo);
}
catch (Exception ee)
{
if (q != null)
{
q.Close();
}
throw new GridException("Error retrieving data from SqlConnection data interface", ee);
}
if (table.Sql != null)
{
LoadColumnSettings(q.DataReader.GetSchemaTable(), table);
}
// Read past first TOP records for paging
// Do not do this if we have a tree. Then manual paging in design render
if (string.IsNullOrEmpty(table.m_Grid.TreeParentId) && table == table.m_Grid.MasterTable &&
table.m_Grid.PageSize > 0 &&
table.m_Grid.PageIndex > 1 && table.m_Grid.DisplayView == DisplayView.Grid)
{
//if(table.Parent.PageLength != 0 && (table.Parent.TreeParentId == null || table == table.Parent.MasterTable ) && table.Parent.PageIndex > 1 && table.Parent.Mode == Modes.Grid)
for (int i = 0; i < (table.m_Grid.PageIndex - 1) * table.m_Grid.PageSize; i++)
{
q.Read();
}
}
if (table.m_Grid.Trace.IsTracing)
{
table.m_Grid.Trace.Trace("SQL: {0} : {1}", table.DataSourceId, sql);
}
// Predefining rows and columns
int tablecolumns = table.Columns.Count;
List <int> skipcolumns = new List <int>();
try
{
while (q.Read())
{
Row r = new Row(table);
for (int i = 0; i < tablecolumns; i++)
{
Column tablecolumn = table.Columns[i];
int columnIndex = i;
RowCell c = new RowCell(tablecolumn.ColumnId, r);
if (tablecolumn.ColumnType == ColumnType.ColumnTemplate)
{
c.Value = ((ColumnTemplate)tablecolumn).CreateCellControls;
}
c.Row = r;
if (tablecolumn.IsInDataSource)
{
c.Value = null;
}
r.Cells.Add(c);
if (skipcolumns.Contains(columnIndex.GetHashCode()))
{
continue;
}
if ((tablecolumn.IsInDataSource == false || tablecolumn.ColumnType == ColumnType.SystemColumn) ||
(tablecolumn.IsBlob /* && tablecolumn.FileNameColumn != tablecolumn.ColumnId*/))
{
skipcolumns.Add(columnIndex.GetHashCode());
continue;
}
int dsindex = q.m_DataReader.GetOrdinal(tablecolumn.ColumnId);
if (q[dsindex] == null)
{
skipcolumns.Add(columnIndex.GetHashCode());
continue;
}
c.DataSourceValue = q.IsDBNull(dsindex) ? tablecolumn.OnLoadFromDatabase(null) : tablecolumn.OnLoadFromDatabase(q[dsindex]);
c.Value = c.DataSourceValue;
// FILES IN DATABASE SHOULD NOT BE LOADED HERE. -Olav
// IS ONLY ACTIVE IF FILENAMECOLUMN IS ACTIVE.
if (tablecolumn.ColumnType == ColumnType.Text && ((Text)tablecolumn).EncryptionKey != null &&
c.DataSourceValue != null)
{
// Decrypt
c.DataSourceValue =
Security.Decrypt(c.DataSourceValue.ToString(), ((Text)tablecolumn).EncryptionKey);
c.Value = c.DataSourceValue;
}
// Foreign key stuff
if (tablecolumn.ColumnType != ColumnType.Foreignkey || string.IsNullOrEmpty(tablecolumn.DataSourceId))
{
continue;
}
if (!string.IsNullOrEmpty(table.m_Grid.Sql) || ((Foreignkey)tablecolumn).Table.DataSource != null)
{
continue;
}
c.DataSourceDisplayValue = q[string.Format("_fk_{0}_text", c.CellId)];
c.DisplayValue = c.DataSourceDisplayValue;
}
table.Rows.Add(r);
}
}
catch (Exception ee)
{
q.Close();
throw new GridException("Error populating grid with data from SqlConnection data interface" + sql, ee);
}
if (table.Rows.Count == 0 && table == table.m_Grid.MasterTable && (table.m_Grid.DisplayView == DisplayView.Detail))
{
if (table.m_Grid.m_IsOneToOneRelationGrid == false)
{
table.m_Grid.SystemMessage.Add(string.Format("{0} ({1})", table.m_Grid.GetSystemMessage("SystemMessage_NoRecord"), table.m_Grid.InternalId), true);
}
else
{
table.m_Grid.m_IsOneToOneRelationNewRecord = true;
}
}
q.Close();
}
private static void LoadForeignKeys(Table table)
{
string foreignkeydatatable = table.DataSourceId;
if (string.IsNullOrEmpty(foreignkeydatatable) ||
table.m_Grid.SystemMessage.CriticalCount != 0)
{
if (table.m_Grid.Debug)
{
table.m_Grid.m_DebugString.AppendFormat("<b>SqlConnection.SkippingForeignkeyLoad({0})", table);
}
return;
}
if (table.m_Grid.Trace.IsTracing)
{
table.m_Grid.Trace.Trace("LoadForeignKeys()");
}
StringBuilder sql = new StringBuilder(string.Empty);
sql.Append("SELECT SO1.name AS Tab, ");
sql.Append(" SC1.name AS Col, ");
sql.Append(" SO2.name AS RefTab, ");
sql.Append(" SC2.name AS RefCol, ");
sql.Append(" FO.name AS FKName ");
sql.Append("FROM dbo.sysforeignkeys FK ");
sql.Append("INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id ");
sql.Append(" AND FK.fkey = SC1.colid ");
sql.Append("INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id ");
sql.Append(" AND FK.rkey = SC2.colid ");
sql.Append("INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id ");
sql.Append("INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id ");
sql.Append("INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id ");
sql.AppendFormat("WHERE SO1.Name = '{0}'", foreignkeydatatable);
if (foreignkeydatatable.IndexOf("[", StringComparison.OrdinalIgnoreCase) != -1)
{
string database = foreignkeydatatable.Substring(0, foreignkeydatatable.IndexOf("."));
string datatable = foreignkeydatatable.Replace(string.Format("{0}.dbo.", database), string.Empty).Replace("[", string.Empty).Replace("]", string.Empty);
sql = new StringBuilder(
@"SELECT SO1.name AS Tab, SC1.name AS Col, SO2.name AS RefTab, SC2.name AS RefCol, FO.name AS FKName
FROM [XXX].sysforeignkeys FK INNER JOIN
[XXX].syscolumns SC1 ON FK.fkeyid = SC1.id AND FK.fkey = SC1.colid INNER JOIN
[XXX].syscolumns SC2 ON FK.rkeyid = SC2.id AND FK.rkey = SC2.colid INNER JOIN
[XXX].sysobjects SO1 ON FK.fkeyid = SO1.id INNER JOIN
[XXX].sysobjects SO2 ON FK.rkeyid = SO2.id INNER JOIN
[XXX].sysobjects FO ON FK.constid = FO.id ");
sql = sql.Replace("[XXX]", string.Format("{0}.dbo", database));
sql.AppendFormat("WHERE SO1.Name = '{0}'", datatable);
}
if (table.m_Grid.Debug)
{
table.m_Grid.m_DebugString.AppendFormat("<b>{0}: SqlConnection.LoadForeignKeys({1})</b> - {2}<br/>",
table.m_Grid.ID, foreignkeydatatable, sql);
}
if (table.m_Grid.Trace.IsTracing)
{
table.m_Grid.Trace.Trace(
"{0}: SqlConnection.LoadForeignKeys({1})</b> - {2}<br/>",
table.m_Grid.ID, foreignkeydatatable, sql);
}
Query q = SqlConnectionQuery.ExecuteReader(sql.ToString(), table.ConnectionString);
//Util.Query q = new Util.Query(sql,table.ConnectionString);
while (q.Read())
{
string col = q["Col"].ToString();
// is there any column with this name?
Foreignkey column = new Foreignkey(col, table);
if (table.Columns.GetIndex(col) > -1)
{
if (table.Columns[col].IsCreatedByWebGrid == false &&
table.Columns[col].ColumnType != ColumnType.Foreignkey)
{
continue;
}
Column oldcolumn = table.Columns[col];
table.Columns[col] = oldcolumn.CopyTo(column);
table.m_Columns[col].m_ColumnType = ColumnType.Foreignkey;
table.Columns[col].GridAlign = HorizontalPosition.Left;
if (table.Columns[col].m_Searchable == null)
{
table.Columns[col].m_Searchable = true;
}
if (table.Columns[col].m_Sortable == null)
{
table.Columns[col].m_Sortable = true;
}
}
else
{
// else new column
table.Columns.Add(column);
column.ColumnId = col;
}
if (column.ConnectionString == null)
{
column.ConnectionString = table.ConnectionString;
}
if (column.m_DataSourceId == null)
{
column.m_DataSourceId = q["reftab"] as string;
}
column.m_SaveValueToViewState = true;
column.GridAlign = HorizontalPosition.Left;
}
q.Close();
}
