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

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.
Step1
Click to Zoom
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.
Step2
Click To Zoom
Step 3 Now, create table. Right click on Tables in the left pane under the database name and select New Table.
Step3
Click to Zoom
Step 4 Now create table with following configuration and give the table name Library. Press Ctrl + S to save the table.
Click To Zoom
Click To Zoom
Step 5 Add some demo data to table. Right click on your table Library and Select Data.
Click to Zoom
Click to Zoom
Step 6 Add some demo data in the table.
Click to Zoom
Click to Zoom

Testing Connection and Fetching Data to WebPage


  • 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.
    steps1
    Click to Zoom
    Step 2 Click on New > SQL Server Connection in Home ribbon.
    Click to Zoom
    Click to Zoom
    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.
    Click to Zoom
    Click to Zoom

    Now write program to access data from dbtutorial

    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.


    Leave a Reply

    Your email address will not be published. Required fields are marked *