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

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.