public void SetDataSourceForeignKeyStringArray()
{
Grid mastergrid = new Grid();
mastergrid.ID = "test";
mastergrid.Page = Testpage;
mastergrid.DisplayView = DisplayView.Detail;
Testpage.Controls.Add(mastergrid);
Foreignkey column = new Foreignkey("ForeignkeyItems", mastergrid.MasterTable);
column.NullText = "Select Category";
column.Required = true;
mastergrid.MasterTable.Columns.Add(column);
WebGrid.Data.Table fktable = new WebGrid.Data.Table(mastergrid, true);
string[] names = new[] { "Tom", "Dick", "Harry", "Thomas", "Anders", "Carl", "Trond", "Eli", "Eva" };
fktable.DataSource = names;
((Foreignkey)mastergrid["ForeignkeyItems"]).Table = fktable;
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
HtmlTextWriter gridwriter = new HtmlTextWriter(sw);
mastergrid.RenderControl(gridwriter);
Assert.AreEqual(mastergrid.MasterTable.Rows.Count, 1);
Assert.IsTrue(mastergrid.MasterTable.GotSchema);
Assert.AreEqual(((Foreignkey)mastergrid["ForeignkeyItems"]).Table.Rows.Count, names.Length);
}
public void SetDataSourceForeignKeyGetDataForce()
{
Grid mastergrid = new Grid();
mastergrid.ID = "test";
mastergrid.Page = Testpage;
mastergrid.DisplayView = DisplayView.Detail;
Testpage.Controls.Add(mastergrid);
Foreignkey column = new Foreignkey("ForeignkeyItems", mastergrid.MasterTable);
column.NullText = "Select Category";
column.Required = true;
mastergrid.MasterTable.Columns.Add(column);
WebGrid.Data.Table fktable = new WebGrid.Data.Table(mastergrid, true);
StringDictionary tmp = new StringDictionary();
tmp.Add("Category 1", "1");
tmp.Add("Category 2", "2");
tmp.Add("Category 3", "3");
tmp.Add("Category 4", "4");
tmp.Add("Category 5", "5");
tmp.Add("Category 6", "6");
tmp.Add("Category 7", "7");
fktable.DataSource = tmp;
((Foreignkey)mastergrid["ForeignkeyItems"]).Table = fktable;
((Foreignkey)mastergrid["ForeignkeyItems"]).ValueColumn = "Value";
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
HtmlTextWriter gridwriter = new HtmlTextWriter(sw);
mastergrid.RenderControl(gridwriter);
Assert.AreEqual(mastergrid.MasterTable.Rows.Count, 1);
Assert.IsTrue(mastergrid.MasterTable.GotSchema);
Assert.AreEqual(((Foreignkey)mastergrid["ForeignkeyItems"]).Table.Rows.Count, 7);
((Foreignkey)mastergrid["ForeignkeyItems"]).Table.FilterExpression = "Value = 3";
Assert.AreEqual(((Foreignkey)mastergrid["ForeignkeyItems"]).Table.Rows.Count, 1);
}
/// <summary>
/// Sends the email.
/// </summary>
/// <param name="emailAddresses">A string array of email addresses to send.</param>
/// <param name="senderEmailAddress">The sender's email address</param>
/// <param name="grid">The grid.</param>
/// <returns>Number of email's sent</returns>
/// <remarks>This asynchronous method are logged at Windows EventLog (Application category) if any error. </remarks>
public static int SendEmail(string[] emailAddresses, string senderEmailAddress, Grid grid)
{
StringBuilder sb = new StringBuilder(string.Empty);
sb.Append("<table width=\"100%\" cellpadding=\"0\" cellspacing=\"0\">");
// Render header for rows if more then 1 row.
if (grid.Rows.Count > 1)
{
sb.Append("<tr>");
foreach (Column column in grid.Columns)
{
if (column.Visibility == Visibility.Both || column.Visibility == Visibility.Grid)
{
sb.AppendFormat("<td>{0}</td>", column.Title);
}
}
sb.Append("</tr>");
}
Mail wgmail = new Mail();
ArrayList attachments = new ArrayList();
// Render column foreach row if one row, else render all columns in one row.
foreach (Row gridrow in grid.Rows)
{
if (gridrow == null)
{
continue;
}
if (grid.Rows.Count > 1)
{
sb.Append("<tr>");
}
foreach (Column column in gridrow.Columns)
{
if (column.Visibility == Visibility.None || column.ColumnType == ColumnType.Chart ||
column.ColumnType == ColumnType.SystemColumn)
{
continue;
}
if (column.ColumnType == ColumnType.File && grid.Rows.Count == 1)
{
File attachcolumn = (File)column;
Attachment attachfile =
new Attachment(attachcolumn.AbsoluteDirectoryFileName(gridrow[column.ColumnId]).Replace("NULL", grid.InternalId),
"UUEncode");
attachments.Add(attachfile);
continue;
}
object value = gridrow[column.ColumnId].Value;
if (column.ColumnType == ColumnType.ColumnTemplate)
{
if (grid.DisplayView == DisplayView.Detail && ((ColumnTemplate)column).DetailViewTemplate != null)
{
value = ((ColumnTemplate)column).RenderTemplate(gridrow[column.ColumnId]);
}
else if (grid.DisplayView == DisplayView.Grid && ((ColumnTemplate)column).GridViewTemplate != null)
{
value = ((ColumnTemplate)column).RenderTemplate(gridrow[column.ColumnId]);
}
}
switch (column.ColumnType)
{
case ColumnType.Foreignkey:
{
RowCollection row = ((Foreignkey)column).Table.Rows;
for (int i = 0; i < row.Count; i++)
{
if (row[i][column.ColumnId].Value == null ||
row[i].PrimaryKeyUpdateValues != row[i][column.ColumnId].Value.ToString())
{
continue;
}
value =
Foreignkey.BuildDisplayText(i,
((Foreignkey)column).ValueColumn,
((Foreignkey)column).Table);
break;
}
}
break;
case ColumnType.ManyToMany:
{
value = string.Empty;
ManyToManyCollection items = ((ManyToMany)column).LoadItems(gridrow[column.ColumnId]);
for (int i = 0; i < items.Count; i++)
{
if (items[i].Checked)
{
if (value == null)
{
value = items[i].DisplayText;
}
else
{
value += string.Format(" , {0}", items[i].DisplayText);
}
}
}
}
break;
}
if ((column.Visibility == Visibility.Both || column.Visibility == Visibility.Grid) &&
grid.Rows.Count > 1)
{
sb.AppendFormat("<td>{0}</td>", value);
}
else if ((column.Visibility == Visibility.Both || column.Visibility == Visibility.Detail) &&
grid.Rows.Count == 1)
{
if (!column.HideDetailTitle)
{
sb.AppendFormat("<tr><td><b>{0}</b></td></tr><tr><td>{1}</td></tr>", column.Title, value);
}
else
{
sb.AppendFormat("<tr><td></td></tr><tr><td>{0}</td></tr>", value);
}
}
}
sb.Append("</tr>");
}
sb.Append("</table>");
string smtpserver = GridConfig.Get("WGSMTPSERVER", (string)null);
if (grid.MailForm.SmtpServer != null)
{
smtpserver = grid.MailForm.SmtpServer;
}
string body = sb.ToString();
int sentemails = 0;
foreach (string emailaddress in emailAddresses)
{
if (!ValidEmail(emailaddress))
{
continue;
}
sentemails++;
MailMessage message = new MailMessage(new MailAddress(emailaddress), new MailAddress(emailaddress));
for (int i = 0; i < attachments.Count; i++)
{
message.Attachments.Add((Attachment)attachments[i]);
}
message.IsBodyHtml = true;
message.From = string.IsNullOrEmpty(senderEmailAddress) == false
? new MailAddress(senderEmailAddress)
: new MailAddress(emailaddress);
message.Subject = grid.MailForm.Subject ?? grid.Title;
message.Body = HttpUtility.HtmlDecode(body);
wgmail.SendEmail(message, smtpserver);
}
return(sentemails);
}
private static void LoadForeignKeys(Table table)
{
if (table.DataSourceId == null)
{
return;
}
//return;
if (table.m_Grid.Trace.IsTracing)
{
table.m_Grid.Trace.Trace("LoadForeignKeys()");
}
OleDbConnection conn = new OleDbConnection(table.ConnectionString);
conn.Open();
DataTable fks = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys,
new Object[] { null, null, null, null });
conn.Close();
if (table.m_Grid.Debug)
{
table.m_Grid.m_DebugString.Append("<b>OleDB.LoadForeignKeys</b><br/>");
}
for (int i = 0; i < fks.Rows.Count; i++)
{
string fkDataSource = fks.Rows[i]["FK_TABLE_NAME"] as string;
string fkColumnId = fks.Rows[i]["FK_COLUMN_NAME"] as string;
if (String.Compare(fkDataSource, table.DataSourceId, true) != 0)
{
continue;
}
// is there any column with this name?
Foreignkey column = new Foreignkey(fkColumnId, table);
if (table.Columns.GetIndex(fkColumnId) > -1)
{
if (table.Columns[fkColumnId].IsCreatedByWebGrid == false &&
table.Columns[fkColumnId].ColumnType != ColumnType.Foreignkey)
{
continue;
}
Column oldcolumn = table.Columns[fkColumnId];
table.Columns[fkColumnId] = oldcolumn.CopyTo(column);
table.Columns[fkColumnId].m_ColumnType = ColumnType.Foreignkey;
table.Columns[fkColumnId].GridAlign = HorizontalPosition.Left;
if (table.Columns[fkColumnId].m_Searchable == null)
{
table.Columns[fkColumnId].m_Searchable = true;
}
if (table.Columns[fkColumnId].m_Sortable == null)
{
table.Columns[fkColumnId].m_Sortable = true;
}
}
else
{
table.Columns.Add(column);
column.ColumnId = fkColumnId;
}
if (column.ConnectionString == null)
{
column.ConnectionString = table.ConnectionString;
}
if (column.m_DataSourceId == null)
{
column.m_DataSourceId = fks.Rows[i]["PK_TABLE_NAME"] as string;
}
column.m_SaveValueToViewState = true;
}
}
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();
}
private static void LoadForeignKeys(Table table)
{
if (table.DataSourceId == null)
return;
//return;
if (table.m_Grid.Trace.IsTracing) table.m_Grid.Trace.Trace("LoadForeignKeys()");
OleDbConnection conn = new OleDbConnection(table.ConnectionString);
conn.Open();
DataTable fks = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys,
new Object[] { null, null, null, null });
conn.Close();
if (table.m_Grid.Debug)
table.m_Grid.m_DebugString.Append("<b>OleDB.LoadForeignKeys</b><br/>");
for (int i = 0; i < fks.Rows.Count; i++)
{
string fkDataSource = fks.Rows[i]["FK_TABLE_NAME"] as string;
string fkColumnId = fks.Rows[i]["FK_COLUMN_NAME"] as string;
if (String.Compare(fkDataSource, table.DataSourceId, true) != 0)
continue;
// is there any column with this name?
Foreignkey column = new Foreignkey(fkColumnId, table);
if (table.Columns.GetIndex(fkColumnId) > -1)
{
if (table.Columns[fkColumnId].IsCreatedByWebGrid == false &&
table.Columns[fkColumnId].ColumnType != ColumnType.Foreignkey)
continue;
Column oldcolumn = table.Columns[fkColumnId];
table.Columns[fkColumnId] = oldcolumn.CopyTo(column);
table.Columns[fkColumnId].m_ColumnType = ColumnType.Foreignkey;
table.Columns[fkColumnId].GridAlign = HorizontalPosition.Left;
if (table.Columns[fkColumnId].m_Searchable == null)
table.Columns[fkColumnId].m_Searchable = true;
if (table.Columns[fkColumnId].m_Sortable == null)
table.Columns[fkColumnId].m_Sortable = true;
}
else
{
table.Columns.Add(column);
column.ColumnId = fkColumnId;
}
if (column.ConnectionString == null)
column.ConnectionString = table.ConnectionString;
if (column.m_DataSourceId == null)
column.m_DataSourceId = fks.Rows[i]["PK_TABLE_NAME"] as string;
column.m_SaveValueToViewState = true;
}
}
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();
}
