Follow in Twitter & Facebook

Like in Facebook

Powered By Blogger Widgets

Free Download

FREE Tools

Friday, July 27, 2012

DataAccess with ADO.Net in Asp.Net

In this article we will discuss how to interact with Database from Asp.Net application. Here we will use Stored procedure and Ado.Net classes provided by Asp.Net. You can also use Microsoft Enterprise Library to interact with database from Asp.Net web site.

Before going forward you can also check Dataset and Datareader
In the first step, Put the connection string in the web.config like below:

 <add name ="DBConnection" connectionString="Data Source=Name of Datasource;Initial Catalog=Name of Database;User ID =UserID;Password=Password"/>

The above connection string is valid if you are using SQL Server authentication. But if you are using windows authentication then the connection string will be like below:
<add name="DBConnection"  connectionString="Server=MyServer;Database=Name of Database;Trusted_Connection=Yes;"/>


<add name="DBConnection" connectionString="Initial Catalog=Name of Database;Data Source=Name of Datasource;Integrated Security=SSPI;"/>

Now we will write the stored procude will will take 2 parameter and insert data to the table. CREATE PROCEDURE [dbo].[Insert_UserMaster]
@FirstName varchar(150),
@LastName varchar(150)
INSERT INTO UserMaster (FirstName,LastName)

VALUES (@FirstName,@LastName)

In the next we will write some using statement like below:

using System.Data.SqlClient;
using System.Configuration;

We can use ConfigurationManager class to retrieve connection string from web.config file.

string connection = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;

SqlConnection dataConnection = new SqlConnection();
dataConnection.ConnectionString = connection;
                SqlCommand command = new SqlCommand("Insert_UserMaster", dataConnection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@FirstName", "Value of First Name"));
                command.Parameters.Add(new SqlParameter("@LastName ", "Value of Last Name"));

                int result = command.ExecuteNonQuery();
// Here ExecuteNonQuery() will return number of rows affected.
 catch (SqlException ex)
                if (ex.Number == 2627)
//This number is for Primary key exception. This exception will occur if you will insert multiple values to primary key column.
catch (Exception ex)
                if (dataConnection != null)
command.CommandType = CommandType.StoredProcedure;

If you want to write inline sql statements then the line will change to below:

command.CommandType = CommandType.Text;

You can also check one article on ExecuteScalar(),ExecuteNonquery(),ExecuteReader() in Asp.Net

0 on: "DataAccess with ADO.Net in Asp.Net"