I was checking my Old Projects and Like to share a simple example with you. Why not to create a SQL Management Application? We will load available SQL Server instances and perform basic operations. Let’s start.
How we would do this? What is our objective?
Lets be clear what we are expecting from this simple application.
- We will List all SQL Server Instances available in Network into Application.
- Then We will connect to Server using Windows Authentication or SQL Server authentication.
- After successful authentication, we will List All Databases in Instance.
- After Database, we will List All Tables in selected Database.
- After Database, we will list All Columns in Selected table.
- We will place a Create button next to Database, Table list box also.
- After selecting a Column, we provided a Filter (operator) and parameters in box.
- Which will fetch data and display in DataGridView.
Design Screen for connecting to SQL Server Instances
In Servers List combo-box we have to load All List of SQL Servers Instances available and Connect. We have Options for Username and Password and a Checkbox for Integrated Security (Windows Authentication).
Let’s See the Code in Form Load.
Namespace to Include:
using System.Data.Sql;
using System.Data.SqlClient;Now Form_Load Event Code to get SQL Server Instances
private void Form1_Load(object sender, EventArgs e)
{
panel1.Visible = false;
groupBox1.Visible = false;
label9.Visible = true;
label9.TextAlign = ContentAlignment.MiddleCenter;
SqlDataSourceEnumerator SerInstances = SqlDataSourceEnumerator.Instance;
DataTable SerNames = SerInstances.GetDataSources();
for (int i = 0; i <= SerNames.Rows.Count - 1; i++)
{
comboBox1.Items.Add(SerNames.Rows[i][0].ToString()+""+SerNames.Rows[i][1].ToString());
}
}Description of Above Code:
We have placed remaining design in Panel and Groupbox, so we can set that invisible on Form Load. “Please Make a Connection First” is Label9 and we set is Visible and aligned. Now main task is to List SQL Servers on Network.
For this we used SqlDataSourceEnumerator class. In Next Line we take a DataTable and stored ServerInstances into that. Rest is Simple, we added all items into Listbox. Column 0 contains Machine name and Column 1 contains Instance Name. So, we combined that at the Time of Adding to ComboBox Control.
Now code for Connect Button:
private void button1_Click(object sender, EventArgs e)
{
if (checkBox1.Checked == true && textBox1.Text.Trim().Length > 0)
{
MessageBox.Show("Please Uncheck Integrated Security or Clear UserName!", this.Text + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (checkBox1.Checked == false && textBox1.Text.Trim().Length == 0)
{
MessageBox.Show("Please Check Integrated Security or Enter UserName!", this.Text + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (textBox1.Text.Trim().Length > 0 && textBox2.Text.Trim().Length == 0)
{
errorProvider1.SetError(textBox2, "Please enter password for Username = " + textBox1.Text + "!");
}
else if (comboBox1.Text.Trim().Length == 0)
{
MessageBox.Show("Please select Server from List. If You can not see any Instance in Servers Combobox, Contact your Network Administrator!", this.Text + " - Message", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
else
{
errorProvider1.Clear();
if (MessageBox.Show("Connection Successfull!, You are Now Connected to Server: " + comboBox1.Text.ToString() + "!", this.Text + " - Alert", MessageBoxButtons.OK, MessageBoxIcon.Information) == DialogResult.OK)
{
comboBox2.Items.Clear();
if (checkBox1.Checked == true)
{
ConStr = "Data Source = " + comboBox1.Text.ToString() + ";Integrated Security = true;";
}
else
{
ConStr = "Data Source = " + comboBox1.Text.ToString() + ";UID=" + textBox1.Text + ";pwd=" + textBox2.Text + ";";
}
SqlConnection Conexion = new SqlConnection(ConStr);
Conexion.Open();
label9.Visible = false;
panel2.Visible = false;
button2.Visible = true;
panel1.Visible = true;
groupBox1.Visible = true;
DataTable tblDatabases = Conexion.GetSchema("Databases");
for (int i = 0; i <= tblDatabases.Rows.Count - 1; i++)
{
comboBox2.Items.Add(tblDatabases.Rows[i][0].ToString());
}
Conexion.Close();
}
}
}Description of Code:
We Put some Validation and Message box to make some user-friendly environment. After getting Proper Username and password, we make a Connection String and Used Username and password in Connection String. We build a connection, Make Servers List, Username, and password and Connect button invisible and Disconnect button Visible. Then First we loaded all Databases of Selected Instance using Connection.GetSchema() method. How it looks after connecting.
For now, it’s your turn to code something for this. I’ll be back soon with Rest Part of This Program. Second part of this article is here.
Comments are Welcome. Feedback keep the Important Role in Improvement. So, Expecting those from you. Happy Learning! Subscribe to our feeds for regular updates.
