Create and Connect to Database in Razor

In this chapter you will learn

How to create database?
How to connect to database?
How to display data in a webpage?

In this tutorial we will use local database file (.sdf) and sql database file(.mdf) for demonstration.

Using Local Database File (.sdf)

Creating Database

It is very easy to create database, create table and adding data in webmatrix. See how

Step 1 Launch WebMatrix and Create a New Project "Database Tutorial" with Empty site. In the left Pane click Databases and then click on Add a database to your site.

Step 2 As you will click on Add a database to your site, you will see that a new database with the same name of your project is created in the left pane.

Step 3 Now, create table. Right click on Tables in the left pane under the database name and select New Table.

Step 4 Now create table with following configuration and give the table name Library. Press Ctrl + S to save the table.

Step 5 Add some demo data to table. Right click on your table Library and Select Data.

Step 6 Add some demo data in the table.

Testing Connection and Fetching Data to WebPage

{
    var db = Database.Open("Database Tutorial");
    var query="SELECT * FROM Library";
}
 
<!DOCTYPE html>
 
<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title></title>
    </head>
    <body>
       @{
           foreach(var row in db.Query(query))
           {
               <span>ID : @row.ID</span><br />
               <span>Book Name : @row.BookName</span><br />
               <span>Author : @row.Author</span>
               <br /><hr /><br />
           }
       }
    </body>
</html>

Output

ID : 1
Book Name : C#
Author : Christian Nagel
________________________________________
ID : 2
Book Name : ASP.NET MVC
Author : Simone Chiaretta

________________________________________
ID : 3
Book Name : Microsoft SQL Server
Author : Paul Turley
________________________________________
ID : 4
Book Name : Android Programming
Author : DiMarzio Jerome
________________________________________
ID : 5
Book Name : Scala
Author : Janek Bogucki
________________________________________
ID : 6
Book Name : .NET Core 1.0
Author : Christian Nagel
________________________________________
ID : 7
Book Name : Visual Studio 2015
Author : William Penberthy
________________________________________
ID : 8
Book Name : Swift IOS
Author : Abhishek Mishra
________________________________________
ID : 9
Book Name : Visual Basic 2015
Author : Bryan Newsome
________________________________________
ID : 10
Book Name : Python
Author : Luke Sneeringer
________________________________________

Using SQL Server Database File(.mdf)

You have seen how to work with local database file in webmatrix. You are a .Net Developer and most of the time you need to connect with SQL Database File. In this section I will show you how you can connect with SQL Database File (.mdf) in Razor.

All the processes are same. I assume that you have already a SQL Server Database file in SQL Server. You just need to make connection with database and you are ready for working with database.

Step 1 Select Database in Left Pane.

Step 2 Click on New > SQL Server Connection in Home ribbon.

Step 3 Give database name in Database Text Box. Select your server ( .\SQLEXPRESS) is default and you not need to change. Select Authentication Mode. Choose between Windows Authentication or SQL Server Authentication.

Now write program to access data from dbtutorial

@{   
    var sqldb=Database.Open("dbtutorial");
    var sqlquery="SELECT * FROM dbLibrary";
}
 
<!DOCTYPE html>
 
<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title></title>
    </head>
    <body>
       @{
           foreach(var row in sqldb.Query(sqlquery))
           {
               <span>ID : @row.ID</span><br />
               <span>Book Name : @row.BookName</span><br />
               <span>Author : @row.Author</span>
               <br /><hr /><br />
           }
       }
    </body>
</html>

Output

ID : 1
Book Name : C#
Author : Christian Nagel
________________________________________
ID : 2
Book Name : ASP.NET MVC
Author : Simone Chiaretta
________________________________________
ID : 3
Book Name : Microsoft SQL Server
Author : Paul Turley
________________________________________
ID : 4
Book Name : Android Programming
Author : DiMarzio Jerome
________________________________________
ID : 5
Book Name : Scala
Author : Janek Bogucki
________________________________________
ID : 6
Book Name : .Net Core 1.0
Author : Christian Nagel
________________________________________
ID : 7
Book Name : Visual Studio 2015
Author : William Penberthy
________________________________________
ID : 8
Book Name : Swift IOS
Author : Abhishek Mishra
________________________________________
ID : 9
Book Name : Visual Basic 2015
Author : Bryan Newsome
________________________________________
ID : 10
Book Name : Python
Author : Luke Sneeringer
________________________________________

Summary

In this chapter you learned how to connect and create database using razor syntax. In the next chapter you will learn Insert, Update and Delete data using razor syntax.

Create and Connect to Database in Razor – SQL and Local DB

Using Local Database File (.sdf)

Creating Database

Testing Connection and Fetching Data to WebPage

Using SQL Server Database File(.mdf)

Summary

Share your thought