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.

In order to run this sample, you will need Visual Studio 2015 and SQL Server 2014.

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

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:

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.

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:

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:

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):

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:

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:

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.

About the Author

One of GrapeCity's technical engagement engineers, Kevin Ashley enjoys trying to solve complex coding problems. After graduating from North Carolina State University with a bachelor's degree in Computer Science, he began his career in the software industry at GrapeCity. In his spare time, you can find Kevin playing guitar, drawing, or developing video games.