AspdotnetCodes.com
Search
Articles
Books
Resources
Asp.Net News
Downloads
Free Tech Magazines
Asp.Net Web Hosting
Archives
Softwares
Newsletter
Suggest Us
Link to Us
Feeds Subscription
Articles
Questions & Answers
Tips & Tricks
 Creating Class File in Asp.Net 2.0 for Database Manipulations
This tutorial explains the programming with class file in Asp.Net 2.0 and explains the simplest way to do database manipulations through the class file. Class files are used to write business logic of the web applications and sometime used to manipulate database records also. The main advantage of a class file is code reusability and ease of code management.

What is a Class?

A class is simply a representation of a type of object; think of it as the object's blueprint. Just as a single blueprint can be used to build multiple buildings, a single negative of photographs can be used to print many photos. So a class can be used to create multiple copies of an object. It is the base for the all object oriented programming concepts.

In everyday programming life, we use this concept throughout our Asp.Net applications. Obviously we are not realizing that we are using these classes from creating a simple Label control to complex WebServices. For example, the TextBox control is defined by a TextBox class, which defines its appearance and its capabilities. Each time you drag a TextBox control onto a webpage, you are actually creating a new instance of the TextBox class. Sometimes you need to create a TextBox from your code-behind, which can be achieved only by creating an object from the TextBox Class as follows
TextBox TextBox1 = new TextBox();
TextBox1.ID="TextBox1";
When you create an object to the class, all the properties and methods can be accessed by the object or in other word we can say as instance. On the other hand, you can use a class only if you create an object to it. For example, if you consider a Car as a Class, then you need a driver to drive the Car. So the driver can access all the properties, functionalities and methods of the Car.
 
How to create a Class file in Microsoft Visual Studio 2005?

In your Microsoft Visual Studio 2005, there is a separate location or folder to store your class files. The folder is named as App_Code. The best practice is to save all your class files in this App_Code directory. You can add this folder to your solution by right-clicking on the project nArticleContentame, choose Add ASP.NET Folder, and then click on App_Code. Immediately this folder will be placed in your project. Now right-click on this folder, choose Add New Item, the select Class from the Add New Item window and provide you desired name to the class file. The extension for the class files will be .cs.
 
 
The class file that adds into the project will have some essential namespaces included into it along with a default constructor. For demonstration, we are going to create a class file for Customer database manipulation, so let us create a Class File named as CustomerCls.cs. The columns in the Customer Table is Customer Code [Cus_Code] which is primary and identity column, Customer Name [Cus_Name], Gender[Cus_Gender], City[Cus_City], State[Cus_State], Customer Type [Cus_Type]. So the basic code of the CustomerCls.cs structure will be as follows

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

///
/// Summary description for CustomerCls
///

public class CustomerCls
{
  public CustomerCls() 
  { 
    // 
    // TODO: Add constructor logic here 
    // 
  }
}

Every class starts with the Accessor type, which defines the accessibility of the class. A class with public accessor type can be accessed anywhere just by creating an object to it. Then you can see a method with the same name as the class, this method is called contructor method. A constructor is a special method that runs when the class is instantiated and it allows you to set some initial values to initiate when the class is created. You can also add arguments to the constructor method.

To control the characteristics of the class we have to define some properties. Usually we can define the properties in two ways one is by accessor property and the another is by public variables. Let us take the sample of the public variables for the Customer table.

public class CustomerCls
{
  public int CustomerCode; 
  public string CustomerName; 
  public string CustomerGender; 
  public string CustomerCity; 
  public string CustomerState; 
  public string CustomerType;
}

These public varibales can be used to define the properties of a class, but it is not advisable to use public variables since it is violating the rule of object-oriented concept. That is it breaks the concept of Abstraction, which provides a simple and guarenteed way to access the functionality of a class. So in this tutorial we will follow the concept of Accessor Properties.

Actually properties are used to control the character of a class, or to expose some value to the users of the class. The accessor type of a properties can be public, private or protected. If a property is public, there is no access restriction for this variable. Its value can be accessed outside of the class. If a property is defined as private, it can be accessed only within the class. If a property is defined as protected, it can be accessed from the same or derived class.

As we are going to manipulate customer table from this CustomerCls.cs, we are going to create six private properties and six public accessor properties to access all the fields of the Customer Table.

private int customercode;
private string customername;
private string customergender;
private string customercity;
private string customerstate;
private string customertype;

public int CustomerCode
{
get { return customercode; }
set { customercode = value; }
}

public string CustomerName
{
set { customername = value; }
get { return customername; }
}

public string CustomerCity
{
set { customercity = value; }
get { return customercity; }
}

public string CustomerState
{
set { customerstate = value; }
get { return customerstate; }
}

public string CustomerGender
{
set { customergender = value; }
get { return customergender; }
}

public string CustomerType
{
set { customertype = value; }
get { return customertype; }
}


An accessor property can be defined as read-only, write-only or both read-write propery. All the above properties have read-write permissions. Let us explain you by taking a single property in detailled manner. Consider the following property


private int customercode;

public int CustomerCode
{
  set { customercode = value; }
  get { return customercode; }
}
 
The private variable customercode cannot be accessed outside the CustomerCls class. This variable can only be used within this class and it handles the value being passed through the object of this class. The next public variable CustomerCode is used to access the property outside the class. When any value is passed to CustomerCode it indirectly call the set keyword section to execute, which sets the value to the customercode. When CustomerCode is called outside the class, in turn, it will call the get keyword section, which returns the value of the customercode. Like this we have define six properties for all the columns in the customer table.

Next, we are going to initialize a connection string from the constructor of this class. For that we have to create a private variable and assign the connectionstring value to this variable in the constructor as follows


private string ConnectionString;

public CustomerCls()
{
  ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
}

Database Manipulation in Class File

The database manipulations such as Insert, Update and Delete of data from the class file can be achieved by making active connection to the database server. To do this we have to include the SqlClient namespace into the class file as follows

using System.Data.SqlClient;

Now we can use SqlConnection and SqlCommand to do insertion, updation and deletion functions from the class file.

SqlConnection class: This class helps to open connection to a SQL Server database. We have to create an instance or object of this class by using a valid connection string. We have to call the Open method to open a SQL connection. In the same way, we must close the connection after it is used by calling the Close or Dispose methods.

SqlCommand class: This class helps a SQL statement or any stored procedure to execute for the given connection. The Parameter of the SqlCommand object is used to set the parameter values to the SQL Statement which is going to execute. The SqlCommand object’s Prepare statement creates a prepared version of the command on an instance of SQL Server. The ExecuteNonQuery executes a SQL statement against the connection and returns the number of rows affected. It is used to execute commands such as Transact-SQL INSERT, DELETE, UPDATE, and SET statements.

Insert method from Class File


The following example creates a SqlConnection and SqlCommand. The SQL Insert query is framed and assigned to a variable. The SqlConnection is opened and set as the Connection for the SqlCommand along with the query variable. All parameters are assigned to the SqlCommand object properly. Then we call the Prepare statement to prepare the SqlCommand before execute and then ExecuteNonQuery is called which executes the SQL Insert statement, and last we close the connection.

public void InsertCustomer()
{
  string sql = "INSERT INTO Customers(Cus_Name, Cus_Gender, Cus_City, Cus_State, Cus_Type) "
+ "VALUES (@CustomerName, @CustomerGender, @CustomerCity, @CustomerState, @CustomerType)";

  SqlConnection conn = new SqlConnection(ConnectionString);
  conn.Open(); 
  SqlCommand cmd = new SqlCommand(sql, conn); 
  cmd.Parameters.Add("@CustomerName", SqlDbType.VarChar,   50).Value = CustomerName; 
  cmd.Parameters.Add("@CustomerGender", SqlDbType.VarChar, 6).Value = CustomerGender; 
  cmd.Parameters.Add("@CustomerCity", SqlDbType.VarChar, 50).Value = CustomerCity; 
  cmd.Parameters.Add("@CustomerState", SqlDbType.VarChar, 50).Value = CustomerState; 
  cmd.Parameters.Add("@CustomerType", SqlDbType.VarChar, 50).Value = CustomerType; 
  cmd.Prepare(); 
  cmd.ExecuteNonQuery(); 
  conn.Close();
}

Update method from Class File


The update method is same as the insert statement except few things. The SQL Update query is framed and assigned to a variable called sql. Also it takes an additional parameter CustomerCode as a condition for the Update statement. Rest everything works the same way as the insert statement.

public void UpdateCustomer()

  string sql = "Update Customers Set Cus_Name=@CustomerName, Cus_Gender=@CustomerGender, Cus_City=@CustomerCity, " + " Cus_State=@CustomerState, Cus_Type=@CustomerType Where Cus_Code=@CustomerCode"; 

  SqlConnection conn = new SqlConnection(ConnectionString); 
  conn.Open(); 
  SqlCommand cmd = new SqlCommand(sql, conn); 
  cmd.Parameters.Add("@CustomerCode", SqlDbType.Int).Value = CustomerCode; 
  cmd.Parameters.Add("@CustomerName", SqlDbType.VarChar, 50).Value = CustomerName; 
  cmd.Parameters.Add("@CustomerGender", SqlDbType.VarChar, 6).Value = CustomerGender; 
  cmd.Parameters.Add("@CustomerCity", SqlDbType.VarChar, 50).Value = CustomerCity; 
  cmd.Parameters.Add("@CustomerState", SqlDbType.VarChar, 50).Value = CustomerState; 
  cmd.Parameters.Add("@CustomerType", SqlDbType.VarChar, 50).Value = CustomerType; 
  cmd.Prepare(); 
  cmd.ExecuteNonQuery(); 
  conn.Close();
}

Delete method from Class File


The delete method is very easy with a simple SQL Delete statement and a single condition to perform delete function. Here the Delete Statement deletes a specific record from the database based on the customer code passed to the CustomerCode parameter.
 
public void DeleteCustomer()
{
  string sql = "Delete From Customers Where Cus_Code=@CustomerCode"; 
  SqlConnection conn = new SqlConnection(ConnectionString); 
  conn.Open(); 
  SqlCommand cmd = new SqlCommand(sql, conn); 
  cmd.Parameters.Add("@CustomerCode", SqlDbType.Int).Value = CustomerCode; 
  cmd.Prepare(); 
  cmd.ExecuteNonQuery(); 
  conn.Close();
}

Fetching Records from Class File

Fetching or retrieving of records from the database can be performed with SqlDataAdapter class. This operation can be performed in a disconnected mode with the help of SqlDataAdapter objects. It serves as a bridge between a DataSet or DataTable and SQL Server for retrieving data. The SqlDataAdapter provides this bridge by the Fill method. For demonstration purpose, we use DataTable to fill the records fetched from the database. Let us look at the sample code.

public DataTable FetchAllCustomers()
{
  string sql = "Select * from Customers Order By Cus_Name"; 
  SqlDataAdapter da = new SqlDataAdapter(sql, ConnectionString); 
  DataTable dt = new DataTable(); 
  da.Fill(dt); 
  return dt;
}
The above example, select records from the database, and populate a DataTable with the selected rows. The populated DataTable is then returned. To accompolish this, a connection string must be passed to the SqlDataAdapter object along with a valid SQL Select statement.

Fetching Records with Parameter from the Class File

The selection of records from the database can also be conditional. That is we can select only few records based on certain conditions. This can be accomplished by passing a parameter to the SqlDataAdapter’s SelectCommand object. The code block below illustrates such method.

public DataTable FetchOneCustomer()
{
  string sql = "Select * from Customers Where Cus_Code=@CustomerCode";
  SqlDataAdapter da = new SqlDataAdapter(sql, ConnectionString);
  da.SelectCommand.Parameters.Add("@CustomerCode", SqlDbType.Int).Value = CustomerCode;
  DataTable dt = new DataTable();
  da.Fill(dt);
  return dt;
}
In both Fetch methods above, if you noticed we do not provide any valid SqlConnection object to perform database connection. Just we pass the connection string variable. This is enough for the SqlDataAdapter class to make connection to the database server and fetch data from the database. Opening and Closing of connections will be handled efficiently by the .NET Framework.

How to use this class in Code-Behind?

You have to create object for this CustomerCls.cs as follows.

CustomerCls customer=new CustomerCls();

By using the customer object, you can access all the properties and the methods of the CustomerCls class.

To learn more about the database manipulation of class file with GridView control, Click here.

Post your comments, doubts and questions about this tutorial here.
 
Sponsored by
Backup and Recovery Best Practices for Microsoft SQL Server 2005
Take Advantage of Oracle's 2 Day DBA Course
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!