Wednesday, May 1, 2013

Connection String in Windows Form Application



Introduction


When you want to use SQL database table data in your C# or say windows Form application for displaying, inserting, deleting or for other purpose , then what we do?, The Microsoft provide many of facility to use SQL server database tables data  in your windows form application, I am much honorable to introduce one of them facility that enables you to perform different type of activity on SQL server databases. Now Lets move out one of them facility name as ADO. NET.


So take a little example which helps you to create a connection string in Windows Form Application. First we create a connection in windows form to displaying data into DataGridView. So I simply drag and drop dataGridView from a data control and a button from all windows from control into your Form.

Code 1 (Without app.config file)
Simple code to display data in dataGridView 

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace ConnectionString
{
    public partial class Form1 : Form
    {
        SqlDataAdapter da;
        DataSet ds;
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            da = new SqlDataAdapter("select * from emp", "Data Source=.;Initial Catalog=sharad;User ID=sa;Password=****************");
            ds = new DataSet();
            da.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
        }
    }
}
When you clicked on "Show Data" button a "emp" table information is displayed into dataGridView like:


No Problem, Its ok you can easily display sql table data into dataGridView. But if You have more than one from and in each and every from you requires to make a connection the what will happen, you muse specifies the full connection string in the SqlDataAdapter(), or say in connected oriented approach you must specify the connection string in SqlConnction().  Or if we see it all things in different manner when you do not want to write full connection string  into SqlConnection() or SqlDataAdapter(). You can simply add coonction string in to app.config file like as:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings >
    <add name="constr" connectionString="Data Source=.;Initial Catalog=sharad;User ID=sa;Password=*********"/>
 </connectionStrings>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
</configuration>
And Simply add a namespace
using System.Configuration;

and take a string datatype value and take a class ConfigurationManager.

like:
 
string constr = ConfigurationManager.ConnectionStrings["constr"].ToString();

and pass this string variable name into SqlDataAdpter() or SqlConnection in the place of full connection string , and it common for all windows from where you want to establish the connection of SQL Server database with the help of ADO.NET.
Now write a simple code that work as upper declared code for displaying the data into Datagridview, but here we declare connection string at  app.config file.

Code 1 (With app.config file)
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
namespace ConnectionString
{
    public partial class Form1 : Form
    {
        SqlDataAdapter da;
        DataSet ds;
        string constr = ConfigurationManager.ConnectionStrings["constr"].ToString();
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            da = new SqlDataAdapter("select * from emp", constr);
            ds = new DataSet();
            da.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
       }
    }
}

Output:

Now that's ok you will play with connection string to declare once and use more then once,. But there is one thing is not perfect, You can declare connection string once at "app.config" file and use it more than once in various forms of windows from application with the help "ConfigurationManger.ConnectionString["nameofconncetionstring"]". If I say you can not use this procedure but you must have declare connection string at app.config file, and use it more than once time. Now what you think it is possible or not,? So My answer is "Yes".

I describe one thing here you do not require to do the above procedure.

Follow the following steps:
At the solution explorer right click on you windows form application-properties like



after completing of it, double-click on "Settings.Settings". You will then get a grid with columns for Name, Type, Scope and Value as in the following:



Now, specify a name, then click in the Type cell. In the drop-down box choose "Connection String". Then set the Scope to "Application". 



Then click in the value cell and an ellipsis ("...") will appear in the right. Click on the ellipsis and create the connection string.




then Click on OK button,Then in the program, access (use) the connection string using: Properties.Setting.Default.(name) where (name) is the name you provided in the name column.

The following code automatically write in the app.config file
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name="ConnectionString.Properties.Settings.constr" connectionString="Data Source=.;Initial Catalog=sharad;User ID=sa;Password=**********" 
providerName="System.Data.SqlClient" />
</connectionStrings>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
</configuration>
The highlighted code simply write in the place of constr in your c# code. like as

Code 3 (With app.config file and with out ConfigurationManager class )
private void button1_Click(object sender, EventArgs e)
{
da = new SqlDataAdapter("select * from emp", Properties.Settings.Default.constr);
ds = new DataSet();
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
}
So after it you can not require to take the class for every windows form.