Development

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

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

Passing IDs to Stored Procs Using XML

by jbrooks 15. October 2010 10:58

In the past we would use a Varchar to pass a comma delimited list of IDs to a stored proc. We would then build a big varchar string of the full SQL and run the dynamic SQL.  Starting with SQL Server 2005 you can pass the stored proc XML and do your select without using dynamic SQL.

The stored proc would look something like:

 
create proc [dbo].[getLocationTypes](@locationIds XML,
@typeIds XML=null)
as  
begin  
set nocount on  
 
   
SELECT locationId, typeId
FROM xrefLocationTypes 
WHERE locationId 
IN (SELECT Item.value('.', 'int' )
FROM @locationIDs.nodes('IDList/ID') AS x(Item))
AND (typeId IN
 (SELECT Item.value('.', 'int' )
FROM @typeIds.nodes('IDList/ID') AS x(Item)))
ORDER BY 1, 2
  
end  
 
 
 
And then you would call this like:
 
 
EXECUTE dbo.getLocationTypes '<IDList><ID>1</ID><ID>3</ID></IDList>', 
'<IDList><ID>200</ID><ID>300</ID><ID>400</ID></IDList>'    
 

Tags:

SQL | Development

Changing HTML Attributes on ASP.NET Pages in the Code Behind

by JBrooks 24. September 2010 11:40

Not many developers realize that you can change a plain HTML object’s attributes in the code behind without having to convert it into an ASP.Net control.

So if I have a checkbox like this:

<input type="checkbox" id="cbShowDef" name="cbShowDef"  onclick="javascript: show()" />

I can hide this in the code behind with:

((System.Web.UI.HtmlControls.HtmlInputCheckBox) Form.FindControl("cbShowDef")).Style.Add("display","none");

Tags:

ASP.Net | Development

Displaying a Long Description Column in a GridView

by JBrooks 6. July 2010 20:05

A lot of times you have something like a description column and you want to display it in a GridView.  But since for some of the rows the description column can be very long, it doesn’t always work very well. One solution is where you set the TD’s attribute NOWRAP, then the column runs off to the right for as long as is needed.  The solution I sometimes employ is the following: 

First, here is my sample table:

CREATE TABLE Categories
(CategoryName varchar(20),
Description varchar(1000))
 
And here is the SQL that is going to bind to my GridView:
 
SELECT c.CategoryName,
CASE WHEN LEN(c.Description) > 30
THEN SUBSTRING(c.Description,1,27)+'...'
ELSE c.Description END AS LineDescription,
c.Description AS FullDescription
FROM dbo.Categories c
ORDER BY c.CategoryName

Notice that the Description column is in there twice. Once where it maxes out at 30 characters (the LineDescription column), and the second one that contains the full text (the FullDescription column.) This brings back a table that looks like the following:

CategoryName LineDescription FullDescription
Beverages Soft drinks, coffees, teas,... Soft drinks, coffees, teas, beers, and ales
Condiments Sweet and savory sauces, re... Sweet and savory sauces, relishes, spreads, and seasonings
Confections Desserts, candies, and swee... Desserts, candies, and sweet breads
Dairy Products Cheeses Cheeses
Grains/Cereals Breads, crackers, pasta, an... Breads, crackers, pasta, and cereal
Meat/Poultry Prepared meats Prepared meats
Produce Dried fruit and bean curd Dried fruit and bean curd
Seafood Seaweed and fish Seaweed and fish

 

So to bind this to the GridView we bind the first two columns only.  The first GridView column is bound to the CategoryName data column and the second one is bound to the LineDescription data column with the heading title of “Description”.  On the second GridView column we also click the “Convert this field into a TemplateField” as shown below.

GridViewColumns

 

We save this and then edit the templates for the GridView columns.  For the Description column we edit the ItemTemplate and we bind the ToolTip to the FullDescription data column.

ToolTipBinding

 

Now we have the GridView column that is titled “Description” bound to the LineDescription data column and the tooltip is bound to the FullDescription data column.  We can run this and  see how it works:

GridViewWithToolTip

So now we have a short description in our grid, but the user can easily see the full description in the ToolTip.

Tags:

ASP.Net | GridView | Development

Quickly Finding Missing Objects in Two SQL Server 2005 Databases

by JBrooks 5. July 2010 09:22

Real quick I wanted to know what tables and stored procs were in my ReleaseManagerTest database and not in my ReleaseManager database.  So here is the SQL for that.

select min(DB) as DB, name, type
from
(
select 'ReleaseManagerTest' as DB, name, type 
from ReleaseManagerTest.sys.objects 
 
union all
 
select 'ReleaseManager' as DB, name, type
from ReleaseManager.sys.objects
) A
group by name, type
having count(*) <> 2
 

Tags:

Development | SQL