WebMatrix Tutorial – Working with Data

In this tutorial we will focus on working with the inbuilt database (SQL Server CE 4) in WebMatrix. If you are new to WebMatrix please check out Part 1 of the WebMatrix Tutorial which deals with installing WebMatrix and working with the new ASP.NET Pages code files.

Working with the Database in WebMatrix

SQL Server CE is deeply integrated into WebMatrix, select Databases on the menu at bottom left of the interface and if you do not currently have an associated database you will be confronted with the below screen:

Click Add a Database to your site , then right-click Tables and select New Table.

You will then be able to add columns to your table (just click New Column on the ribbon for add the additional columns). You can specify the column details in the Column Properties settings list:

You can edit or add data by clicking Data in the ribbon, which will take you to the existing data in the database presented as an editable grid:

Of course you would not normally add data using this method but it is convenient for a quick edit or to add some dummy data.

Note that using WebMatrix does not mean that you are tied to using the inbuilt SQL Server CE, you can interact with any version of SQL Server or other databases supported by the .NET framework such as MySQL.

Accessing the Database using ASP.NET Pages in WebMatrix

The database can be easily accessed using the new database API – primarily using the Open, Query and Execute commands.

The Open command combined with the Query command can be used to access the data in the database:

You can use queries in parameters to generate dynamic pages. Note the syntax of the Query command of the database API, which can accept numbered parameters:

Updating the Database using ASP.NET Pages in WebMatrix

The Execute command of the Database object can be used to insert, update or delete data from the database. The first paramater of the Execute command accepts the SQL string for the database operation and the subsequent parameters are for the parameters to be inserted into the SQL string.

Publish and Deploy using WebMatrix

Publishing a web app is intended to be very simple using WebMatrix. If you click the Publish button on the ribbon you will have an option to either Configure or find a Web Host. Since most developers will probably have their own hosting, just click Configure.

From there you will be taken to the screen allowing you to configure your remote host or FTP server. The Protocol drop down allows you to select either Web Deploy, FTP/SSH, or FTP.

Web Deploy will only work with Microsoft’s selected Web Hosts, for most developers the familiar FTP protocol will be the most suitable. Simple select FTP and enter the FTP host details.

Here is where WebMatrix plays its trump card – seemless deployment of databases. Just enter the FTP details in and upload the files, WebMatrix will automatically create binaries and datafiles for the database and no databse server or additional configuration is required.

Conclusion

WebMatrix is a powerful lightweight web development tool which sacrificially addresses the greatest problem in web development for new developers – the requirement to master so many different technologies and tools to create even a simple application. There are however, currently some major omissions. There is no debugging,  the coding tools have few features such as Intellisense and the database management/creation tool  is very basic. Hopefully these will be addressed as the product matures.

Related Posts:

• Working with ADO.NET Transactions
• WebMatrix Tutorial – Getting Started
• Implementing a Generic Data Access Layer in ADO.NET Part 3
• Implementing a Generic Data Access Layer in ADO.NET Part 1