Using CRUD operations with Spread for ASP.NET provides useful database functionality along with an intuitive interface. Spread for ASP.NET has built-in functionality for connecting to a SQL Data Source that can be accomplished in the Design view of Visual Studio. This blog shows another way to implement data binding with Spread ASP. To see data binding with ADO.NET, check out this blog: Spread ASP CRUD ADO.NET Binding The download for this sample can be found here: Spread ASP CRUD SpreadDataSource In order to run this sample, you will need Visual Studio 2015 and SQL Server 2014. The finished data-bound Spread instance

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. Rather than adding it to the project, we are going to add it directly to the form as a SqlDataSource. To do this, double-click the SqlDataSource control under the Data section in the Toolbox in Visual Studio. Select Configure Data Source in the pop-up menu, and select New 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. Once you are back at the Configure Data Source window, keep all of the options as their defaults and click Finish. This will configure the data source so that it can be used with Spread. The Add Connection window in Visual Studio

Connect to the Database

Now that the data source has been added to the form, we can connect to that data source with the SmartTags interface of the Spread instance and use the Choose Data Source drop-down menu. The SmartTags for the Spread control in Visual Studio. This will connect the Spread instance to the data source automatically, but we have to add the code for the Update and Insert commands inside the tag for the SqlDataSource in the markup for the page:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CRUDSampleConnectionString %>"   
    SelectCommand="SELECT [EmployeeID], [EmployeeName], [Age], [OfficeID], [Link], [Employed] FROM [Employees]"  
    UpdateCommand="UPDATE Employees SET EmployeeName = @EmployeeName, Age = @Age, OfficeID = @OfficeID, Link = @Link, Employed = @Employed WHERE EmployeeID = @EmployeeID"  
    InsertCommand="INSERT INTO Employees (EmployeeID, EmployeeName, Age, OfficeID, Link, Employed) VALUES (@EmployeeID, @EmployeeName, @Age, @OfficeID, @Link, @Employed)"  
    DeleteCommand="DELETE FROM Employees WHERE EmployeeID = @EmployeeID">  

    <UpdateParameters>  
        <asp:Parameter Name="EmployeeName" Type="String" />  
        <asp:Parameter Name="Age" Type="Int32" />  
        <asp:Parameter Name="OfficeID" Type="Int32" />  
        <asp:Parameter Name="Link" Type="String" />  
        <asp:Parameter Name="Employed" Type="Boolean" />  
    </UpdateParameters>  

    <InsertParameters>  
        <asp:Parameter Name="EmployeeID" Type="Int32"/>  
        <asp:Parameter Name="EmployeeName" Type="String" />  
        <asp:Parameter Name="Age" Type="Int32" />  
        <asp:Parameter Name="OfficeID" Type="Int32" />  
        <asp:Parameter Name="Link" Type="String" />  
        <asp:Parameter Name="Employed" Type="Boolean" />  
    </InsertParameters>  

    <DeleteParameters>  
        <asp:Parameter Name="EmployeeID" Type="Int32"/>  
    </DeleteParameters>  
</asp:SqlDataSource>  

Additionally, go through the same steps as above, but for the Offices table instead, and don’t add the extra code for the Update, Insert, or Delete commands. Add code to allow inserting and deleting rows. Also, add code to protect the column used for the table’s key:

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

Outside of the Page_Load function, define two DataView variables so that we can access the DataView for each data source in other areas of the web form:

DataView employeesView, officesView;  

Once those are defined, initialize them in the Page_Load function:

employeesView = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);  
officesView = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);  

Now that we have the officesView, we can initialize the Spread instance with specific cell types and column widths. The ComboBoxCellType will be using the officesView as a data source, so it can display the names of the offices rather than their IDs:

FarPoint.Web.Spread.ComboBoxCellType cb = new FarPoint.Web.Spread.ComboBoxCellType();  
cb.DataSource = officesView;  
cb.ShowButton = true;  
cb.DataMember = "Office";  
cb.DataTextField = "officeName";  
cb.DataValueField = "officeID";  
cb.UseValue = true;  
for (int r = 0; r < FpSpread1.ActiveSheetView.RowCount; r++)  
{  
    FpSpread1.ActiveSheetView.Cells[r, 3].CellType = cb;  
}  
FpSpread1.ActiveSheetView.Columns[1].Width = 200;  
FpSpread1.ActiveSheetView.Columns[3].Width = 200;  
FpSpread1.ActiveSheetView.Columns[4].Width = 300;  

Bind Spread Events to Make Database Changes

Now that the SqlDataSources are set up, we can bind Spread events to those commands with the CommandBar buttons. The first one we can implement is the UpdateCommand. Since this is handled by the data source and Spread, we don’t have to implement the handler, but you can if you want to add more functionality to it. 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)  
{   
}  

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. Just like the previous Spread ASP CRUD blog, 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)  
{  
    if (e.CommandName == "AddRow")  
    {  
        // Get the row count for use in incrementing the employeeId  
        int rowCount = FpSpread1.ActiveSheetView.RowCount;  
        // Convert the employeesView DataView to a DataTable  
        DataTable dt = employeesView.ToTable();  
        // Add a new row to that DataTable  
        var newRow = dt.NewRow();  
        newRow["EmployeeID"] = (int)(dt.Rows[dt.Rows.Count - 1]["EmployeeID"]) + 1;  
        dt.Rows.Add(newRow);  

        // Perform the insert with the new DataTable  
        SqlDataSource1.InsertParameters["EmployeeID"].DefaultValue = ((int)(dt.Rows[dt.Rows.Count - 1]["EmployeeID"])).ToString();  
        SqlDataSource1.Insert();  

        // Create the cell types for the new row  
        FarPoint.Web.Spread.ComboBoxCellType cb = new FarPoint.Web.Spread.ComboBoxCellType();  
        cb.DataSource = officesView;  
        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;  

        // Set those cell types for specific cells in the new row  
        FpSpread1.ActiveSheetView.Cells[rowCount, 4].CellType = hl;  
        FpSpread1.ActiveSheetView.Cells[rowCount, 5].CellType = chkbx;  
    }  
    else if (e.CommandName == "DeleteRow")  
    {  
        // Remove the active row from the Spread instance  
        FpSpread1.ActiveSheetView.RemoveRows(FpSpread1.ActiveSheetView.ActiveRow, 1);  

        // Delete that row in the DataSource as well  
        SqlDataSource1.Delete();  
    }  
}  

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 Spread ASP’s interface and built-in Data Binding functionality with a SQL Server Data Base with CRUD operations. The finished data-bound Spread instance