Make your ASP.NET application install it's own Database

An easy way to make your ASP.NET application offer to install the database it is supposed to use, as directed by the user, when it is first requested.

I am in the process of developing an ASP.NET app that creates and manages content for web site owners, and it is likely that it will be sold for profit, so I need to make it "idiot proof". One of the things that needs to be done with many ASP.NET - based applications is that when it is first run, it must ask the user some questions and have the app save these entries in its configuration file and then create the database for itself -- as directed by the user. If you want people to like you and buy your product, you cannot assume anything - you have to make it so easy to use and install that you hardly ever get support questions.

There are a number of Portal-type applications that offer this feature. Some of them get very complex with it. Rather than getting into a big library-based "setup" feature, I decided to roll my own and in keeping with my credo of following Einstein's famous quote, "Make everything as simple as possible, but not simpler" I believe I have captured the essence of a simple and highly manageable "installation" page that does the trick and is easily customizable.

First, here's my logic, and then I'll share how I did it:

1) When the app is first requested, in the Default.aspx page, we need to be able to quickly test if the app has been "installed" and the database and connection string config sections set up. If not, we need to bring the user to the installation page. This code can be left there since it is pretty fast, and if the app is moved, it would need to run again anyway.

2) Basic installation should consist of nothing more than asking the user to supply a server, database, user id, and password, and the installation code should "take care of the rest". The user only needs to supply an existing database. We want to do this since usually they already have a single database to work with if on shared hosting, and if they aren't on shared hosting, they can always create a new one.

3) Once installation is complete, the user would automatically be directed to the main page again so they can immediately start using the product.

With that in mind, let's take a look at some sample code with comments. At the end, you can download the sample solution, which installs a US Zipcode database (only the first few hundred rows) including a stored proc.

First, in the Default Page I have this code:

 string connectionString = "";

        protected void Page_Load(object sender, EventArgs e)

        {

            CheckInstalled();

        }

 

        private void CheckInstalled()

        {

        connectionString = ConfigurationManager.AppSettings["connectionString"];

            if (connectionString == null)

                Server.Transfer("install/install.aspx");

            object o = null;

            try

            {

            o = SqlHelper.ExecuteScalar(connectionString, CommandType.Text,
                    "Select Count(*) from zipcodes");

            }

            catch

            {

                Server.Transfer("install/install.aspx");

 

            }

            if (o == null)

                Server.Transfer("install/install.aspx");

OK, so the code first checks to see if there is a connectionString element in the appSettings section of the web.config. If it is null, we can transfer the user to the installation page immediately. If not null, we then use it to perform a simple operation that - if the database were installed - would return a value. In this case, to keep it simple, I just ask for a count of the rows in the zipcodes table.

If there is an exception or the "o" scalar object return value is null, I also know that we aren't installed so again, we transfer over to the install page.

 

If everything checks out, no transfers, and the user is left on the Default page to use the application. Simple! Now we get into the Install.aspx page:


protected void Page_Load(object sender, EventArgs e)

        {

            if (Request.QueryString["server"] != null)

            {

                string server = Request.QueryString["server"];

                string database = Request.QueryString["database"];

                string uid = Request.QueryString["uid"];

                string pwd = Request.QueryString["pwd"];

                InstallDatabase(server, database, uid, pwd);

            }

        }

 

        private void InstallDatabase(string server, string database, string uid, string pwd)

        {

            String connectionString = "server=" + server + ";database=" + database +

                ";uid=" + uid + ";pwd=" + pwd;

            FileStream fs = new FileStream(Server.MapPath("install.sql"),FileMode.Open);

            byte[] b = new byte[(int)fs.Length];

            fs.Read(b,0,b.Length );

            fs.Close();

            string s = System.Text.Encoding.ASCII.GetString(b);

            s = "USE " +database + ";" +s;

            string[] sCommands =s.Split(';');

            int numBooBoos = 0;

            lblMessage.Text = "The Following Errors occurred:<BR>";

            foreach (string sCmd in sCommands)

            {

                try{

                SqlHelper.ExecuteNonQuery(connectionString,CommandType.Text,sCmd);

                }

                catch(Exception ex)

                {

                    numBooBoos++;

                    lblMessage.Text+=ex.Message+"<BR>";

 

                }

            }

            if (numBooBoos == 0)

            {

                Response.Redirect("../default.aspx");

            } 

        }

 

        protected void Button1_Click(object sender, EventArgs e)

        {

        AppConfig cfg = new AppConfig();

        string connectionString ="server="+this.txtServer.Text +";database="

            +this.txtDatabase.Text +";uid="+this.txtUsername.Text +";pwd="

            +this.txtPassword.Text;

        cfg.SetValue("connectionString", connectionString);

        Server.Transfer("./install.aspx?server=" + this.txtServer.Text + "&database="           

            + this.txtDatabase.Text + "&uid=" + this.txtUsername.Text

            + "&pwd=" + this.txtPassword.Text);       

        }

You can see above that I am looking for Querystring info in Page_Load, that's actually for the "Second pass" and I'll explain it later. When we first get to the install.aspx page there's no querystring, so we get to see a form:

So, the user fills in the Server, Database, a username (uid) and a password (pwd), and they click INSTALL. Now here's what happens:

1) I use my little AppConfig class that I wrote about 3 years ago to load the web.config file and add a connectionString element to the appSettings section. If there is one already, it gets replaced. I don't need to create the appSettings section because the app is distributed with a web.config that already has an empty one. But I could, if I needed to. Now when the web.config is written back to the filesystem, this is going to cause our ASP.NET application to restart, and that's why the Server.Transfer with the queryString items.

I need to put my database info on the querystring because the app hasn't read the new Web.config (yet). So by simply re-requesting the page and looking for querystring items in Page_Load, I know that I am in my "Step Two" phase.

NOTE: If you are going to be saving a lot of elements, or doing it multiple times, I'd suggest you modify my appConfig class to make it so you have to call the "Save" method yourself, when you are really ready. Otherwise, you are going to have your app restarting on you in the middle of forms being filled out, etc.

Step Two is the InstallDatabase Method. Some explanation may be in order here. I have a Sql Script (which you can easily export from Sql Server) but I"ve modified it by replacing all the "GO" statements with semicolons. I do this because after I read in this big string, I want to Split it into separate commands. The primary reason for doing this is that if there are any errors in testing, we can find them more easily.

So the InstallDatabase method reassembles a connection string from the values, and creates a FileStream, from which is loaded the "install.sql" file. We prepend "USE XYZ" (the name of the database) to make sure that we are doing our stuff in the right place. Finally, I simply split the semicolon - delimited strings into a string array and execute each command. Errors (if any) are accumulated into the Label and they are counted. If no errors, I send the user to the main page where she can now begin using the newly installed application. In the case of the "sample" you are taken back to the default.aspx page where you can start looking up zipcode information.

Easy! This is just "The basics" - you can get a lot more fancy - but the purpose here is just to present a concept as simply as possible - but "no simpler".

Download the Visual Studio 2005 Web Application Project that accompanies this article

By Peter Bromberg   Popularity  (4248 Views)