Sunday, December 06, 2009

Using SQL Connection Dialog

This article shows how to include the SQL Connection Dialog into your code in C#. There is quite a good article with many google hits on this subject at:
http://www.codeproject.com/KB/vb/SQL_Connection_Dialog.aspx?msg=2320576
However, this is written in VB.

There is also a less well known article on this subject written in C#.
http://www.gotdotnet.ru/forums/1/94733/451052/#post451052 (in C#)
However, this lacks the Test Connection button and the overall structure was not very clear.


Yet having the two article above and numerouse searches in between, it still took me a few hours to get the SQL Connection Dialog working properly. So I thought I'll publish my own C# code here. Although similar to the two references, I were a few key points which I need to change to make it work. Also I hope to show that my code structure is clearer in the relationship between the form and the SQL Connection dialog.



There are 3 separate code files below and I've developed in Visual Studio 2005. First Create a C# Windows forms project.
1. Program.cs - this is the executable that calls on Form1 class. This is automatically generated.

2. Form1.cs - this is also autogenerated. But I added my own button called btnSQLconnect (using drag and drop to drag the Button from the Toolbox).
In Form1.cs, I also added the method btnSQLconnect_Click_1 that will be called when the button is pressed.
Basically the idea is you have your own form, and you have a button on that form to allow the user to click and that will open up the SQL Connection dialog.

3. SQLconnectForm.cs - this is the main thing to add to make use of the SQL Connection Dialog. The rest of this article is devoted to explaining this in greater detail.

Firstly, note the SQL Connection Dialog, need to be a Windows Form by itself, hence the creation of the SQLconnectForm.cs class (which inherits from Form) to hold it.

Secondly, we need to crucial components to make the SQLConnectionForm to become the actual SQL Connection Dialog:
SqlFileConnectionProperties cp;
SqlConnectionUIControl uic;

The cp contain the connection properties to connect to the DB. The uic is the .Net built-in SQL Connection Dialog.

A class member called sConnectionString is defined as the get / set method for the actual connection string stored within cp.

The initialization of the SQL Connection Dialog is done in the constructor of the SQLconnectionForm class. Initialization includes:
i) Setting values for properties of cp and uic.
ii) Creation of 4 buttons: OK, Cancel, Test Connection, Advanced.
iii) Setting properties (including position) of the 4 buttons.

The Advanced and Test Connection button cannot be seen on the visual designer of SQLConnectionForm, but they are definitely there when the program is run.
The OK and Cancel buttons are added in the usual way by dragging them from the Toolbox onto the Form in the Visual Designer.

Each of the 4 buttons also have four methods to handle the event when the buttons are clicked.

The Advanced button clicked event will call another form which has been predefined in the .Net. Hence there is only a few lines of code in the Avanced's click method.

The Test Connection event method is where an attempt is made to open and close the DB to check that the connection is OK. Initially, there was an error like "unable to login user" as it tries to connect to the DB. The solution that worked is to modify the User Instance in the connection string as:
cp.ConnectionStringBuilder["User Instance"] = false;

If you have similar problems, consider commenting out the line above.



Perhaps the most challenging concept the SQL Connection Dialog (at least for me) is that we need to create a blank Form (SQLconnectionForm.cs), then by including the components uic and cp, the form magically becomes the standard Microsoft .Net SQL Connection Dialog.


*********** Program.cs *****************************
using System;
using System.Collections.Generic;
using System.Windows.Forms;

namespace MapDB2Class_GUI
{
static class Program
{
///
/// The main entry point for the application.
///

[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
}

*********** Form.cs **********************
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Data.ConnectionUI;


namespace MapDB2Class_GUI
{
public partial class Form1 : Form
{

public Form1()
{
InitializeComponent();
}

private void btnSQLconnect_Click_1(object sender, EventArgs e)
{
SQLconnectForm sdlg = new SQLconnectForm("Selecting DB");
sdlg.ShowDialog();
}


}
}

*************** SQLconnection.cs *************************
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Data.ConnectionUI;
using System.Data.SqlTypes;
using System.Data.SqlClient;

namespace MapDB2Class_GUI
{
public partial class SQLconnectForm : Form
{

SqlFileConnectionProperties cp;
SqlConnectionUIControl uic;

public string sConnectionString
{
get { return cp.ConnectionStringBuilder.ConnectionString; }
set { cp.ConnectionStringBuilder.ConnectionString = value; }
}


public SQLconnectForm(string sTitle)
{
// This call is a default for Windows Form
InitializeComponent();

this.Text = sTitle; // title of this form
this.AcceptButton = btnOK;
this.CancelButton = btnCancel;


this.Padding = new Padding(5);
uic = new SqlConnectionUIControl();
Button adv = new Button();
Button tst = new Button();

cp = new SqlFileConnectionProperties();
uic.Initialize(cp);
uic.LoadProperties();
uic.Dock = DockStyle.Top;
uic.Parent = this;


this.ClientSize = Size.Add(uic.MinimumSize, new Size(10, adv.Height + 25));
this.MinimumSize = this.Size;

adv.Text = "Advanced";
adv.Dock = DockStyle.None;
adv.Location = new Point(uic.Width - adv.Width, uic.Bottom + 10);
adv.Anchor = AnchorStyles.Right | AnchorStyles.Top;
adv.Click += new EventHandler(Advanced_Click);
adv.Parent = this;

tst.Text = "Test Connection";
tst.Width = 100;
tst.Dock = DockStyle.None;
tst.Location = new Point((uic.Width - tst.Width) - adv.Width - 10, uic.Bottom + 10);
tst.Anchor = AnchorStyles.Right | AnchorStyles.Top;
tst.Click += new EventHandler(Test_Clicked);
tst.Parent = this;



}

void Test_Clicked(object sender, EventArgs e)
{
cp.ConnectionStringBuilder["User Instance"] = false; // Need to change this to false, otherwise cannot login user.
SqlConnection conn = new SqlConnection(cp.ConnectionStringBuilder.ConnectionString);
try
{
conn.Open();
MessageBox.Show("Test Connection Succeeded.", "Connected", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
catch (Exception exc)
{
MessageBox.Show("Test Connection Failed.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally {
try { conn.Close(); }
catch (Exception exc) { }
}
}

void Advanced_Click(object sender, EventArgs e)
{
Form frm = new Form();

PropertyGrid pg = new PropertyGrid();
pg.SelectedObject = cp;
pg.Dock = DockStyle.Fill;
pg.Parent = frm;

frm.ShowDialog();
}

private void btnOK_Click(object sender, EventArgs e)
{
this.DialogResult = DialogResult.OK;
this.Close();
}

private void btnCancel_Click(object sender, EventArgs e)
{
this.DialogResult = DialogResult.Cancel;
this.Close();
}

}
}