Friday, 20 January 2012

Working with Stored Procedures in Sql server

Working with Stored Procedures

"a precompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit". Stored procedures allow a more flexibility offering capabilities such as conditional logic .Bandwidth and execution time are reduced because stored procedures are stored within the DBMS,. because a single stored procedure can execute a complex set of SQL statements.

SQL Server pre-compiles stored procedures such that they execute optimally and client developers are abstracted from complex designs. They would simply need to know the stored procedure's name and the type of data it returns.

To add a new stored procedure to the Northwind Microsoft SQL database, perform these steps.

Load SQL Server Enterprise Manager, found in the Microsoft SQL Server program group.
Expand the tree "Microsoft SQL Servers," "SQL Server Group," and locate your database server. With SQL installed locally, the default server will be (LOCAL
Locate and expand the Student database. Select Stored Procedures. On the right side of the screen, you'll see any default stored procedures included with the student database.
Right-click anywhere on the right pane of the Enterprise Manager and select New Stored Procedure.

Simple insert Stored Procedure

use studentcreate procedure studentinsertproc
@stdid text,@subid text,@subname text,@mark int
as
insert into student
(stdid ,subid,subname,mark)values(@stdid,@subid,@subname,@mark)
studentinsertproc '10001','101','computer graphics',85
studentinsertproc '10002','101','computer graphics',65,
studentinsertproc '10003','101','computer graphics',75
studentinsertproc '10004','101','computer graphics',55
studentinsertproc '10005','101','computer graphics',85
studentinsertproc '10006','101','computer graphics',87
select * from student

5)select and press F5

Introduction of ADO .NET,ADO .NET Architecture,ADO VS ADO .NET,Feature of ADO .NET,Advantage of ADO .NET

Introduction of ADO .NET

Microsoft ADO.NET (ActiveX Data Objects .NET) is part of the Microsoft .NET Framework: a set of tools and layers that allows your application to easily manage and communicate with its file-based or server-based data store.ADO.NET (ActiveX Data Objects .NET) is the primary data access API for the .NET Framework. It provides the classes that you use as you develop database applications with Visual Basic .NET as well as other .NET languages. Ado.net consists of classes that allow a .NET application to connect to the data source, executes commands and manage disconnected data.In the .NET Framework, the ADO.NET libraries appear under the System.Data namespace.These libraries include functionality to connect to these data sources, execute commands,and store, manipulate, and retrieve data.

Various .NET Data Providers:-

ADO .NET Architecture

The two key components of ADO.NET are Data Providers and DataSet . The .Net Framework includes mainly three Data Providers for ADO.NET. They are the Microsoft SQL Server Data Provider, OLEDB Data Provider and ODBC Data Provider. SQL Server uses the SqlConnection object , OLEDB uses the OleDbConnection Object and ODBC uses OdbcConnection Object respectively.

Data Provider

he Data Provider is used for providing and maintaining the connection to the database. A DataProvider is a collection of related components that work together to provide data in an efficient and performance driven mannern.The .net framwork has two DataProviders: the SQL Data Provider which is designed only to work with Microsoft's SQL Server 7.0 or later and the OleDb DataProvider which allows us to connect to other types of databases like Access and Oracle. Each DataProvider consists of the following component classes:
The Connection object - It provides a connection to the database
The Command object - It is used to execute a command
The DataReader object- It provides a forward-only, read only, connected recordset
The DataAdapter object- It populates a disconnected DataSet with data and performs update.

Data SET

The dataset is a disconnected, in-memory representation of data. It can be defined as a local copy of the relevant portions of the database.DataSet consists of a collection of DataTable objects that you can relate to each other with DataRelation objects. The DataTable is a collection of DataRow and DataCoulumn Object which contains Data. The DataAdapter Object create a bridge between the DataSet and the Data Source The DataSet is persisted in memory and the data in it can be manipulated and updated independent of the database. When the use of this DataSet is finished, changes can be made back to the central database for updating. The data in DataSet can be loaded from any valid data source like Microsoft sql database an oracle atabase or from a Microsoft Access database.

ADO VS ADO .NET

ADO	ADO .NET
ADO works with connected data.	ADO.NET uses data in a disconnected fashion.
In ADO, the in-memory representation of data is the recordset.	In ADO.NET, it is the dataset
In ADO you scan sequentially through the rows of the recordset using the ADO MoveNext method. ADO's are stateful(TCP/IP)	In ADO.NET, rows are represented as collections, so you can loop through a table as you would through any collection, or access particular rows via ordinal or primary key index. ADO.NET's are stateless(internet)
ADO objects communicate in binary mode.	ADO.NET uses XML for passing the data.
ADO allows you to create client-side cursors only	ADO.NET gives you the choice of either using client-side or server-side cursors.
Derives information about data implicitly at run time, based on metadata that is often expensive to obtain.	Leverages known metadata at design time in order to provide better run-time performance and more consistent run-time behavior.
ncludes implicit behaviors that may not always be required in an application and that may therefore limit performance.	ncludes implicit behaviors that may not always be required in an application and that may therefore limit performance.

Feature of ADO .NET

The salient features of ADO.NET :

Enhancements to the DataSet and Datatable classes
Optimized DataSet Serialization
Conversion of a DataReader to a DataSet or a DataTable and vice versa
Data Paging
Batch Updates Â— Reduction in database roundtrips
Asynchronous Data Access
Common Provider Model
Bulk Copy

Advantage of ADO .NET

ADO.NET Does Not Depend On Continuously Live Connections.
Database Interactions Are Performed Using Data Commands.
Data Can Be Cached in Datasets.
Datasets Are Independent of Data Sources.
Data Is Persisted as XML.
Schemas Define Data Structure

How To make a Web Services ( To Calculate Circle area using C#),

How To make a Web Services ( To Calculate Circle area using C#)
Step 1. Start a New Web Site

Click on ASP.Net Web Services and named your service CircleArea and press on OK
Step 2. The code written for your application which return area of circle (Service.cs). This service contain a CircleArea method which calculate the area.

using System;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class Service : System.Web.Services.WebService
{
    public Service () {  }
 [WebMethod (Description="To calculate Area of Circle")]
    public double CircleArea(int a) 
    {return 3.14 * (a * a);}
}

Step 3. Run your service.

Click on CircleArea. Service is run and when you input value the result be shown.

If you want to see Service Description. Click on Service Description

Create a Web Service For Calculate Age when user input Date
Step 1. Open a new Web Site and click on ASP.Net Web Services press on OK as shown below in fig.

Step 2. The code (Service.cs) written for Calculating Age , Calculate Factorial and Square Root of a Number.

using System;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class Service : System.Web.Services.WebService
{[WebMethod (Description="Calculating Age")]
    public string calAge(DateTime date)
    {   DateTime nowDate = System.DateTime.Now;
        System.TimeSpan span = nowDate - date;
        int days = (int)span.TotalDays;
        int yea = days / 365;
        string age = yea.ToString() + " years ";
        return age;}
    [WebMethod(Description = "Calculating SquareRoot")]
    public double calSquareRoot(int a)
    {   double i = 0;
        double x1, x2=0;
        while ((i * i) <= a)
        i += 0.1;
        x1 = i;
        for (int j = 0; j < 10; j++)
        {   x2 = a;
            x2 /= x1;
            x2 += x1;
            x2 /= 2;
            x1 = x2;}
        return x2;}
    [WebMethod(Description = "Calculating Factorial")]
    public double calFactorial(int a)
    {if (a == 0)return 1;
        else return (a*calFactorial(a-1));}}

Step 3. Debug The service

Click on each link and check your service
Step 4. To use this service, go to your project solution right click and click on Add Web Reference. A new window open

Copy the URL of your running web service and paste it in the new window place as shown in below fig.

Note: Remember the web reference name, it will be added in your project (.cs ) file e.g. this reference help to call the method of your web services. You will call the methods to create an object of SERVICE. To know more click Here

using localhost;

Click on Add Reference, The service will be added in your web site. It will be show on your page like below fig.

How to Use web Services in ASP. Net using C#
Step 1. Run Your Service and open a new Website, Add a label and one textBox to enter radius of Circle

Click on Add web Reference

Copy the URL of Your running web Service and paste it in Your Web Reference window

Click on Go
Step 2. You can change your Web reference name but you remember this. click on add Reference

The service will be added in your application

Step 3. Now add the Web reference (name space) in your application (shown below). written the following code in your .cs file (Default.aspx.cs).

using System;
using System.Configuration;
using System.Web;
using System.Data;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using localhost;
public partial class _Default : System.Web.UI.Page 
{
    Service ser = new Service();
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        double area = ser.CircleArea(int.Parse(TextBox1.Text));
        Label2.Text = "Area is :  " + area.ToString();
    }
}

Step 4. Run your application (Necessary to running your web service which you use in your application)

How to Use web Services in Console Application using C#
Step 1. Open a new project and select console Application. press on Ok.

Step 2. Click on Add service reference a new window open Click on Advance,

A new window open click on Add Web reference and copy the URL of your running service and paste it to the URL space in new window shown below and click on Go. Click on Add reference but remember your Web reference name

The Service will be added in your project

Step 3. Written the following code in your Program (Program.cs)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
namespace Circlearea
{
    class Program
    {
       static void Main(string[] args)
        {
            localhost.Service lo = new localhost.Service();
            Console.Write("Please enter the value=");
            int area = Convert.ToInt32(Console.ReadLine());
            double fr=lo.CircleArea(area);
            Console.WriteLine("Area is =" + fr);
            Console.ReadLine();
        }
    }
}

Step 4. Run the Application

3 tier Architecture of ASP.NET

The configuration of ASP.NET is managed by information stored in XML-format in a configuration file (Web.Config).
The cache allows for improved performance of ASP.NET, as the most commonly requested pages would be served from
the ASP.NET cache.State management services for ASP.NET are provided by the ASP.NET state service.
The .NET Framework provides the Common Language Runtime (CLR) , which compiles and manages the execution of ASP.NET
code, and the class libraries, which offer prebuilt programmatic functionality for Web Forms, XML support, and exception handling.
ADO.NET provides ASP.NET with connections to databases.

3-Tier Architecture in ASP.NET with C#

3-Tier architecture generally contains UI or Presentation Layer, Business Access Layer (BAL) or Business Logic Layer and Data Access Layer (DAL).

Presentation Layer (UI)
Presentation layer contains pages like .aspx or windows form where data is presented to the user or input is taken from the user.

Business Access Layer (BAL) or Business Logic Layer
BAL contains business logic, validations or calculations related with the data..

Data Access Layer (DAL)
DAL contains methods that helps business layer to connect the data and perform required action, might be returning data or manipulating

data (insert, update, delete etc).

Designing 3-Tier Architecture

Data Access Layer (DAL) Code

Code for Data Access Layer


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;

using System.Data.OleDb;


public class PersonDAL
{
    
  string connStr = ConfigurationManager.ConnectionStrings["connectionstring"].ToString();
    public PersonDAL()
    {
   }
    
    public int Insert(string name, string address, int age)
    {

        OleDbConnection conn = new OleDbConnection(connStr);

        conn.Open();

  OleDbCommand dCmd = new OleDbCommand("insert into insertrecord values(@P,@q,@r)",conn);
        try
        {
            dCmd.Parameters.AddWithValue("@p", name);
            dCmd.Parameters.AddWithValue("@q", address);
            dCmd.Parameters.AddWithValue("@r", age);
            return dCmd.ExecuteNonQuery();

        }

        catch
        {

            throw;

        }

        finally
        {

            dCmd.Dispose();

            conn.Close();

            conn.Dispose();

        }

    }
    }

Code for .cs file


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

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //Lets validate the page first

        if (!Page.IsValid)

            return;

        int intResult = 0;

        PersonBAL pBAL = new PersonBAL();

       // Instantiate the object we have to deal with

        string name = txtname.Text;
        string address = txtaddress.Text;
        int age = Int32.Parse(txtAge.Text);
        try
        {
           intResult = pBAL.Insert(name, address, age);
            if (intResult > 0)

               lblMessage.Text = "New record inserted successfully.";

            else

   lblMessage.Text = "FirstName [<b>" + txtname.Text + "</b>]

                      alredy exists, try another name";

        }
        catch (Exception ee)
        {

            lblMessage.Text = ee.Message.ToString();

        }

        finally
        {

            pBAL = null;

        }        
    }
}

Presentation Layer

Code for .aspx page


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

<!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>Untitled Page</title>
    <style type="text/css">
        .style1
        {
            width: 100%;
            height: 215px;
            background-color: #FFFFCC;
        }
        .style2
        {
            width: 271px;
        }
        .style3
        {
            width: 271px;
            height: 44px;
        }
        .style4
        {
            height: 44px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div style="background-color: #008000; height: 253px;">
    
        <table class="style1">
            <tr>
                <td colspan="2">
                                      ADD RECORDS&nbsp;</td>
            </tr>
            <tr>
                <td class="style2">
                    &nbsp;&nbsp;&nbsp;&nbsp;Name</td>
                <td>
                    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td class="style2">
                    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp; 
                    Address&nbsp;</td>
                <td>
                    <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td class="style3">
                    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Age&nbsp;</td>
                <td class="style4">
                    <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td class="style2">
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <td>
                    <asp:Button ID="Button1" runat="server" onclick="Button1_Click" 
                        Text="Save Record" />
                </td>
            </tr>
            <tr>
                <td class="style2">
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
        </table>   
    </div>
    </form>
</body>
</html>

Introduction of ASP.NET Architecture Of ASP.NET Framework Page Life Cycle Of ASP.NET

1.Introduction of ASP.NET
2.Architecture Of ASP.NET Framework:
3.Page Life Cycle Of ASP.NET:

ASP.NET is the next generation ASP, but it's not an upgraded version of ASP. ASP.NET is an entirely new technology for server-side scripting. ASP.NET is a part of the Microsoft .NET framework, and a powerful tool for creating dynamic and interactive web pages. ASP.NET is a server side scripting technology that enables scripts (embedded in web pages) to be executed by an Internet server

ASP.NET is a Microsoft Technology.
ASP stands for Active Server Pages
ASP.NET is a program that runs inside IIS.
IIS (Internet Information Services) is Microsoft's Internet server.
IIS comes as a free component with Windows servers.
IIS is also a part of Windows 2000 and XP Professional.

ASP.NET File

An ASP.NET file is just the same as an HTML file
An ASP.NET file can contain HTML, XML, and scripts
Scripts in an ASP.NET file are executed on the server
An ASP.NET file has the file extension ".aspx"

Working of ASP.NET

When a browser requests an HTML file, the server returns the file.
When a browser requests an ASP.NET file, IIS passes the request to the ASP.NET engine on the server.
The ASP.NET engine reads the file, line by line, and executes the scripts in the file.
Finally, the ASP.NET file is returned to the browser as plain HTML.

Difference Between ASP and ASP.NET

Code behind in asp. net allows separation of business logic from UI which is not possible in asp.

ASP. Net uses ADO .Net objects which are represented using XML and hence they are lightweight and can travel through firewalls. ASP uses ADO record sets which are binary COM objects heavier than ADO.Net counterparts and cannot travel through firewalls.

ASP.Net supports object oriented programming. ASP is procedural.

ASP.Net is compiled and hence performs better. ASP is pure scripting and hence interpreted at the time of page load.

ASP.Net has caching and exception handling which is not available with ASP.

ASP.NET has better language support, a large set of new controls, XML-based components, and better user authentication.

ASP.NET provides increased performance by running compiled code.

ASP.NET code is not fully backward compatible with ASP

Basic Features In ASP.NET:

Easy-to-use, graphical data mapping interface
Instant data transformation
XSLT 1.0/2.0 and XQuery code generation
Java, C#, and C++ code generation
Advanced data processing functions
Support for all major relational databases including SQL Server, IBM DB2, Oracle, and more
Integration with Altova StyleVision for report rendering
Visual Studio & Eclipse integration
Available in 32-bit and 64-bit versions

Advanced Features in ASP.NET:

Some of the new features in ASP.NET 2.0 are:

Master Pages, Themes, and Web Parts
Standard controls for navigation
Standard controls for security
Roles, personalization, and internationalization services
Improved and simplified data access controls
Full support for XML standards like, XHTML, XML, and WSDL
Improved compilation and deployment (installation)
Improved site management
New and improved development tools

Some of the new features in ASP.NET 3.5 are:

ASP.NET AJAX.
New ListView and DataPager Controls.
WCF Support for RSS, JSON, POX and Partial Trust.
LINQ (Language Integrated And Query).
New Web Design Interface.
Multi-targeting Support.

The new assemblies that would be of use to ASP.NET 3.5 developers are as follows:

System.Core.dll - Includes the implementation for LINQ to Objects
System.Data.Linq.dll - Includes the implementation for LINQ to SQL
System.Xml.Linq.dll - Includes the implementation for LINQ to XML
System.Data.DataSetExtensions.dll - Includes the implementation for LINQ to DataSet
System.Web.Extensions.dll: Includes the implementation for ASP.NET AJAX (new enhancements added) and new web controls as explained earlier.

Advantage of ASP.NET

ASP.NET drastically reduces the amount of code required to build large applications.
With built-in Windows authentication and per-application configuration, your applications are safe and secured.
It provides better performance by taking advantage of early binding, just-in-time compilation, native optimization, and caching services right out of the box.
The ASP.NET framework is complemented by a rich toolbox and designer in the Visual Studio integrated development environment. WYSIWYG editing, drag-and-drop server controls, and automatic deployment are just a few of the features this powerful tool provides.
Provides simplicity as ASP.NET makes it easy to perform common tasks, from simple form submission and client authentication to deployment and site configuration.
The source code and HTML are together therefore ASP.NET pages are easy to maintain and write. Also the source code is executed on the server. This provides a lot of power and flexibility to the web pages.
All the processes are closely monitored and managed by the ASP.NET runtime, so that if process is dead, a new process can be created in its place, which helps keep your application constantly available to handle requests.
It is purely server-side technology so, ASP.NET code executes on the server before it is sent to the browser.
Being language-independent, it allows you to choose the language that best applies to your application or partition your application across many languages.
ASP.NET makes for easy deployment. There is no need to register components because the configuration information is built-in.
The Web server continuously monitors the pages, components and applications running on it. If it notices any memory leaks, infinite loops, other illegal activities, it immediately destroys those activities and restarts itself.

Easily works with ADO.NET using data-binding and page formatting features. It is an application which runs faster and counters large volumes of users without having performance problems.

Page Life Cycle Of ASP.NET:

PreInit	Use this event for the following: • Check the IsPostBack property to determine whether this is the first time the page is being processed. • Create or re-create dynamic controls. • Set a master page dynamically. • Set the Theme property dynamically.
Init	Use this event to read or initialize control properties
InitComplete	Use this event for processing tasks that require all initialization be complete.
PreLoad	Use this event if you need to perform processing on your page or control before the Load event. After the Page raises this event, it loads view state for itself and all controls, and then processes any postback data included with the Request instance.
Load	The Page calls the OnLoad event method on the Page, then recursively does the same for each child control, which does the same for each of its child controls until the page and all controls are loaded.
LoadComplete	Use this event for tasks that require that all other controls on the page be loaded.
Page_PreRender	The application is about to render the page object.
Page_Unload	The page is unloaded from memory.
Page_Disposed	The page object is released from memory. This is the last event in the life of a page object.

Architecture Of ASP.NET Framework:

In Above Figure:

The configuration of ASP.NET is managed by information stored in XML-format in a configuration file (Web.Config). The cache allows for improved performance of ASP.NET, as the most commonly requested pages would be served from the ASP.NET cache.State management services for ASP.NET are provided by the ASP.NET state service. The .NET Framework provides the Common Language Runtime (CLR) , which compiles and manages the execution of ASP.NET code, and the class libraries, which offer prebuilt programmatic functionality for Web Forms, XML support, and exception handling. ADO.NET provides ASP.NET with connections to databases.

Subscribe to: Posts ( Atom )