Spread ASP CRUD ADO.NET Binding

Using CRUD operations with Spread for ASP.NET provides useful database functionality along with an intuitive interface. You can use ADO.NET code and a SQL Server with Spread for ASP.NET with just a little bit of coding to connect everything. The download for this sample can be found here: SpreadASP CRUD ADO.NET Binding Sample In order to run this sample, you will need Visual Studio 2015 and SQL Server 2014. Finished SpreadASP Page with ADO.NET Data Binding Finished SpreadASP Page with ADO.NET Data Binding

Set Up the Project

Create a new empty ASP.NET Web project, and add a new web form. Add an instance of FpSpread to the web form via the toolbox. Now that an instance of Spread is on your page, create the database connection for use in your project. In this example, we will be using the CRUDSample.mdf database provided. To add it to Visual Studio, open the Server Explorer window, right-click on the Data Connections entry, and click Add Connection…. Once the Add Connection window opens, change the data source to Microsoft SQL Server Database File (SqlClient), then browse to the CRUDSample.mdf file. Keep the selection as Use Windows Authentication, and click on Test Connection to make sure it works. Click OK to close the window and add the database connection. The Add Connection window in Visual Studio 2015 The Add Connection window in Visual Studio 2015

Connect to the Database

Now that the database has been added to Visual Studio, we have to write code to actually connect the Spread instance to the database using SqlDataAdapters. First, define a DataSet variable and a SqlDataAdapter for the Employees table outside of the Page_Load function, as we will need access to these variable later on:


SqlDataAdapter employeeDataAdapter;  
DataSet ds;  
protected void Page_Load(object sender, EventArgs e)  
{  
}

Add some code to allow inserting and deleting rows, as well as protecting the column we will be using for the table’s key:


FpSpread1.ActiveSheetView.AllowInsert = true;  
FpSpread1.ActiveSheetView.AllowDelete = true;  
FpSpread1.ActiveSheetView.Protect = true;  
FpSpread1.ActiveSheetView.Columns[0].Locked = true;

Inside of that Page_Load function, write a try/catch statement for containing our code to set up the data adapters. For clarification, the database has the following tables and columns:

  • Employees Table
    • EmployeeName
    • Age
    • OfficeID (This is connected to the Offices Table)
    • Link
    • Employed
  • Offices Table
    • OfficeID
    • OfficeName
    • Country
  • Products Table (not used in this example, but added as an alternative table)
    • ProductID
    • ProductName
    • EmployeeID
    • OfficeID

The DataTable as viewed in Visual Studio. The DataTable as viewed in Visual Studio. Inside that try/catch statement, define a connection string and then initialize the SqlDataAdapters. The connection string can be found by selecting the database in the Server Explorer and then opening the Properties window to find the Connection String property. Use this as your connection string in code (it might have to be changed to add escape characters for specific characters in the string). This is how the code looks for my specific machine:


var connectionString = @"Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=""C:\\Users\\admin\\Documents\\Samples\\SpreadASP CRUD Sample\\SpreadASP CRUD\\SpreadASP CRUD\\CRUDSample.mdf"";Integrated Security=True;Connect Timeout=30";  

employeeDataAdapter = new SqlDataAdapter("SELECT * FROM dbo.Employees", connectionString);  
var officeDataAdapter = new SqlDataAdapter("SELECT * FROM dbo.Offices", connectionString);  
var productDataAdapter = new SqlDataAdapter("SELECT * FROM dbo.Products", connectionString);  
ds = new DataSet();  

//Get data  
employeeDataAdapter.Fill(ds, "Employees");  
officeDataAdapter.Fill(ds, "Offices");  
productDataAdapter.Fill(ds, "Products");

In this example, we are going to be using the Employees table as the main data source, so we have to add the SqlCommands for Updating, Inserting, and Deleting from the database:


// Add update command for employee data adapter  
SqlConnection connection = new SqlConnection(connectionString);  

// Create the UpdateCommand for the employeeDataAdapter  
SqlCommand command = new SqlCommand(  
    "UPDATE Employees SET EmployeeName = @EmployeeName, Age = @Age, OfficeID = @OfficeID, Link = @Link, Employed = @Employed " +  
    "WHERE EmployeeID = @EmployeeID", connection);  
// Add the parameters for the UpdateCommand  
command.Parameters.Add("@EmployeeName", SqlDbType.NVarChar, 50, "EmployeeName");  
command.Parameters.Add("@Age", SqlDbType.Int, 3, "Age");  
command.Parameters.Add("@OfficeID", SqlDbType.Int, 5, "OfficeID");  
command.Parameters.Add("@Link", SqlDbType.NVarChar, 4000, "Link");  
command.Parameters.Add("@Employed", SqlDbType.Bit, 1, "Employed");  
SqlParameter parameter = command.Parameters.Add(  
    "@EmployeeID", SqlDbType.Int);  
parameter.SourceColumn = "EmployeeID";  
parameter.SourceVersion = DataRowVersion.Original;  
employeeDataAdapter.UpdateCommand = command;  

// Create the InsertCommand for the employeeDataAdapter  
command = new SqlCommand(  
    "INSERT INTO Employees (EmployeeID, EmployeeName, Age, OfficeID, Link, Employed) " +  
    "VALUES (@EmployeeID, @EmployeeName, @Age, @OfficeID, @Link, @Employed)", connection);  
// Add the parameters for the InsertCommand.  
command.Parameters.Add("@EmployeeID", SqlDbType.Int, 5, "EmployeeID");  
command.Parameters.Add("@EmployeeName", SqlDbType.NVarChar, 50, "EmployeeName");  
command.Parameters.Add("@Age", SqlDbType.Int, 3, "Age");  
command.Parameters.Add("@OfficeID", SqlDbType.Int, 5, "OfficeID");  
command.Parameters.Add("@Link", SqlDbType.NVarChar, 4000, "Link");  
command.Parameters.Add("@Employed", SqlDbType.Bit, 1, "Employed");  
employeeDataAdapter.InsertCommand = command;  

// Create the DeleteCommand for the employeeDataAdapter  
command = new SqlCommand(  
    "DELETE FROM Employees WHERE EmployeeID = @EmployeeID", connection);  
// Add the parameters for the DeleteCommand.  
parameter = command.Parameters.Add(  
    "@EmployeeID", SqlDbType.NChar, 5, "EmployeeID");  
parameter.SourceVersion = DataRowVersion.Original;  
employeeDataAdapter.DeleteCommand = command;  

// Load data table here from MDF file  
FpSpread1.DataSource = ds.Tables[0];  
FpSpread1.DataBind();

In the Employees table, the OfficeID corresponds to a specific office in the Offices table. In the instance of Spread, we will have a ComboBoxCellType that connects to that table, getting the names of the offices from the office id. In the following code, set the data source of the ComboBoxCellType and set the other cell types as well:


FarPoint.Web.Spread.ComboBoxCellType cb = new FarPoint.Web.Spread.ComboBoxCellType();  
cb.DataSource = ds.Tables[1];  
cb.ShowButton = true;  
cb.DataMember = "Office";  
cb.DataTextField = "officeName";  
cb.DataValueField = "officeID";  
cb.UseValue = true;  

FarPoint.Web.Spread.CheckBoxCellType chkbx = new FarPoint.Web.Spread.CheckBoxCellType();  

for (int r = 0; r < FpSpread1.ActiveSheetView.RowCount; r++)  
{  
    FpSpread1.ActiveSheetView.Cells[r, 3].CellType = cb;  
    FarPoint.Web.Spread.HyperLinkCellType hl = new FarPoint.Web.Spread.HyperLinkCellType();  
    hl.NavigateUrl = FpSpread1.ActiveSheetView.Cells[r, 4].Text;  

    FpSpread1.ActiveSheetView.Cells[r, 4].CellType = hl;  
    FpSpread1.ActiveSheetView.Cells[r, 5].CellType = chkbx;  
}  
FpSpread1.ActiveSheetView.Columns[3].Width = 200;  
FpSpread1.ActiveSheetView.Columns[4].Width = 300;

Bind Spread Events to Make Database Changes

Now that we have the SqlCommands for making changes to the database, we have to actually connect the buttons on the CommandBar to those commands. The first one we will implement is the UpdateCommand. It will need to get the changes from the Spread instance and update them to the database using the data adapter. Attach an event handler to the OnUpdateCommand event (in this application, I used FpSpread_UpdateCommand):


protected void FpSpread1_UpdateCommand(object sender, FarPoint.Web.Spread.SpreadCommandEventArgs e)  
        {  
            int colcnt;  
            int r = (int)e.CommandArgument;  
            colcnt = e.EditValues.Count - 1;  

            for (int i = 0; i <= colcnt; i++)  
            {  
                if (!object.ReferenceEquals(e.EditValues[i], FarPoint.Web.Spread.FpSpread.Unchanged))  
                {  
                    // Change the values of the checkbox to be more database-friendly  
                    if (e.EditValues[i].Equals("false"))  
                        e.EditValues[i] = 0;  
                    else if (e.EditValues[i].Equals("True"))  
                        e.EditValues[i] = 1;  
                    // Set the values from the changed row into the row in the DataTable  
                    ds.Tables[0].Rows[r][i] = e.EditValues[i];  
                }  
            }  
            // Update the data adapater with the changed DataTable  
            employeeDataAdapter.Update(ds.Tables[0]);  
            e.Handled = true;  
        }

The next commands to implement are the Add and Delete commands. Since we want to have specific functionality with adding and removing rows, such as setting cell types, we will need to implement our own event for clicking on the CommandBar buttons. To do this, we need to override the default behavior of those button clicks using the Render function:


protected override void Render(System.Web.UI.HtmlTextWriter writer)  
{  
    WebControl addBtn = (WebControl)FpSpread1.FindControl("Add");  
    addBtn.Attributes.Add("onclick", "javascript:" + ClientScript.GetPostBackEventReference(FpSpread1, "AddRow") + "; return false;");  

    WebControl deleteBtn = (WebControl)FpSpread1.FindControl("Delete");  
    deleteBtn.Attributes.Add("onclick", "javascript:" + ClientScript.GetPostBackEventReference(FpSpread1, "DeleteRow") + "; return false;");  

    base.Render(writer);  
}

The above code binds the Add button to a callback command called “AddRow”, and the Delete button to a callback command called “Delete”. These commands can be handled in the ButtonCommand event of Spread. Similar to the Update command, attach an event handler for the OnButtonCommand update (in this case, I called it “FpSpread1_ButtonCommand”). The event handler will need to check for which command was fired, and then update the data source accordingly:


protected void FpSpread1_ButtonCommand(object sender, FarPoint.Web.Spread.SpreadCommandEventArgs e)  
{  
    // Check to see which command was fired  
    if (e.CommandName == "AddRow")  
    {  
        int rowCount = FpSpread1.ActiveSheetView.RowCount;  

        // Add a new row to the DataTable and update the DataBase.  
        // We will need this in order to set the cell types since the Spread instance  
        // is bound to a DataSource.  
        var newRow = ds.Tables[0].NewRow();  
        newRow["EmployeeID"] = (int)(ds.Tables[0].Rows[ds.Tables[0].Rows.Count - 1]["EmployeeID"]) + 1;  
        ds.Tables[0].Rows.Add(newRow);  

        employeeDataAdapter.Update(ds.Tables[0]);  

        // Just like when we initialized it, create and set the different cell types   
        // to place in the newly added row.  
        FarPoint.Web.Spread.ComboBoxCellType cb = new FarPoint.Web.Spread.ComboBoxCellType();  
        cb.DataSource = ds.Tables[1];  
        cb.ShowButton = true;  
        cb.DataMember = "Office";  
        cb.DataTextField = "officeName";  
        cb.DataValueField = "officeID";  
        cb.UseValue = true;  
        FpSpread1.ActiveSheetView.Cells[rowCount, 3].CellType = cb;  

        FarPoint.Web.Spread.CheckBoxCellType chkbx = new FarPoint.Web.Spread.CheckBoxCellType();  
        chkbx.AutoPostBack = false;  
        FarPoint.Web.Spread.HyperLinkCellType hl = new FarPoint.Web.Spread.HyperLinkCellType();  
        hl.NavigateUrl = FpSpread1.ActiveSheetView.Cells[rowCount, 4].Text;  

        FpSpread1.ActiveSheetView.Cells[rowCount, 4].CellType = hl;  
        FpSpread1.ActiveSheetView.Cells[rowCount, 5].CellType = chkbx;  
    }  
    else if (e.CommandName == "DeleteRow")  
    {  
        // Delete a row from the DataTable.  
        var row = ds.Tables[0].Rows[FpSpread1.ActiveSheetView.ActiveRow];  
        row.Delete();  

        // Update the data adapter to reflect the change.  
        employeeDataAdapter.Update(ds.Tables[0]);  
    }  
}

When the page is running, it will display all of the data that was bound from the database in a format that makes it easier to read. It also provides CRUD operations to the user via the CommandBar at the bottom of the Spread instance on the page. This tutorial showed how to use SpreadASP’s interface with ADO.NET Data Binding and a SQL Server Data Base with CRUD operations. Finished SpreadASP Page with ADO.NET Data Binding Finished SpreadASP Page with ADO.NET Data Binding

MESCIUS inc.

comments powered by Disqus