Part of the difficulty of installing custom database driven sites onto remote servers is the unpredictability of the server configuration. I am trying to ease that pain by automating as much as possible the initial configuration.
One major hurdle is coming up with the correct connection string to SQL Server. I am attempting to make this a easy process by allowing the user to select the data source and database from drop down menu's.
I thought I'd share a handful of code snippets that makes this possible:
1 private DataTable GetDataSources()
2 {
3 DbProviderFactory factory =
4 DbProviderFactories.GetFactory
5 ("System.Data.SqlClient");
6
7 DbDataSourceEnumerator dataSourceEnumerator =
8 factory.CreateDataSourceEnumerator();
9
10 if (dataSourceEnumerator != null)
11 {
12 DataTable dataSources = dataSourceEnumerator.GetDataSources();
13 return dataSources;
14 }
15 else
16 {
17 throw new Exception("Something bad happened");
18 }
19 }
This method returns all available data sources on the local server. Obviously, if the SQL Server is located on a different box, this won't work, so I have implemented a manual entry text box as well.
The method returns a data table that can be bound to a drop down list. However, because you need to concatenate the value of two columns, you cannot use regular data binding, instead use a method similar to this:
1 private void BindDataSource()
2 {
3 DataTable dt = GetDataSources();
4 foreach (DataRow dr in dt.Rows)
5 {
6 ListItem currentItem = new ListItem();
7 currentItem.Value = dr["ServerName"] + "\\" + dr["InstanceName"];
8 currentItem.Text = dr["ServerName"] + "\\" + dr["InstanceName"];
9 ddlDataSources.Items.Add(currentItem);
10 }
11 if (ddlDataSources.Items.Count > 0)
12 {
13 BindDatabaseList();
14 }
15 else
16 {
17 ddlDataSources.Items.Add("No DataSources found.");
18 }
19 }
Once the user has selected the proper data source, I wanted to populate a second drop down list with all available databases. The following snippet creates a list of available databases on the data source:
1 private void BindDatabaseList()
2 {
3 SqlConnectionStringBuilder builder = BuildConnectionString();
4
5 builder.InitialCatalog = "master";
6
7 SqlConnection connection = new SqlConnection(builder.ConnectionString);
8 SqlCommand command = new SqlCommand();
9 SqlDataReader dataReader;
10
11 connection.Open();
12
13 command.CommandText = "SELECT NAME FROM master..sysdatabases ORDER BY NAME ASC";
14 command.Connection = connection;
15 dataReader = command.ExecuteReader();
16
17 ddlDataBase.Items.Clear();
18
19 while (dataReader.Read())
20 {
21 string dbName = (String)dataReader["name"];
22 if (dbName != null)
23 {
24 if (dbName != "master" &&
25 dbName != "msdb" &&
26 dbName != "tempdb" &&
27 dbName != "model")
28 {
29 ddlDataBase.Items.Add(dbName);
30 }
31 }
32 }
33 connection.Close();
34 }
All of these little snippets combine to create a intuitive way to setup the initial database connection. It is a lot of work, but the result is a simple configuration screen.
Normally, I would avoid placing database code directly into front end code, but in the case of a run once installation script, it makes sense. Once the script has ran, the user will have to physically delete it off the server before CodeBlog will run. This is to prevent a malicious person from reconfiguring the database.
0 comments:
Post a Comment