008 LINQ with MVC Razor

LINQ was introduced .Net Framework 3.5 (Visual Studio 2010).LINQ apply OOPS concept with relational database. MVC application  can be used with LINQ to retrieve and modify data from database and collections. You can use blank MVC Razor template for this purpose.In this example ,we have used a blank MVC Razor template and a local sql server database.

 

The first step is to design of view of the Project.MVC Razor uses "cshtml" file.We have added a file called "Student.cshtml" under a folder "Home". Now we have to design "cshtml" , which accept input Name,Roll,Age.

---------------------------Student.cshtml------------------------------------

@{

Layout = null;

}

 @using (Html.BeginForm("SaveCmd", "Home", FormMethod.Post, new { id = "submitForm" }))

{

        <div style="width:500px;">

        <fieldset>

        <legend>Student:</legend>

                  <table>

                         <tr>

                             <td>

                                    Name :

                             </td>

                            <td>

                                    <input id="txtName" name="txtName" />

                           </td>

                       </tr>

                      <tr>

                          <td>

                                 Roll :

                         </td>

                         <td>

                                <input id="txtRoll" name="txtRoll" />

                         </td>

                     </tr>

                    <tr>

                    <td>

                            Age :

                    </td>

                   <td>

                               <input id="txtAge" name="txtAge" />

                  </td>

                 </tr>

                  <tr>

                  <td>

                          <button type="submit" id="btnSave" name="Command" value="Save">

                          Save</button>

                 </td>

                <td>

                </td>

             </tr>

          </table>

</fieldset>

</div>

<br />

<span>List of Student</span>

<br />

<br />

<table>

                 @foreach (var student in ViewBag.student)

                {

                      <tr>

                      <td>@student.Name

                     </td>

                     <td>|

                     </td>

                     <td>@student.Roll

                     <td>|

                     </td>

                     </td>

                     <td>@student.Age

                     </td>

                      </tr>

                    }

</table>

}

---------------------------------------------------------------

The output will be see like this.

The interface can accept  name ,roll , age of a student and a save button for saving data and a for loop to show the data in tabular format.

Now from the left hand side of the project side tab , click on "Database Explorer" ,Add database connection and add new database database ,then you will find tables. We have a already designed a table , names "student".

 

Now from the project,right mouse click, Add-->New Item -->LINQ to SQL class. After adding LINQ to SQL class,double click on the class and drag your database table over it. A graphical representation of SQL table will be drawn over "dbml" file.

 

Now go to "Home" controller , create an instance of a line LINQ to SQL class file and set the connection constructor with database during the instance creation. 

LINQtoSQLDataContext db = new LINQtoSQLDataContext(you connection string)

In this project, "Save" submit will call "SaveCmd" action under "Home" controller.FormCollection will return the values of the posted files.Now create a object of the student class, fill the object with the data from the input text boxes. When click on save button , you will see ,data has been insertedto the databases tables successfully.Below is the code for insert data.

public ActionResult SaveCmd( FormCollection fc )

{

           

LINQtoSQLDataContext db = new LINQtoSQLDataContext(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\aa\tutorial.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");

               string mName = fc["txtName"].ToString();

               string mRoll = fc["txtRoll"].ToString();

               string mAge = fc["txtAge"].ToString();

              student obj = new student();

              obj.Name = mName;

              obj.Roll = Convert.ToInt16(mRoll);

              obj.Age = Convert.ToInt16(mAge);

             db.students.InsertOnSubmit(obj);

             db.SubmitChanges();

            return View("~/Views/Home/Student.cshtml");

}

Now write a select query on the table and fill it to a viewbag.In  "Student.cshtml" , a for loop is written which retrieve data from viewbag and create a tabular display of data.

public ActionResult Index()

{

                           LINQtoSQLDataContext db = new LINQtoSQLDataContext(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\aa\tutorial.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");

                          var stu = (from ord in db.students 

                      select ord).ToList<student>();

                          ViewBag.student = stu;

                         return View("~/Views/Home/Student.cshtml");

}

Output