Establishing connection to Sql Server in ADO.NET
Hi.
In this blog I will teach you that how to establish
connection to sql server in ado.net. This is first basic program through which
you learn that how to establish connection.
1.
For creating any program in ADO.Net basically
you need two namespaces. One is System.Data which contains
classes for disconnected architecture such as DataSet, SqlDataAdapter etc and
other one is System.Data.SqlClient namespace which contains classes
such as SqlConnection, SqlDataReader, SqlCommand etc. For making this program
please import these two namespaces.
using System.Data; //Namespace
required for ado.net programing.
using System.Data.SqlClient; //Namespace required for ado.net programing.
2. Now
create a property named getConnectionString
which will return connection string required to establish connection from
database. Connection string is something like a string which contains all
information required to establish connection from database. Such as DataBase
name which is passed in InitialCatalog keyword, name of server which is passed
in DataSource kjeyword. Then create a property like this.
/// <summary>
/// This property will
return connection string required to establish connection to desired server.
/// </summary>
public static string getConnectionString
{
get
{
return "Data
Source=AAA-PC;User Id=sa;Password=aaaaa;Initial Catalog=WorkBook";
}
}
3. No
finally create a method named establishConnection() which will check connection
establish or not like following example demonstrate.
/// <summary>
/// This method will return boolean type value. If it returns
true it means connection successfully
/// establish and if it
returns false then it means some problem occurs while establishing connection
to server.
/// </summary>
/// <returns></returns>
public static bool establishConnection()
{
bool status = false;
4. try
{
con = new SqlConnection(); //Create
an object of SqlConnection class. This class will reside in
System.Data.SqlClient namespace.
con.ConnectionString = getConnectionString; //Pass
connection string to SqlConnection class by calling ConnectionString property
of SqlConnection class.
con.Open(); //Open connection by calling Open() method of SqlConnection
class.
status
= true; //Make status to be true flag.
}
catch
{
status = false; //If any exception is generated then make status to be
false. This means connection is not established.
}
finally
{
if (con != null)
con.Close();
//Finally close connection.
}
return status;
}
Now finally execute method and
see the output.
Example of complete program
using System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Text;
using
System.Data; //Namespace required for ado.net programing.
using
System.Data.SqlClient; //Namespace required for ado.net programing.
namespace
ConnectionDemo
{
class Program
{
/// <summary>
/// This property will return connection string required to
establish connection to desired server.
/// </summary>
public static string
getConnectionString
{
get
{
return
"Data Source= AAA-PC;User
Id=sa;Password=aaaaa;Initial Catalog=WorkBook";
}
}
/// <summary>
/// This method will return boolean type value. If it returns
true it means connection successfully
/// establish and if it returns false then it means some
problem occurs while establishing connection to server.
/// </summary>
/// <returns></returns>
public static bool
establishConnection()
{
bool
status = false;
try
{
con = new
SqlConnection(); //Create
an object of SqlConnection class. This class will reside in
System.Data.SqlClient namespace.
con.ConnectionString =
getConnectionString; //Pass connection string to SqlConnection class by calling
ConnectionString property of SqlConnection class.
con.Open(); //Open connection by calling Open() method of SqlConnection
class.
status = true; //Make status to be true flag.
}
catch
{
status = false; //If any exception is generated then make status to be
false. This means connection is not established.
}
finally
{
if
(con != null)
con.Close(); //Finally close connection.
}
return
status;
}
private
static SqlConnection
con = null; //Create a
reference variable of SqlConnection class.
static void Main(string[]
args)
{
if
(establishConnection()) //Check whether connection establish or not.
Console.WriteLine("Great! You have successfully establish
connection..."); //Display success message.
else
Console.WriteLine("Sorry! Unable to establish connection from
server..\nPlease check connection string."); //Display failure
message.
}
}
}
Thanks...