Jim's blog | .Net and SQL Server blog

Notify Me When Something Doesn’t Happen

by JBrooks 7. January 2011 14:24

I have 10 clients that each have a process that posts a value to my web service every hour at three minutes after the hour.  I needed to know when a post doesn’t happen for a client. 

The logic of the SQL is that I want to return the last row posted for each Client ID when that last row’s post time is older than 60 minutes (plus 2 minutes for variations).  And I only care if it has happened in the last 4 days.  This is the SQL:

 
declare @intervalMinutes int,
@variationMinutes int,
@activeDays int
 
set @intervalMinutes = 60
set @variationMinutes = 2
set @activeDays = 4
 
 
select 'For Client '+cast(clientId as varchar)+
    ' Last Posting is too old: '+ 
    convert(varchar(5),p.postTime,1)+ ' '+convert(varchar(5),p.postTime,108) 
    as Message,
'Last status was: '+
    status as Details
from Postings p
where p.postTime = 
    (select max(pIn.postTime) 
    from Postings pIn
    where pIn.clientId = p.clientId)
and p.postTime  between 
    dateadd(day,-@activeDays,getdate()) 
    and dateadd(mi,-@intervalMinutes - @variationMinutes,getdate()) 
order by p.postTime
 

So this could return a table that looks like:

image

I now only get notified if there last posting is more than 62 minutes old.  I previously blogged about how to have rows like these show up in an RSS Feed HERE.  The good thing about an RSS Feed is that you can have it on your phone, Outlook and web browser.

Tags:

Having the Result Set of a Stored Proc Sent to You by RSS Feed.

by JBrooks 14. December 2010 12:44

I wanted to monitor one of my system from my desk top and from my phone.  I found a simple solution whereby I can subscribe to the result set of a stored proc by using RSS. So I can have this feed MS Outlook, my phone or my web browser.

First, Visual Studio 2010 makes creating an RSS feed a simple matter that is about 1 page of code.

I simply add an ASPX page to my project and remove most of the markup so it only has 2 lines:

 
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="rss.aspx.cs"  Inherits="RSS.rss"  %>
 
<%@ OutputCache Duration="60" VaryByParam="none" %>

Next the code behind simply calls the stored proc placing the results into a table and then loading up some of the RSS related collections VS2010 gives you. 

 
using System;
using System.Data;
using System.ServiceModel.Syndication;
using System.Web;
using System.Collections.Generic;
using System.Xml;
 
namespace RSS
{
public partial class rss : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
 
        string id = Request.QueryString["id"];
 
        // I don't want just anyone to subscribe, so you have to know the GUID.
        if (id== null || id != "23F14EA1-1B20-443B-9B94-92C4EA4A8099")
            throw new Exception("Guid not reconized");
 
 
        Response.ContentType = "application/atom+xml";
 
        // this gets the data from the database and populates a table.
        DataTable dt = cDB.getFeed();
        SyndicationFeed myFeed = new SyndicationFeed();
 
        myFeed.Title = TextSyndicationContent.CreatePlaintextContent("SampleApp Activity");
        myFeed.Description = TextSyndicationContent
            .CreatePlaintextContent(@"A syndication of the most recently 
                    SampleApp activity including exceptions.");
        myFeed.Links.Add(SyndicationLink.CreateAlternateLink(
            new Uri(GetFullyQualifiedUrl("/rss.aspx"))));
        myFeed.Links.Add(SyndicationLink.CreateSelfLink(
            new Uri(GetFullyQualifiedUrl(Request.RawUrl))));
        myFeed.Copyright = TextSyndicationContent
                        .CreatePlaintextContent("Copyright SampleApp");
        myFeed.Language = "en-us";
 
 
        List<SyndicationItem> feedItems = new List<SyndicationItem>();
        foreach (DataRow dr in dt.Rows)
        {
 
            SyndicationItem item = new SyndicationItem();
            item.Title = TextSyndicationContent.CreatePlaintextContent(dr["title"].ToString());
            SyndicationPerson authInfo = new SyndicationPerson();
            authInfo.Email = "SampleApp@YourDomain.com";
 
            item.Authors.Add(authInfo);
            // RSS feeds can only have one author.
 
            // The stored proc returns different categories of data that I am interested in.
            switch (dr["category"].ToString())
            {
                case "WindFarms":
                case "WindFarms ":
                    item.Links.Add(SyndicationLink.CreateAlternateLink(
                        new Uri(GetFullyQualifiedUrl("/WindFarms.aspx"))));
                    authInfo.Name = "SampleApp WindFarm";
                    break;
 
                case "Exceptions":
                    item.Links.Add(SyndicationLink.CreateAlternateLink(
                        new Uri(GetFullyQualifiedUrl("/ErrorLog.aspx"))));
                    authInfo.Name = "SampleApp Exception";
                    break;
 
                default:
                    authInfo.Name = "SampleApp";
                    break;
 
            }
            item.Summary = TextSyndicationContent.CreatePlaintextContent(
                dr["summary"].ToString());
 
            item.Categories.Add(new SyndicationCategory(dr["category"].ToString()));
            item.PublishDate = DateTime.Parse(dr["pubdate"].ToString());
            item.LastUpdatedTime = item.PublishDate;
            item.Id = item.PublishDate.ToString();
 
            // Add the item to the feed
            feedItems.Add(item);
        }
 
 
        myFeed.Items = feedItems;
 
 
        XmlWriter feedWriter = XmlWriter.Create(Response.OutputStream);
 
        // Use Atom 1.0 
        Atom10FeedFormatter atomFormatter = new Atom10FeedFormatter(myFeed);
        atomFormatter.WriteTo(feedWriter);
 
        feedWriter.Close();
 
    }
 
    private string GetFullyQualifiedUrl(string s)
    {
        Uri u = new Uri(HttpContext.Current.Request.Url, s);
        return u.ToString();
 
    }
 
}
}

 

To have this feed my Outlook RSS folder I just need to right click “RSS Feeds” and select “Add a New RSS Feed…”. 

AddFeedMenu

Then enter the URL of my RSS feed.   Don’t forget to add the GUID at the end with     ?id=23F14EA1-1B20-443B-9B94-92C4EA4A8099

AddFeed

If you site uses authentication in your site you will have to turn it off for the rss.aspx page.  To do this you would add an entry in your web config file:

 
<location path="rss.aspx">
    <system.web>
        <authorization>
            <allow users="*"/>
        </authorization>
    </system.web>
</location>

You should now have a folder in Outlook that will get populated by the feed.

Tags:

ASP.Net | Development

Logging Exceptions and the Commands that Caused Them.

by jbrooks 14. December 2010 12:12

For a long time I’ve been logging all of my exceptions to a table that looks like:

ExceptionTable

Now for exceptions that were generated by the database I want to also log the exact database call that caused the exception.  It will make debugging go a lot faster.  For this I have a new table:

ExceptionCmdTable

This will get the exceptionId from the first table and the a string that caused the exception.  Something like:

saveSomething @id=100, @UserName='Jakey',  @IPAddress='100.100.100.100',  @editBy='JBrooks'

So the first part of logging the Exception is two methods on my base class.

 
public void LogException(Exception ex)
{
    LogException(ex, null);
}
 
 
public void LogExceptionx(Exception ex, SqlCommand objBadCmd)
{
    int id = 0;
 
    if (this._WritingErrorLog == true)
    {
        // don't want to get in an infinite loop.
        return;
    }
    else
    {
        this._WritingErrorLog = true;
    }
 
 
    try
    {
 
        SqlCommand objCmd = this.GetNewCmd("dbo.insertException");
 
        string message = "";
 
        // Get the userId of the user that got the error.
        if (System.Web.HttpContext.Current.Request.Cookies["UID"] != null)
            message = System.Web.HttpContext.Current.Request.Cookies["UID"].Value + "|";
 
        // Add the command that gave the error.
        if (objBadCmd != null && !string.IsNullOrEmpty(objBadCmd.CommandText))
            message += objBadCmd.CommandText + "|";
 
 
        message += ex.ToString();
 
        if (message.Length > 8000)
            message = message.Substring(0, 7999);
 
 
        objCmd.Parameters.Add("@Message", SqlDbType.VarChar, 8000).Value = message;
 
        objCmd.Parameters.Add("@StackTrace", SqlDbType.VarChar, 8000).Value =
            ((ex.StackTrace == null) ? "no stack trace." : ex.StackTrace);
 
        //insert the exception and get the new ID.
        id = this.ExecuteScalarInt(objCmd);
 
        if (id > 0 && objBadCmd != null)
            insertExceptionCmd(id, objBadCmd);
 
    }
    catch (Exception ExLogException)
    {
        this.LogExceptionToFile(ex);
        this.LogExceptionToFile(ExLogException);
    }
 
    this._WritingErrorLog = false;
 
}

Now the part that generates the command string is another method that gets called be the method above:

 
private string getCmdString(SqlCommand objBadCmd)
{
 
    if (objBadCmd == null)
        return string.Empty;
 
    if (objBadCmd.Parameters == null || objBadCmd.Parameters.Count == 0)
        return objBadCmd.CommandText;
 
    StringBuilder sb = new StringBuilder();
 
    try
    {
        sb.Append(objBadCmd.CommandText+" ");
 
        for (int i = 0; i < objBadCmd.Parameters.Count; i++)
        {
            SqlParameter p = objBadCmd.Parameters[i];
 
            if (i > 0)
                sb.Append(", ");
 
            sb.Append(p.ParameterName + "=");
 
            if (p.Value == null)
                sb.Append("null");
            else
            {
                switch (objBadCmd.Parameters[i].SqlDbType)
                {
                    case SqlDbType.BigInt:
                    case SqlDbType.Int:
                    case SqlDbType.Float:
                    case SqlDbType.Decimal:
                    case SqlDbType.SmallInt:
                    case SqlDbType.Money:
                    case SqlDbType.Image:
                    case SqlDbType.Real:
                    case SqlDbType.SmallMoney:
                    case SqlDbType.TinyInt:
                    case SqlDbType.DateTimeOffset:
                        sb.Append(p.Value.ToString());
                        break;
 
                    default:
                        sb.Append("'" + p.Value.ToString() + "'");
                        break;
 
                }
            }
        }
    }
    catch (Exception ex)
    {
        return "getCmdString created its own error processing:  " + 
            sb.ToString() + "      +++++" + ex.ToString().Substring(0, 8000);
    }
 
    return sb.ToString();
}

So now I have a complete logging of exception in my applications and it is a simple matter to make a page where I can view them, or make an RSS feed where they are sent to me.

Tags:

Development | SQL | ASP.Net

Change Connection String Password Programmatically

by jbrooks 6. December 2010 13:07

On one of our projects we have a requirement that we have to change our passwords every 90 day.  So I created a page where I can just click a button and it will generate a password, change the password on the database for the user in the connection string and then change the password in the web.config.

The page just has a button that say “Change Password” and a label called lblMessage to show the results.

The code behind just calls the class that does all of the work and then shows a message:

 
 
const string CONNSTRINGNAME = "SampleAppConnString";
const string WEBCONFIGFILE = "~/Web.Config";
 
protected void btnChangePassword_Click(object sender, EventArgs e)
{
 
if (cConnStringPasswordChanger.ChangePassword(HttpContext.Current.Server.MapPath(WEBCONFIGFILE), 
        CONNSTRINGNAME))
    this.lblMessage.Text = "Password has been changed";
else
    this.lblMessage.Text = "Password has NOT been changed";
 
}

Now for the class that does all of the work:

 
public static class cConnStringPasswordChanger
{
 
    public static bool ChangePassword(string webConfig, string ConnStringName)
    {
        return ChangePassword(webConfig, ConnStringName, 
                Guid.NewGuid().ToString().Replace("-", "").Substring(0, 9));
    }
 
 
    // 1. Open the web.config.
    // 2. Change the connString to have the new password.
    // 3. Change the password on the database.
    // 4. Commit the web.config change.
    public static bool ChangePassword(string webConfig, string ConnStringName, 
        string newPassword)
    {
 
        bool changed = false;
        string oldConnString;
 
        FileInfo fi = new FileInfo(webConfig);
 
        if (fi.IsReadOnly)
        {
            File.SetAttributes(webConfig, FileAttributes.Normal);
        }
 
        fi = null;
 
        XmlDocument cfgDoc = new XmlDocument();
        cfgDoc.Load(webConfig);
 
        XmlNode connNode = cfgDoc.SelectSingleNode("//connectionStrings");
 
        XmlNode myNode = connNode.SelectSingleNode("//add[@name='" + ConnStringName + "']");
        oldConnString = myNode.Attributes["connectionString"].Value;
 
        string oldPassword = getValue(oldConnString, "Password");
 
        string newConnString = oldConnString.Replace(oldPassword, newPassword);
 
        myNode.Attributes["connectionString"].Value = newConnString;
 
        string userId = getValue(newConnString, "User Id");
 
        XmlTextWriter writer = new XmlTextWriter(webConfig, null);
        writer.Formatting = Formatting.Indented;
 
        // last possible second change it on the database.
        changePassword(oldConnString, userId, oldPassword, newPassword);
        try
        {
            cfgDoc.WriteTo(writer);
            writer.Flush();
            writer.Close();
            changed = true;
        }
        catch (Exception ex)
        {
            // error saving web.config change, so change it back on the database.
            changePassword(newConnString, userId, newPassword, oldPassword);
            throw;
        }
 
        writer = null;
        cfgDoc = null;
 
        return changed;
    }
 
    // This function is passed a connection string like:
    // "data source=.\SQLEXPRESS;Initial Catalog=MyDb;User Id=McUser;Password=c99c0472e;"
    // and a partName like "User Id" or "Password".
    // it returns the value for that partName.
    private static string getValue(string connString, string partName)
    {
        int partStart = connString.ToLower().IndexOf(partName.ToLower());
        int partEndSemi; 
        int partEndQuote;
            
 
        if (partStart > -1)
            partStart += partName.Length + 1;
        else
            throw new Exception(partName + " not found in connection string");
 
        partEndSemi = connString.Substring(partStart).IndexOf(";");
        partEndQuote = connString.Substring(partStart).IndexOf("\"");
            
 
        if (partEndQuote == -1)
            partEndQuote = connString.Length - partStart - 1;
 
        if (partEndSemi == -1)
            partEndSemi = connString.Length - partStart - 1;
                        
        return connString.Substring(partStart, Math.Min(partEndQuote, partEndSemi));
    }
 
    // Call the database to change the password.
    private static bool changePassword(string connString, string loginName, 
        string oldPassword, string newPassword)
    {
        bool changed = false;
 
        using (SqlConnection connection = new SqlConnection(connString))
        {
            SqlCommand command = new SqlCommand("dbo.sp_password", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@loginame", SqlDbType.NVarChar, 128).Value = loginName;
            command.Parameters.Add("@old", SqlDbType.NVarChar, 128).Value = oldPassword;
            command.Parameters.Add("@new", SqlDbType.NVarChar, 128).Value = newPassword;
            command.Connection.Open();
            command.ExecuteNonQuery();
            changed = true;
        }
 
 
        return changed;
 
    }
}
 

Tags:

ASP.Net | Development | SQL

Get a Reference to MainPage in a Silverlight Application

by JBrooks 30. November 2010 08:57

Once the user was logged in I wanted to change the menu options that are shown.  To do this I had to get a reference to MainPage.  Here is the code for that:

 
 
    MainPage m;
 
    if (Application.Current.RootVisual.GetType().Name == "BusyIndicator")
        m = (MainPage)((BusyIndicator)Application.Current.RootVisual).Content;
    else
        m = (MainPage)Application.Current.RootVisual;

The code below was a PREVIOUS TRY, but didn’t work because sometimes I would get an exception because the first Parent was null.  Didn’t have time to look into this, and thought the code had some value so I left it here for now.

========= PREVIOUS TRY ============================================================

First I created a static method in the App class that walks up the hierarchy of parents until it finds a match.  This can be used for more than just finding MainPage.  Everything in the hierarchy should be derived from the FrameworkElement class.

 
public static FrameworkElement GetParentByName(FrameworkElement currentPage, string ParentName)
        {
 
            FrameworkElement fe = (FrameworkElement)currentPage.Parent;
 
            // Walk your way up the chain of Parents until we get a match
            while(fe.GetType().Name != ParentName)
                fe = (FrameworkElement)fe.Parent;
 
            return fe;
 
        }

Then in the page Home I have the following code that calls that method.

 
private void Page_LayoutUpdated(object sender, System.EventArgs e)
{
    if (WebContext.Current.User.IsAuthenticated)
    {
        MainPage m = (MainPage)App.GetParentByName(this, "MainPage");
 
        m.LinkResetPassword.Visibility = System.Windows.Visibility.Collapsed;
        m.LinkChangePassword.Visibility = System.Windows.Visibility.Visible;
 
    }     
}

Tags:

Silverlight

Data Access Layer Architecture

by JBrooks 23. November 2010 10:59

I wanted to document how we do our DAL.  First, performance is important so we use ADO.NET instead of the Entity Framework, NHibernate, etc.   Second, in our main project we have 1,743 stored procs  and counting, so a stored proc centered approach is necessary. 

So over the years I have reduced it down so that the following is possible:

 
protected void Page_Load(object sender, EventArgs e)
{
        if (!Page.IsPostBack)
        {
            this.GridView1.DataSource = cApp.DB.getIPAddresses();
            this.GridView1.DataBind();
        }
    }
}

Normally you would have a business layer in there, but you get the idea from this sample.  Encapsulated in that one call is the logic to get the connection string, open and close connections, log exceptions, etc. 

Here is how access to the method works.  We have a central class called cApp that contains a lot of stuff having to do with the application in general.  One of those items is the database object.

 
using System.Configuration;
 
namespace SampleApp
{
    public sealed class cApp
    {
 
        static readonly SampleApp.cDB _cDB = new SampleApp.cDB(
            ConfigurationManager.ConnectionStrings["SampleAppConnString"].ToString());
        
        public static cDB DB
        {
            get
            {
                return _cDB;
            }
        }
       
 
        static cApp()
        { }
    }
}

 

The thing to note here is that there is only every one database object, it is created in a thread safe way, and it stays around for the full length of the application.  Since the applications that I develop are almost always data driven applications I didn’t think it made much sense to create,destroy and garbage collect a data object for every request.  Nothing performs faster than the work that you don’t do.

So what does this cDB class look like?   Here is a sample with only a few methods.

 
using System;
using System.Data;
using System.Data.SqlClient;
 
namespace SampleApp
{
    public class cDB : DBLib.cDBBase
    {
        public cDB(string ConnString)
            : base(ConnString)
        { }
 
        public cDB()
            : base()
        { }
 
 
        public DataTable getIPAddresses()
        {
            SqlCommand cmd = GetNewCmd("dbo.getIPAddresses");
            return GetDataTable(cmd);
        }
 
        public void saveIPAddresses(Int32 id, String UserName, String IPAddress, String editBy)
        {
            SqlCommand cmd = GetNewCmd("dbo.saveIPAddresses");
 
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 150).Value = UserName;
            cmd.Parameters.Add("@IPAddress", SqlDbType.VarChar, 50).Value = IPAddress;
            cmd.Parameters.Add("@editBy", SqlDbType.VarChar, 150).Value = editBy;
 
            ExecuteNonQuery(cmd);
        }
 
        public DataTable getExceptions(DateTime exTime)
        {
            SqlCommand cmd = GetNewCmd("dbo.getExceptions");
            cmd.Parameters.Add("@exTime", SqlDbType.DateTime).Value = exTime;
 
            return GetDataTable(cmd);
        }
 
 
    }
}

We follow the pattern where the method name and parameters are the same as the stored procs.   I had previously blog about how I automatically generate the code for these methods based on the stored procs HERE , which really helps when there are a lot of parameters. 

Most of the work is found in the class that the cDB class inherits from, DBLib.cDBBase.  It is a class I use in every project and it has methods for getting DataTables, Integers, GUIDs, etc.  It will log exceptions to a table and then fail over to a text file if needed.  

You can download a sample application that has the full source code including cDBBase HERE

Tags:

Development | General

Stripping Out Passwords

by jbrooks 19. November 2010 07:44

I have a page in my ASP.Net application where I show all of my AppSettings, connection strings, etc.  But I didn’t want to show the passwords.  I wanted something like:

server=MyServer;uid=MyUserId;pwd=*******;database=MyDatabase;

So here is the code to strip out the password:

 
 
        private string stripPassword(string connString)
        {
            if (string.IsNullOrEmpty(connString))
                return connString;
 
            int pos = connString.ToLower().IndexOf("pwd");
            if (pos > -1)
                pos += 4;
            else
            {
                pos = connString.ToLower().IndexOf("password");
                if (pos > -1)
                    pos += 9;
                else
                    return connString;
            }
 
            return connString.Substring(0, pos) + "*******" + 
                connString.Substring(pos + connString.Substring(pos).IndexOf(";"));
        }
 

Tags:

ASP.Net | Development

Uploading XML Files to SQL Server

by jbrooks 18. November 2010 13:14

I had a large number of XML files that each contained hourly prices for a lot of different locations.  I needed to get all of the prices for a single location.  I did this by loading all of the files into a table and then querying the table.

First, I needed to change the rights on my database to allow me to run the xp_cmdshell store procedure.

 
USE MASTER
GO
 
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
 
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO
 
USE MyTestDB
GO
 

Then I needed to make the temporary table that would hold my file names and load the file names from the directory.

 
CREATE TABLE #DIR (fileName varchar(100))
 
INSERT INTO #DIR
EXEC master..xp_CmdShell 'DIR C:\RTHourly\*.xml /B'
 

A second table is needed hold the content of my XML files,  it is one row for each file. Note the XML data type for the last column called priceData.

 
create table xmls
(id int identity,
fileName varchar(250),
priceData xml)
 

Now we are ready to loop thru each file name found in the  #DIR table and load it’s contents into the xmls table.

 
DECLARE @fileName AS varchar(250)
DECLARE @fullPathName AS varchar(250)
 
DECLARE file_cursor CURSOR FOR
SELECT fileName
FROM #DIR
WHERE fileName like '%.xml'
 
OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @fileName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @fileName
 
    set @fullPathName = 'C:\RTHourly\'+ @fileName
    
 
    -- this must be dynamic sql b/c file name has to be a literal string for OPENROWSET
 
    exec (
    'INSERT INTO xmls(fileName, priceData)
    SELECT '''+@fileName+''' AS fileName, A.*
    FROM OPENROWSET( BULK '''+@fullPathName+''',SINGLE_CLOB)
    AS A')
 
    FETCH NEXT FROM file_cursor INTO @fileName;
END
 
CLOSE file_cursor;
DEALLOCATE file_cursor;
 

 

At this point all of the files are loaded so I just need to get the data out for my location “AZ”. 

 
select priceData.value('(//PriceGroup/Prices/Price[@location = "AZ"]/Detail/@utcTimepoint)[1]',
        'varchar(150)') as time,
priceData.value('(//PriceGroup/Prices/Price[@location = "AZ"]/Detail/@lmp)[1]',
'real') as price,
fileName
from xmls
order by 1 
 

 

Now let’s turn off the security hole that we created when we started this little project.

 
USE MASTER
GO
 
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO
 
 
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO
 

Tags:

Development | SQL

Grid within a GridView Cell

by JBrooks 8. November 2010 11:04

GridInGrid

I needed to display some parent data with it’s child data on the same row like shown in the image above. I happen to be combining a few tables together into a new table (called dtPlan) and then binding the GridView to that new table.  So this was my approach while moving the data into the dtPlan table.  dtRampRates is the child table to the data in dtResults.

 
DataView dvRampRates = new DataView(dtRampRates);
dvRampRates.Sort = "parentId, id";
StringBuilder sb = new StringBuilder();
 
for (int indexRow = 0; indexRow < dtResults.Rows.Count; indexRow++)
{
    DataRow drPlan = dtPlan.NewRow();
    drPlan["c0"] = planHour;
    drPlan["c1"] = dtResults.Rows[indexRow]["ResourceStatus"].ToString();
    drPlan["c2"] = dtResults.Rows[indexRow]["MinEconomicMW"].ToString();
    drPlan["c3"] = dtResults.Rows[indexRow]["MaxEconomicMW"].ToString();
 
    dvRampRates.RowFilter = "parentId=" + dtResults.Rows[indexRow]["id"].ToString();
    sb.Length = 0;
    sb.Append("<table cellspacing='0' class='RampRate'>");
 
    if (indexRow == 0)
        sb.Append("<tr><td>Limit</td><td>Up</td><td>Down</td></tr>");
 
 
    for (int i = 0; i < dvRampRates.Count; i++)
    {
        sb.Append("<tr><td>");
        sb.Append(dvRampRates[i]["breakpointLimit"].ToString());
        sb.Append("</td>");
 
        sb.Append("<td>");
        sb.Append(dvRampRates[i]["rampRateUp"].ToString());
        sb.Append("</td>");
 
 
        sb.Append("<td>");
        sb.Append(dvRampRates[i]["rampRateDown"].ToString());
        sb.Append("</td></tr>");
    }
    sb.Append("</table>");
 
    drPlan["c4"] = sb.ToString();
    dtPlan.Rows.Add(drPlan);
}
 
gvPlan.DataSource = dtPlan;
gvPlan.DataBind();

The child grid is made in the “for” loop.  Normally you would have this loop in the RowDataBound event for the GridView.  With that approach it is easy to see how most of the code would be the same.

Tags:

ASP.Net | GridView | Development

Using the Ajax Tabs Control as a Menu

by JBrooks 24. October 2010 10:53

I have a regular menu for my site and I wanted to use the Ajax Tabs Control as a submenu when the user was doing point-of-sale (ordering cards that describe the wine).  So when the user was on POS  on the main menu (black background) they would see the submenu as shown below.

Menu

To make this, the first thing I did was create a new web user control and add the Ajax Tabs Control.  This user control is called wcPOSMenu and will be place on each of the 3 ASP.Net pages involved. You can see there isn’t much to the markup below.

 
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="wcPOSMenu.ascx.cs" 
Inherits="ToutonWeb.wcPOSMenu" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<asp:TabContainer ID="TabContainerPOS" runat="server" AutoPostBack="true" BorderWidth="0px"
    Width="926px" Height="12px" CssClass="ajax__tab_xp" ActiveTabIndex="0" 
    OnActiveTabChanged="TabContainerPOS_ActiveTabChanged">
    <asp:TabPanel ID="tpSelect" runat="server" HeaderText="Select POS" BorderWidth="0px">
        <ContentTemplate>
            Enter Description or ItemNo to Search for and Hit Enter. Click a Column Heading
            to Sort by That Column.
        </ContentTemplate>
    </asp:TabPanel>
    <asp:TabPanel ID="tpEdit" runat="server" HeaderText="Edit SKUs" BorderWidth="0px">
        <ContentTemplate>
            Enter a SKU to Add or Edit.
        </ContentTemplate>
    </asp:TabPanel>
    <asp:TabPanel ID="tpSend" runat="server" HeaderText="Send POS" BorderWidth="0px">
        <ContentTemplate>
            Send POS.
        </ContentTemplate>
    </asp:TabPanel>
</asp:TabContainer>

One thing to note is that I couldn’t quickly figure out how to eliminate the tab’s containers border so I just made it a height of 12px and used that area as a subtitle to the rest of the content.

Below is the code behind, there isn’t much to this either.  First you have a user control’s property TabIndex that can be set on each of the parent pages.  So for my example the page for “Edit SKUs” will have this user control on it and it’s TabIndex property will be set to the value of 1. 

The page Page_Load event just uses that property to set the current tab.

The TabContainerPOS_ActiveTabChanged just redirects the user to the correct page whenever they change the tab.  To the user is appears they are selecting a menu option, but to the tab control they are actually changing the active tab.

 

 
using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
 
namespace ToutonWeb
{
    public partial class wcPOSMenu : System.Web.UI.UserControl
    {
        private int _tabIndex;
        public int TabIndex
        {
            get { return _tabIndex; }
            set { _tabIndex = value; }
        }
 
 
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
                this.TabContainerPOS.ActiveTabIndex = _tabIndex;
        
        }
 
 
        protected void TabContainerPOS_ActiveTabChanged(object sender, EventArgs e)
        {
            switch (TabContainerPOS.ActiveTabIndex)
            {
                case 0:
                    Response.Redirect("~/frmPOS.aspx");
                    break;
                case 1:
                    Response.Redirect("~/frmPOSEdit.aspx");
                    break;
 
                case 2:
                    Response.Redirect("~/frmPOSSend.aspx");
                    break;
 
            }
 
        }
    }
}

On each of the parent pages I just need to add 2 lines.  The first is to register the user control,  line 5 below. The second is line 9 below.  Note the TabIndex property of the user control being set in line 9.

   1: <%@ Page Title="" Language="C#" MasterPageFile="~/Site1.Master" AutoEventWireup="true"
   2:     CodeBehind="frmPOSEdit.aspx.cs" Inherits="ToutonWeb.frmPOSEdit" %>
   3:  
   4: <%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp"%>
   5: <%@ Register Src="wcPOSMenu.ascx" TagName="wcPOSMenu" TagPrefix="uc1" %>
   6: <asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
   7: </asp:Content>
   8: <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
   9:     <uc1:wcPOSMenu ID="wcPOSMenu1" runat="server" TabIndex="1" />

That is it.

Tags:

ASP.Net | Development

One Script

OneScript Continuous Integration for you database Scripts in version control are automatically combined into a release script.

RecentPosts