- Add the following entries under connectionStrings section in web.config
<connectionStrings>
<add name="database" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient"/>
</connectionStrings>
-
Add a class to the website (and it is placed in App_Code folder) with the name Database and put the following code in it.
using System;
using System.Configuration;
using System.Web.Configuration;
public class Database
{
static public String ConnectionString
{
get
{ // get connection string with name database from web.config.
return WebConfigurationManager.ConnectionStrings["database"].ConnectionString;
}
}
}
- Create Book class to represent a book.
using System;
public class Book
{
// Using automatically implemented properties feature of C# 3.0
public int Bookid { get; set; }
public string Title { get; set; }
public string Authors { get; set; }
public string Publishers { get; set; }
public double Price { get; set; }
}
- Create a class, BooksDAL, to call stored procedures using ADO.NET. This is DAL for our application. Total data access must be done through DAL.
using System;
using System.Data.SqlClient;
using System.Data;
public class BooksDAL
{
public static DataSet GetBooks()
{
SqlConnection con = new SqlConnection(Database.ConnectionString);
SqlDataAdapter da = new SqlDataAdapter("getbooks", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
da.Fill(ds, "books");
return ds;
}
public static Book GetBook(int bookid)
{
SqlConnection con = new SqlConnection(Database.ConnectionString);
try
{
con.Open();
SqlCommand cmd = new SqlCommand("getbook", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@bookid", bookid);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
Book b = new Book();
b.Title = dr["title"].ToString();
b.Authors = dr["authors"].ToString();
b.Price = Double.Parse(dr["price"].ToString());
b.Publishers = dr["publisher"].ToString();
return b;
}
else
return null;
}
catch (Exception ex)
{
return null;
}
finally
{
con.Close();
}
}
public static string AddBook(string title, string authors, double price, string publisher)
{
SqlConnection con = new SqlConnection(Database.ConnectionString);
try
{
con.Open();
SqlCommand cmd = new SqlCommand("addbook", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@title", title);
cmd.Parameters.AddWithValue("@authors",authors);
cmd.Parameters.AddWithValue("@price",price);
cmd.Parameters.AddWithValue("@publisher", publisher);
cmd.ExecuteNonQuery();
return null; // success
}
catch (Exception ex)
{
return ex.Message; // return error message
}
finally
{
con.Close();
}
}
public static string DeleteBook(int bookid)
{
SqlConnection con = new SqlConnection(Database.ConnectionString);
try
{
con.Open();
SqlCommand cmd = new SqlCommand("deletebook", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@bookid", bookid);
cmd.ExecuteNonQuery();
return null; // success
}
catch (Exception ex)
{
return ex.Message; // return error message
}
finally
{
con.Close();
}
}
public static string UpdateBook(int bookid, string title, string authors, double price, string publisher)
{
SqlConnection con = new SqlConnection(Database.ConnectionString);
try
{
con.Open();
SqlCommand cmd = new SqlCommand("updatebook", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@bookid", bookid);
cmd.Parameters.AddWithValue("@title", title);
cmd.Parameters.AddWithValue("@authors", authors);
cmd.Parameters.AddWithValue("@price", price);
cmd.Parameters.AddWithValue("@publisher", publisher);
cmd.ExecuteNonQuery();
return null; // success
}
catch (Exception ex)
{
return ex.Message; // return error message
}
finally
{
con.Close();
}
}
}
Now, let us focus on user interface. Create a couple of ASP.NET pages and write code to access DAL from ASP.NET pages.
Comments
|
Posted By Chandra On 27-Mar-09 07:32:06 PM
its very helpful for me.... thanks sir.
Is there any projects related to 3-tier architecture i mean any projects based on 3-tier architecture
|
|
Posted By sridhar On 08-Apr-09 02:12:40 PM
Thanks sir,
This is very helpful for me...
i want one favour from u r side....
coule u please conduct a workshop on MVC with small project... i want to learn MVC architecture with good examples and how to apply MVC architecture of existing projects...
|
|
Posted By Francisco Montero On 06-Jun-09 05:05:30 AM
Excelent example!. Thank You very much. It help me to understand ASP.NET
|
|
Posted By vasu On 10-Jun-09 05:42:35 PM
Excellent!!!!!!!!! Many thanks
|
|
Posted By RIDVAN ARIK On 26-Feb-10 07:01:45 PM
It's very helpful for me.
Thanks sir. Eskisehir / TURKEY 26/02/2010
|
|
Posted By IntaftinS On 04-Mar-10 08:49:15 AM
I do think this is a most incredible website for proclaiming great wonders of Our God!
|
|
Posted By Manivannan On 11-May-10 09:37:36 AM
Nice work...
|
|
Posted By srinivas On 26-Aug-10 10:53:45 AM
Excelent!, Very Good Example....thanks sir, thank you very much
|
|
Posted By CRUD Queries On 06-Nov-11 04:25:19 PM
Do you suggest any tool which generates these types of stored procedures automatically? Like www.tools4sql.net
|
|
Posted By Rajeeivf On 10-Nov-11 03:21:05 PM
Nice code for absolute beginners...keep it up..
|
|
Posted By waive jnr On 12-Nov-11 11:59:11 PM
hey, you seem to be an asp.net guru. am kinda lost and i need your help.Am developing my first application and now am lost. I working on a club application, i have 5 tables (relational database) Table 1- Individual (fname, lname, date of birth, sex, passport, thumbprint, signiture) table 2-Spouse (name, sex, date of birth, tribe) table 3-Kids (name, date of birth,sex) table 3-Address (street, Town, state, country, Post box number) table 4-Employment (Occupation, name of firm, address of firm, email, phone, mobile) table 5-Referee (name, sex, address, signiture).
My question is, how do i create an insert form to insert the require details into these 5 tables? i can create one insert form to insert into one table at a time but not 5, i can also use join to retrieve the data once inserted. Kindly help me pleeeaaase....
|
|
Posted By ratnesh On 02-Jan-12 06:54:41 PM
good
|
|
Posted By Ed On 27-Feb-12 10:40:49 PM
Nice!
|
|
Posted By sheta sanjay On 22-Mar-12 06:50:09 PM
Its Very Good Example.
Is there Any simple Project to understand 3 tier properly.
send me mail.
|
|
Posted By Raju On 12-Apr-12 08:22:09 AM
nice buddy....
ihave seen lot many xample but this as simple and complete
|
|
Posted By surendar reddy.G On 14-Jun-12 06:26:43 PM
your website very good for beginners .I hope you that you will make some more articles for beginners in .net
|
|
Posted By Dhanalakshmi On 30-Oct-12 10:16:51 AM
its very usefull and it was great tutorial for biginners.Thanks you so much.
and keep post like this article especialy sql joins with stored procedure please.
|
|
Posted By meghana On 11-Dec-12 07:40:05 AM
its really nice. that helped me lot.. thankyou
|
|
Posted By Vasu Ravuri On 13-Jan-13 05:59:38 PM
The code you provided is nice.
Friends please look at below link for more projects and source codes
http://dotnettrainer.wordpress.com/sample-dot-net-projects-and-sample-dot-net-source-codes/
|
|
Posted By james On 01-Feb-13 06:09:54 PM
good
|
|
Posted By Ali On 18-Oct-13 07:50:50 PM
I got lot f knowledge...thnxxx
|
|
Posted By anita On 08-Jul-14 03:58:47 PM
thanx sir..it was somuch helpful project for me
|
|
Posted By Hiren Patel On 10-Jan-15 03:53:01 PM
Please Provide screenshot of this application.
|
|
Posted By Pawan Kumar yadav On 10-Jul-15 03:43:06 PM
Thanks for this code It very helpful for me
|
|
Posted By shweta On 14-Jun-19 03:15:20 PM
very informative blog related to ADO.NET......
well explained.......well done ........
thank you......keep sharing...............
|
|
Posted By Sagar On 05-Aug-19 02:24:15 PM
Hi,
This is Sagar, I was tried this program, but I didn't get output currently.
When I was entered Add/Update/delete/list options in menu. Html it showed exception.
|