Introduction

In our previous article, we described binding a worksheet to database with the help of a GUI tool called Worksheets Designer. But, we also realize that developers may also like to bind their worksheets with database at runtime programmatically instead of using Worksheets Designer. So, in this article, we will again start our discussion about data binding but without Worksheets Designer.

 

Binding a Worksheet with Database at Runtime

Aspose.Grid.Web also provides an easiest API that can be used by developers to bind their worksheets with database programmatically without using Worksheets Designer. Let's create a sample application to practice this approach.

IMPORTANT: Assume that we have already created oleDbConnection1, oleDbDataAdapter1 & dataSet11 objects. For more details, please refer to Step 1 , Step 2 , Step 3 , Step 4 & Step 5 explained in the article i.e., Binding Worksheet to a DataSet using  Worksheets Designer editor.

 

Steps to Follow

To bind a worksheet with database, following step should be followed:

• Access the worksheet that will be bound with database
• Specify a DataSource (which could be a DataSet, DataTable or DataView etc.) for the worksheet
• Create columns bound to the DataSource
• Create Validations, if required. For our application, we will create a REQUIRED validation for ProductID column
• Specify Number Format Settings, if required. For our application, we will set the Number Format of ProductPrice column to Currency3
• Load and Fill DataSet
• Bind worksheet with DataSet

NOTE: We will add all code to Page_Load event handler of our Web Form and before adding code to Page_Load event handler, make sure that there is not any PostBack because data should be loaded and bound only once.

Example:

[C#]

 

 

//Implementing Page_Load event handler

private void Page_Load(object sender, System.EventArgs e)

{

   //Checking if there is not any PostBack

   if (!IsPostBack)

   {

        //Accessing a desired worksheet

        WebWorksheet sheet = GridWeb1.WebWorksheets[0];

 

 

        //Specifying Data Source for the worksheet

        sheet.DataSource = dataSet11;

 

 

        //Specifying Products tables as the DataMember

        sheet.DataMember = "Products";

 

 

        //Creating data bound columns automatically

        sheet.CreateAutoGenratedColumns();                       

 

 

        //Creating REQUIRED validation for ProductID column

        Validation v = new Validation();

        v.IsRequired = true;

        sheet.BindColumns[0].Validation = v;

 

 

        //Setting Number Format of ProductPrice column to Currency3

        sheet.BindColumns[2].NumberType = NumberType.Currency3;

 

 

        try

        {

                //Filling DataSet

                oleDbDataAdapter1.Fill(dataSet11);

        }

        finally

        {

                //Closing database connection

                oleDbConnection1.Close();

        }

 

 

        //Binding worksheet with DataSet

        sheet.DataBind();

   }

}

 

 

[VB.NET]

 

 

'Implementing Page_Load event handler

Private  Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load

   'Checking if there is not any PostBack

   If Not IsPostBack Then

        'Accessing a desired worksheet

        Dim sheet As WebWorksheet =  GridWeb1.WebWorksheets(0)

 

 

        'Specifying Data Source for the worksheet

        sheet.DataSource = dataSet11

 

 

        'Specifying Products tables as the DataMember

        sheet.DataMember = "Products"

 

 

        'Creating data bound columns automatically

        sheet.CreateAutoGenratedColumns()                       

 

 

        'Creating REQUIRED validation for ProductID column

        Dim v As Validation =  New Validation()

        v.IsRequired = True

        sheet.BindColumns(0).Validation = v

 

 

        'Setting Number Format of ProductPrice column to Currency3

        sheet.BindColumns(2).NumberType = NumberType.Currency3

 

 

        Try

                'Filling DataSet

                oleDbDataAdapter1.Fill(dataSet11)

        Finally

                'Closing database connection

                oleDbConnection1.Close()

        End Try

 

 

        'Binding worksheet with DataSet

        sheet.DataBind()

   End If

End Sub

 

Finally, compile and run this web application by either pressing Ctrl+F5 or clicking Start button of Visual Studio.NET IDE. After the Web Form is loaded, you would see data in the worksheet loaded from database as shown below in the figure:

 

 

Figure: Worksheet bound with the Products table

 

For performing other operations on data like adding, editing and deleting rows, you can use the right click menu of GridWeb control. Such operations are already discussed in Binding Worksheet to a DataSet Using Worksheets Designer.

Conclusion

Aspose.Grid.Web provides a very simple and easy to use API that can be learned by developers quickly without much efforts. Aspose.Grid.Web saves the time and efforts of developers by performing complex tasks with just a few lines of code.