Razor with Database – Insert, Update, Search and Delete

In this chapter you will learn

Insert, update, search and delete data from database using Razor Syntax
Programming Example

In previous chapter you learned how to create and connect to a database in razor syntax. In this chapter I will show you how you can Insert, update and delete data.

Programming Example

@{   
    var sqldb=Database.Open("Database Tutorial");    
}
 
<!DOCTYPE html>
 
<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title></title>
    </head>
    <body>
       @{
           //Insert a Row           
           var sqlinsert = "INSERT INTO Library (BookName,Author) Values(@0,@1)";           
           sqldb.Execute(sqlinsert,"SQL","Clark");
 
           //Update a Row
           var sqlupdate = "UPDATE Library SET BookName=@0 where Author=@1";
           sqldb.Execute(sqlupdate,"MySQL","Clark"); 
           
           //Delete a Row
           var sqldelete = "DELETE FROM Library where ID=@0";
           sqldb.Execute(sqldelete,"15");
 
           //Search a Row
           var sqlsearch = "SELECT * FROM Library where ID=4";
           foreach(var sd in sqldb.Query(sqlsearch))
           {   
               <h2>ID : @sd.ID, Book Name : @sd.BookName, Author : @sd.Author</h2>   
           }
 
           //Show Data
           var sqlquery="SELECT * FROM Library";
           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 : 4, Book Name : Android Programming, Author : DiMarzio Jerome

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
________________________________________
ID : 11
Book Name : JavaScript
Author : BalaGuruSwami
________________________________________
ID : 16
Book Name : MySQL
Author : Clark
________________________________________

Explanation

Insert a Row

var sqlinsert = "INSERT INTO Library (BookName,Author) Values(@0,@1)";           
sqldb.Execute(sqlinsert,"SQL","Clark");

You cannot directly insert a value in INSERT query. You need to use place holder(@) starting with 0. That’s why I used Values(@0,@1) for value and passed real string value in sqldb.Execute(sqlinsert,"SQL","Clark") command.

Update a Row

var sqlupdate = "UPDATE Library SET BookName=@0 where Author=@1";
sqldb.Execute(sqlupdate,"MySQL","Clark");

You cannot insert direct value in update command. You need to use place holder (@) instead of value. You can pass respective value in sqldb.Execute(sqlupdate,"MySQL","Clark"); command.

Delete a Row

var sqldelete = "DELETE FROM Library where ID=@0";
sqldb.Execute(sqldelete,"15");

You cannot pass value directly to delete command. You need to use place holder (@) in delete command. Next in sqldb.Execute(sqldelete,"15") command you have to pass value.

Summary

In this chapter you learned how to Insert, update and delete data from database in Razor Syntax. However, it is very basic operation but you can study in details in demo project. In the next chapter you will learn WebGrid to Display data with ease.