Connecting To Database Using C# ADO.Net

What is Connection Object in ADO.NET

Your application can be connected to a database or data source via a connection object. It opens a connection and carries the necessary authentic data, such as the login and password, in the connection string. For various data providers, a distinct kind of connection object is required. For instance:

• OLE DB – OleDbConnection
• SQL Server – SqlConnection
• ODBC – OdbcConnection
• Oracle – OracleConnection

What is Connection String

The Connection String comprises all the necessary legitimate data, such as Server Name, Database Name, Username, Password, etc., that is used to connect to a Data Source. A connection object uses a single line string to establish a connection to the database. This is how a connection string appears.

Data Source=.\SQLEXPRESS;Initial Catalog=Test1;Integrated Security=True

Another Example

Data Source=.\SQLEXPRESS;Initial Catalog=TestDB;User ID=sa;Password=System123;Pooling=False

Connect To the Database

There are 5 steps to connecting the database.

·???????Add Namespace: using System.Data.SqlClient;

·???????Create a Connection Object and Pass Connection String as Parameter.

·???????Open Connection

·???????Execute SQL Query

·???????Close the Connection.

Read The Data from the Database

The below code snippet will help you understand how we will use ADO.Net to retrieve the data from the database.

using System.Data.SqlClient;

string ConString = @"Server=localhost\SQLEXPRESS;Database=Test1;Trusted_Connection=True;";

SqlConnection con = new SqlConnection(ConString);

string SqlQuery = "Select * from Users";

con.Open();

try

{

????SqlCommand sqlCommand = new SqlCommand(SqlQuery,con);

????SqlDataReader reader = sqlCommand.ExecuteReader();

????while (reader. Read())

????{

??????????Console.WriteLine(reader[0].ToString() + "\n" +

???????????????reader[1].ToString() + "\n" + reader[2].ToString()+"\n"

???????????????+reader[3].ToString());

????}

}

catch (Exception)

{

????throw;

}

finally {

????con.Close();

????Console.ReadKey();

}

Program Output