Monday, 14 May 2012

how to call stored procedure in c#

Step 1) Create a ASP.NET Web Application using .net 4.0

before that

create database table called "sales data" 

USE [test]
GO

/****** Object:  Table [dbo].[Sales Data]    Script Date: 05/15/2012 00:40:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Sales Data](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Sales Man] [nchar](20) NOT NULL,
[2005] [int] NOT NULL,
[2006] [int] NOT NULL,
[2007] [int] NOT NULL,
 CONSTRAINT [PK_Sales Data] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSET DATA INTO TABLE



INSERT INTO [Sales Data]([Sales Man],[2005],[2006],[2007]) VALUES('CAROL',2000,3000,4000)
......

create stored procedure 


USE [test]
GO

/****** Object:  StoredProcedure [dbo].[getsalesdata]    Script Date: 05/15/2012 00:42:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[getsalesdata]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT * from [sales data]
END

GO


test the stored procedure
exec getsalesdata

in ASPX  PAGE ADD FOLLOWING CODE 


    protected void Page_Load(object sender, EventArgs e)
    {
        using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=test;Integrated Security=True;trusted_connection=yes"))
        {
            using (SqlDataAdapter ad = new SqlDataAdapter("exec getsalesdata", conn))
            {
                using (DataSet ds = new DataSet())
                {
                    ad.Fill(ds);
                    GridView1.DataSource = ds;
                    GridView1.DataBind();
                }
            }
        }
    }



OUTPUT