Database Connectivity

Database Connectivity

Following seven namespace used in Ado.Net

1. System.Data;
2. System.Data.SqlClient;
3. System.Data.OracelClient;
4. System.Data.Oledb;
5. System.Data.Odbc;
6. System.Data.Common;
7. System.Data.SqlType;

There are following common classes that are used in Connectivity.

1. Connection Class
2. Command Class
3. DataAdapter
4. DataReader
5. DataSet 

When we use these classes in different – different databases then we use a prefix before these classes. Sql for Sql Server, Oracle for Oracle Database and OLEDB for Data Access.

SqlConnection Class:

SqlConnection class is used to create connection between Client side and Server side.

To declare the object of SqlConnection:
SqlConnection con = new SqlConnection ();

Properties of SqlConnection Class
1. Con.ConnectionString :

ConnectionString defines the complete path of database which helps us to define connection.

Let See:

Con.ConnectionString = @”Data Source 
=.\SQLeXPRESS;AttachDbFilename=|DataDirectory|\KeypromptDatabase.mdf; Integrated Security=True; User Instance=True”;

Declaration of Connection string in web.config

Write Following line in add tag into connectionStrings tag in web.config

name="MyConnection" connectionString ="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\KeypromptDatabase.mdf;Integrated Security=True;User Instance=True"


How to define Connection after declaring connection string into web.config:

Step 1. Add Following namespace.
System.Configuration;

Step 2. Con.ConnectionString = 
ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;

We can define connection at the time of object declaration.

SqlConnection con = new SqlConnection ( ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString );
2. Con.State 
Con.State defines the state of Connection.

Example:

If(con.State == connectionState.Closed)
{
Con.Open();
}
Methods of SqlConnection Class

1. Con.Open() = To Open Connectoin.
2. Con.Close() = To Close Connection after use.

SqlCommand Class:

To declare object of SqlCommand Class

SqlCommand cmd = new SqlCommand();

Properties of SqlCommand Class

1. cmd.Connection: defines the connection for SqlCommand.
Example:
cmd.Connection =con;
2. cmd.CommandType: defines the type of command As: StoredProcedure, Text or TableDirect.
Example:
cmd.CommandType = CommandType.TableDirect;
cmd.CommandType = CommandType.Text;
cmd.CommandType = CommandType.StoredProcedure;

3. cmd.CommandText :if we use commandType Text then the CommandText will Sql Query. And if we use CommandType StoreProcedure then CommandText will be name of Store Procedure.
Example:
cmd.CommandText = " Select EmpName from KeypromptAccounts where EmpSalary between 45000 and 50000";

Methods of SqlCommand Class

1. cmd.ExecuteNonQuery() : is used to execute insert,update and delete queries; 
2. cmd.ExecuteScalar() : is used to return a single value from database;

Example

string TotalEmployee = cmd.ExecuteScalar();
3. cmd.ExecuteReader() : is used to fill DataReader.

SqlDataAdapter Class:
To declare object of SqlDataAdapter Class and define command 
SqlDataAdpter adpt = new SqlDataAdpter(cmd);
Adpt.Fill() : Method is used to fill data set. 

SqlDataReader

To declare object of SqlDataReader
SqlDataReader dr = new SqlDataReader();
Example: To fill DataReader
SqlDataReader dr = cmd.ExecuteReader();
DataSet: 

To declare object of Dataset
DataSet ds = new DataSet ();

Now we take an example of Database connectivity with KeypromptDatabase to select Employee
Name who’s Salary between 45000 and 50000.

Write following code in aspx.cs page 

Step 1. Add following Name space.

1. System.Data;
2. System.Data.SqlClient;
3. System.Data.SqlType;

Step 2.Make Sql Connection 

Write following code for create connection 

SqlConnection con 
= new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString);
if (con.State == ConnectionState.Closed)
{
con.Open();
}

SqlCommand cmd = new SqlCommand ();

cmd.Connection = con;

cmd.CommandType = CommandType.Text;

cmd.CommandText = "Select EmpName from KeypromptAccounts where EmpSalary between 45000 and 50000";

DataSet ds = new DataSet();

SqlDataAdapter adpt = new SqlDataAdapter(cmd);

adpt.Fill(ds);
con.Close();

 Friends,

We have learned database connectivity in Asp.net with SQL Server 2008.Now friends we will learn how to insert data into database.

Step 1: First Add an Employee.aspx page

Step 2: Now deign the Employee.aspx.




 Following Code show the html or source view of Employee.aspx page.

  <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Employee.aspx.cs" Inherits="Employee" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Keyprompt Technologies (P) Ltd. </title>

    <style type="text/css">

        .style1

        {

            width: 100%;

        }

    </style>

</head>

<body>

    <form id="form1" runat="server">

    <div>

    <fieldset style="width:50%; margin:0 auto;" >

    <center><h2> Add Employee </h2></center>

            <table cellpadding="5" cellspacing="5" width="100%">

            <tr>

                <td>First Name:</td>

                <td> <asp:TextBox ID="txtFName" runat="server"></asp:TextBox> </td>

            </tr>

            <tr>

                <td> LastName :</td>

                <td> <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox> </td>

           </tr>

            <tr>

                <td>Contact Number : </td>

                  <td> <asp:TextBox ID="txtContactNo" runat="server"></asp:TextBox> </td>

           </tr>

            <tr>

               <td> Email:</td>

                <td> <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox> </td>

          </tr>

            <tr>

                <td> Address : </td>

                <td><asp:TextBox ID="txtAddress" runat="server"></asp:TextBox> </td>

            </tr>

         <tr>

            <td colspan="2" align="center">

            <asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />

           <asp:Label ID="lblMSG" runat="server" ForeColor="Red"></asp:Label>

           </td>

            </tr>

        </table>

    </fieldset>

    </div>

    </form>

</body>

</html>

 Step 3: Write following code in Employee.aspx.cs.

  Step 3.1 First add following namespace.

             using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Configuration;Step 3.2: Write following code on button save click event.

protected void btnSave_Click(object sender, EventArgs e)

    {

         //establish the connection string

         SqlConnection con=new SqlConnection

         (ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString);

          if (con.State == ConnectionState.Closed)

          {

               con.Open();

           }

           //set command for query

           SqlCommand cmd = new SqlCommand("usp_InsertEmployee", con);

           //attach parameters

           cmd.Parameters.Add("@firstName", SqlDbType.VarChar, 20).Value = txtFName.Text;

           cmd.Parameters.Add("@lastName", SqlDbType.VarChar, 20).Value = txtLastName.Text;

           cmd.Parameters.Add("@contactNo", SqlDbType.VarChar, 15).Value = txtContactNo.Text;

           cmd.Parameters.Add("@email", SqlDbType.VarChar, 50).Value = txtEmail.Text;

           cmd.Parameters.Add("@address", SqlDbType.VarChar, 100).Value = txtAddress.Text;

           cmd.CommandType = CommandType.StoredProcedure;

           //execute command

           int result = cmd.ExecuteNonQuery();

           con.Close();

           if (result > 0)

           {

               lblMSG.Text = "Data Save Succesfully...";

            }

    }