Example #1
0
        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);
        }
Example #2
0
        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);
        }
Example #3
0
        /// <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);
        }
Example #4
0
        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;
            }
        }
Example #5
0
        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();
        }
Example #6
0
        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;
            }
        }
Example #7
0
        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();
        }