header1.html

Friday 16 August 2013

CRUD operation using Entity Framework and LINQ



This article is for beginners who are new to EF Terminology and currently working on ADO.NET by creating DAL,BAL to interact with database.
First question comes in to picture is What is Entity Framework?
Lets see the definition given by Microsoft-"The Microsoft ADO.NET Entity Framework is an Object/Relational Mapping (ORM) framework that enables developers to work with relational data as domain-specific objects, eliminating the need for most of the data access plumbing code that developers usually need to write. Using the Entity Framework, developers issue queries using LINQ, then retrieve and manipulate data as strongly typed objects. The Entity Framework’s ORM implementation provides services like change tracking, identity resolution, lazy loading, and query translation so that developers can focus on their application-specific business logic rather than the data access fundamentals."
So,Entity Framework is ORM(Object/Relational Mapping) framework.
ORM helps to keep database design separate from domain Class design and it also improves standard CRUD (Create, Read, Update & Delete)operation.
Entity Framework 4.1 have multiple modeling techniques like code first, model first or database first.Here we are going to implement Database first approach. In this article we are also going to implement LINQ(Language Integrated Query)to perform CRUD operation with entity model.
To implement Database/Model first approach we need to create database first,
Query to create Database in SQL Server:

CREATE DATABASE MYCART

Query To create PRODUCTS table in MYCART Database:

USE MYCART
CREATE TABLE PRODUCTS
(
 Product_ID int IDENTITY(100,1) NOT NULL primary key,
 Product_Name varchar(20),
 Product_Desc varchar(50),
 Product_Price money,
 Quantity int
)

Create New ASP.NET Application:Create application "EF_CRUD" and add AddProduct.aspx page to implement CREATE operation.
structureCareerWebHelper
Sample Structure

Create the First simple Entity Data Model (EDM):
Solution Explorer =>right click on project => Add => New Item. Select the Data Template => ADO.NET Entity Data Model and click Add.
edmx

Rename it as ProductModel.edmx

It opens Entity Data Model Wizard. Select "Generate from database" option and click "Next"

Now enter your credentials and your database details as follows.
CareerWebHelperEM

StepsEF

By double clicking on ProductModel.edmx, it will open EDM designer which displays all the entities for selected tables and relation between them.Now check Entity model will be generated like
ProductModel

CREATE/INSERT Operation:Check AddProduct.aspx.cs ---Download
Create instance of Context MYCARTEntities context = new MYCARTEntities();
protected void btnAdd_Click(object sender, EventArgs e)
        {
            try
            {

                PRODUCT objProduct = new PRODUCT();
                objProduct.Product_Name = txtName.Text;
                objProduct.Product_Desc = txtPdesc.Text;
                objProduct.Product_Price = decimal.Parse(txtPrice.Text);
                objProduct.Quantity = int.Parse(txtQuantity.Text);

                context.AddToPRODUCTS(objProduct);
                int result=context.SaveChanges();
                if (result != 0)
                {
                    lblMsg.Text = "Product Added!!";  
                }
                else
                {
                    lblMsg.Text = "Failed!!";
                }
                

            }
            catch (Exception ex)
            {

                lblMsg.Text = "Failed!!";
                throw ex;
            }
        }

SaveChanges() method on the context to reflect the changes back to database.This will insert the record into the table.

Now, READ,UPDATE and DELETE OPERATION:1.Add ModifyProduct.aspx page to Project
2.Add GridView to page "gvModifyProducts"
3.Use Template fields and Bound Fields to bind Data to GridView.
4.Create following Method to implement READ Operation
private void BindProductDetails()
        {
            try
            {
                var myData = from c in context.PRODUCTS select new { c.Product_ID, c.Product_Name, c.Product_Desc,       c.Product_Price,c.Quantity };
                gvModifyProducts.DataSource = myData;
                gvModifyProducts.DataBind();
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
        
        
        }

5.Now handle Following events
protected void gvModifyProducts_RowEditing(object sender, GridViewEditEventArgs e)
        {
            try
            {
                gvModifyProducts.EditIndex = e.NewEditIndex;
                BindProductDetails(); 
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        protected void gvModifyProducts_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            try
            {
                BindProductDetails();
                gvModifyProducts.PageIndex = e.NewPageIndex;
                gvModifyProducts.DataBind();
           }
            catch (Exception ex)
            {
                throw ex;
            }
         }

        protected void gvModifyProducts_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
          gvModifyProducts.EditIndex = -1;
            BindProductDetails();

        }
        protected void gvModifyProducts_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            try
            {
                int intID = int.Parse(gvModifyProducts.Rows[e.RowIndex].Cells[0].Text);
                TextBox txtName = (TextBox)gvModifyProducts.Rows[e.RowIndex].Cells[1].FindControl("txtName");
                TextBox txtDesc = (TextBox)gvModifyProducts.Rows[e.RowIndex].Cells[2].FindControl("txtDesc");
                TextBox txtPrice = (TextBox)gvModifyProducts.Rows[e.RowIndex].Cells[3].FindControl("txtPrice");
                TextBox txtQuant = (TextBox)gvModifyProducts.Rows[e.RowIndex].Cells[4].FindControl("txtQuantity");
//This Code is used to handle Validations like Required field
                if (txtName.Text.Trim() == "")
                {
                    txtName.Focus();

                }
               else if (txtDesc.Text.Trim() == "")
                    txtDesc.Focus();
               else if (txtPrice.Text.Trim()=="")txtPrice.Focus();
                else if (txtQuant.Text.Trim()=="")
               txtQuant.Focus();
                else
                {
              var data =
                    (from c in context.PRODUCTS
                     where c.Product_ID == intID
                     select c).First();

                    data.Product_Name = txtName.Text;
                    data.Product_Desc = txtDesc.Text;
                    data.Product_Price = decimal.Parse(txtPrice.Text);
                    data.Quantity = int.Parse(txtQuant.Text);
                    context.SaveChanges();

                    lblMsg.Visible = true;
                    lblMsg.Text = "Product Updated Successfully!!!";
                    BindProductDetails();
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        protected void gvModifyProducts_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            try
            {
                int intID = int.Parse(gvModifyProducts.Rows[e.RowIndex].Cells[0].Text);
                PRODUCT p = new PRODUCT();
                p = (from c in context.PRODUCTS
                     where c.Product_ID == intID
                     select c).First();
                context.PRODUCTS.DeleteObject(p);
                
                context.SaveChanges();

                lblMsg.Visible = true;
                lblMsg.Text = "Product  deleted Successfully!!!";
                BindProductDetails();

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

CRUD
READ,UPDATE and DELETE

Hope this article helped you, do write your feedback and comments.
Happy Coding ☺ ☺ ☺
You Can DOWNLOAD Code here
Please write your valuable comments if you liked this.☺

Monday 5 August 2013

SQL Joins mcqs Quiz


After Simple Quiz and MCQs on Stored Procedures.Try for this simpel Quiz.

SQL JOINS
1.___________can be specified in the FROM clause only.
Inner joins
Outer joins
Cross joins
None of above


2.The_______________returns a number of rows equal to the product of all rows in all the tables being joined.
Outer join
Inner join
Cartesian product
Self Join


3._________return rows only when there is at least one row from both tables that matches the join condition.
Inner joins
Outer joins
Self Join
Left outer join


4.___________return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions.
Cartesian Product
Inner Join
Self Join
Outer joins


5.To retain the nonmatching information by including nonmatching rows in the results of a join, use a _________.
full outer join
left outer join
right outer join
Any of above


6.________ is simple sql join condition which uses the equal sign as the comparison operator.
SQL Equi Join
SQL Non equi join
Cross Join
None of above


7._________is a sql join condition which makes use of some comparison operator other than the equal sign like >, <, >=, <= .
Cross Join
SQL Equi Join
SQL Non equi join
None of above.


8.A ___________that does not have a WHERE clause produces the Cartesian product of the tables involved in the join.
self join
cross join
outer join
inner join


9.In self join ______ helps the query processor determine whether columns should present data from the right or left version of the table.
where clause
from clause
table alias
All of above


10.Which of the following Joins won’t work in SQL Server but only supports in Oracle.
Cross Join
Equi Join
Self join
Natural joins
http://www.highrankdirectory.com/blogs_diaries/garden_and_home/