A small foray into ASP.NET development

Here at Youi in our database administration/data platforms/data foundations (we have a few names, depending on who you speak to) we have an intranet site built way back when that we use to maintain our ETL processes. It’s simply a front end for more easily adding records to some admin tables we have, tables which drive the procs that run, the other procs they are dependent on, and mapping of columns from source system to SQL table, among other things.

The old state of things had one development database, where changes were staged, and a live database, where the changes were deployed to once you were happy. Since we recently moved the live database to using source control and Redgate’s SQL Change Automation, I thought it would be better to change the admin site functionality to use that same process - rather than a dev database, use the dev server.

Anyway, that required some rejigging of the funcionality, since currently the connection string is hard-coded for the one database. We have also started using dedicated, rather than shared, development databases, so I wanted a way to dynamically change the connection based on who is using the site. My experience with ASP.NET development is extremely limited, so I needed a bit of help from my boss and from Google. This is the solution I ended up with.

First, I wanted to write a function (or class, or whatver - I still get mixed up with the terminology) that would return a connection string with your personalised database name. I included this is one of the .cs files included in the project:

    public class CustomSQLhelperFunctions
    {
        public static string FindPersonalConnectionString()
        {
            // Change the connection string based on the user
            // Based on the example at https://medium.com/@hiba.eldursi95/how-to-read-write-delete-from-the-web-config-file-3ffbf0dbf4ee
			// DOMAIN1 DOMAIN2 DOMAIN3 are stand-ins for the actual domains we've gone through over the years
            string LoggedInUser = HttpContext.Current.Request.LogonUserIdentity.Name.ToLower().Replace("DOMAIN1\\", "").Replace("DOMAIN2\\", "").Replace("DOMAIN3\\", "");
            AppSettingsReader app = new AppSettingsReader();
            string strConnection = (string)app.GetValue("ConnectionString", typeof(string));
			// Database names have also been changed to protect the innocent
            strConnection = strConnection.Replace("DevDatabaseName", "LiveDatabaseName_" + LoggedInUser);
            return strConnection;
        }
    }
	

As you can see in the comments, I was heavily inspired by this post to get the current user context. For development, we each have a database on the dev server with our username appended, so we don’t get in each other’s way. Then changes are generated, pushed, and deployed to live - usual Change Automation story.

Then, the actual work that is done when you make changes using the site. This code is in several of the .cs files for when you change drop-downs, add procedures to nightly jobs, etc:

//  Get the connection string from the webconfig file.
string strConnection = CustomSQLhelperFunctions.FindPersonalConnectionString();

// Instantiate a new connection object.
con = new SqlConnection(strConnection);

// Check if the connection is closed.
if (con.State == ConnectionState.Closed)
{
	// Open the connection.
	con.Open();
}

// Instantiate a new command object.
cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Admin.StoredProcedureName";

// Add parameters.
cmd.Parameters.Add(new SqlParameter("@ID", intID));
cmd.Parameters.Add(new SqlParameter("@Job", strJob));
cmd.Parameters.Add(new SqlParameter("@Description", strDescription));
cmd.Parameters.Add(new SqlParameter("@Extract", strExtract));
cmd.Parameters.Add(new SqlParameter("@Username", strUsername));
cmd.Parameters.Add(new SqlParameter("@Action", strAction));

// Return the reader.
return cmd.ExecuteReader(CommandBehavior.CloseConnection);

There is more to it, obviously, but this is the meat of it. Thanks for my lack of experience here, this end state took probably 4 hours of work. At least.

It was nice when it finally worked.