Paging without a Wizard (SqldataSource Control) in ASP.NET

Vuyiswamb
Posted by in ASP.NET category on for Beginner level | Views : 9832 red flag
Rating: 5 out of 5  
 1 vote(s)

Dave(CodeProject) once told me not to use Wizards. Well let me tell what I come across in ASP.NET. I came across “PAGING” and I looked for examples on the NET, all were using SqldataSource control. I have nothing against the control; I must admit that Microsoft has done a very good job on it. It’s just that I am against using the wizards. I am a type of Programmer that is interested in writing what the wizard will do for me. Others might ask questions lie “Why reinvent a wheel”. My answer is it makes me a better programmer who understands better. Now in this Article we are going to write our small Paging Application still following the N-Tier Design Style without using any wizard.
Create Database

 

Now the First thing we need to do is to create a Database name it your name in SQL

 

Create Database Vuyiswa

 

After you are done use the Database and create the table

 

create table memyself
(
ID int identity(100,01)Primary key not null,
Fname varchar(20) null,
Lname varchar(20) null,
Tel int null,
Adress varchar(35) null,
Car_model varchar(20) null,
Car_Type varchar(20) null
)
 

Then lets Fill the table with data. Rememeber we want to demostrate the “Paging Functionality” So that means you an write one insert statement  and execute it 80 times J. Write an insert statement like I did below

 
--Run the Insert to Fill the Table
 
insert into memyself
values('Dave','Mucky',054545774,'44 Drake Avenue, Pretoria west 001','2009','HAMMER H3')
 

when you are done, check how many records in your table you have

 
SELECT * FROM  memyself
 

I have got 80 records, that will be enough for the test

 
 
 

The Last Step on the SQL side is to create a Stored Procedure that we are going to use in our Application, like this

 
CREATE PROC PRCALLDATA
AS
 
SELECT * FROM  DBO.memyself
 

When you are Done lets go and Create our Application. Iam using VS2005. Open your Visual Studio and create a new Website or Web Application. Am using C# you can use your vb.net.

 

As I said before all my examples , no matter how small they are , I will show them in a Tired way.

 
Tire Creating
 

Now your Application will only contain a Website, that has a page “Default.aspx” like this 

 

Now we have to Add other Layers. The DAL and the BLL. Right Click on the Solution Explorer and Add a Class Project and name it “DAL” and the Next one again and name it “BLL” after that Add a Connection string on your Web.Confi File like this

 
  <appSettings>
    <add key ="F" value ="User id =sa;Password=wow;Server=VUYISWA\SQLEXPRESS;Database=VUYISWA"/> 
  </appSettings>
 

And after that is done your Solution explorer will look like this

 

Oops now it seems like everything is now ready to be code, ooh wait we forget that we have to start from the presentation layer First. As you can see I have renamed my default.aspx to shop.aspx, in gauss I love shopping. Open your default.aspx in a Design mode and add a Gridview. In the Wizard world (Harry Potter), you would have added eve the SqldataSource control, but now we are not going to do that. Last thing add a Button because we don’t want to show our data on page load. At the Bottom of your page you will see something like this

 
 

Click on the Source

 

And we need to enable paging and you will see something like this

 
    <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
 

Now change it to look like something like this

 
        <asp:GridView ID="GridView1"
         runat="server"
         Height="293px"
         Width="674px"
          AllowPaging="True"       
           OnPageIndexChanging="GridView1_PageIndexChanging">
        </asp:GridView>
 

The Bolded part AllowPaging is the one that will allow you to have a paging Functionality and the next one OnPageIndexChanging, this one will allow you to go to another page and see different data. In this event you will rebind the Page. And name your button “btnshowdata” and the text make it “Show Data” its not a must, you can name it anything.

 

Now your page should look like this

 
 

At the bottom are numbers that represents the pages. Now we want it to work like this. When you click the Button, it should show us the Data from the Database.

 
DAL
 

Now Go to your DAL and make sure you write a Code like this.

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

using System.Data;

using System.Configuration;

 

namespace DAL

{

 

    public class DAL

    {

        String strcon = ConfigurationManager.AppSettings.Get("F");

 

        SqlConnection con;

 

        SqlCommand cmdselect;

 

        //SqlCommand cmdupdate;

 

        //SqlCommand cmdDelete;

 

        SqlDataAdapter da;

 

        public DataSet GetData()

        {

            con = new SqlConnection(strcon);

 

            cmdselect = new SqlCommand();

 

            cmdselect.CommandText = "dbo.PRCALLDATA";

 

            cmdselect.CommandTimeout = 0;

 

            cmdselect.CommandType = CommandType.StoredProcedure;

 

            cmdselect.Connection = con;

 

            da = new SqlDataAdapter();

 

            da.SelectCommand = cmdselect;

 

            DataSet Curriculat = new DataSet();

 

            try

            {

                con.Open();

 

                da.Fill(Curriculat);

            }

            catch (SqlException)

            {

                throw;

            }

            finally

            {

                con.Close();

            }

            return Curriculat;

 

        }

    }

}

 

Am not going to explain what is happening here, I think I have explained enough from my previous articles. Here we connect to the database and execute a storedprocedure and save data in a dataset and wait for the BLL to call this code.

 
BLL
 

In the BLL we call the function we created in DAL and wait for the PL(Presentation layer) to call it . make sure that your BLL likes like this.

 

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.SqlClient;

 

namespace BLL

{

    public class BLL

    {

 

        public DataSet GetData()

        {

            DAL.DAL obj = new DAL.DAL();

 

            DataSet Curriculat = new DataSet();

 

            try

            {

            Curriculat =  obj.GetData();

           

            }

            catch(SqlException)

            {

                throw;

            }

            return Curriculat;

 

 

        }

 

    }

}

 

Go to your PL(Presentation Layer), your Aspx page and double click, this will lie under the code behind code. And write the Following  code. 

 

    private void GetData()

    {

        DataSet Curriculat = new DataSet();

 

        BLL.BLL obj = new BLL.BLL();

 

        Curriculat = obj.GetData();

 

 

        GridView1.DataSource = Curriculat;

 

        GridView1.DataBind();

   }

 
 

Now this will Bind your Dataset to the Datagrid, what next time you juist call the function.

 

On your button Click event  call the Function to Bind the Gridview like this.

    protected void btnShowData_Click(object sender, EventArgs e)

    {

        GetData();

    }

 

Go back to the Design view and click the Gridview and a Property window will appear. Click on the Sign that looks like a lightning.

 
 
 
 

And double click the GridView1_PageIndexChanging event and make sure it looks like this.

 protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)

    {

        GridView1.PageIndex = e.NewPageIndex;

        GetData();

    }

 

When you are done build and press “F5” to admire your work

 

And you should see something like this

 
 
 

Click on the Numbers at the Botton to navigate to another page.

 

Don’t say am Good, I just asked how it was done without a Wizard so that I can continue without using a Wizard.

 
Conclusion
 

On the Next Article we will Update,Delete and Insert into the Grid. On my Previos articles we did it in Windows Application and now we are going to do it via Web. This Article is Dedicated to (www.ITS.co.za) and Kamogelo Mokgoro from PostOffice, why this is the first article am writing since I joined them on the  10 December 2008.

 

Thank you.

Page copy protected against web site content infringement by Copyscape

About the Author

Vuyiswamb
Full Name: Vuyiswa Maseko
Member Level: NotApplicable
Member Status: Member,MVP,Administrator
Member Since: 7/6/2008 11:50:44 PM
Country: South Africa
Thank you for posting at Dotnetfunda [Administrator]
http://www.Dotnetfunda.com
Vuyiswa Junius Maseko is a Founder of Vimalsoft (Pty) Ltd (http://www.vimalsoft.com/) and a forum moderator at www.DotnetFunda. Vuyiswa has been developing for 16 years now. his major strength are C# 1.1,2.0,3.0,3.5,4.0,4.5 and vb.net and sql and his interest were in asp.net, c#, Silverlight,wpf,wcf, wwf and now his interests are in Kinect for Windows,Unity 3D. He has been using .net since the beta version of it. Vuyiswa believes that Kinect and Hololen is the next generation of computing.Thanks to people like Chris Maunder (codeproject), Colin Angus Mackay (codeproject), Dave Kreskowiak (Codeproject), Sheo Narayan (.Netfunda),Rajesh Kumar(Microsoft) They have made vuyiswa what he is today.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)