Code First: Queries, Stored Procedures

Yes, till now we created 2 POCO Classes(Employee,Departments) then we seen how to perform Crud Operations.

but now we will see How to Enable 

Using queries:

POCO Class is  Employee then but which is saved in database as Employees---> s is added to every  POCO class in Code First model

like : Employee--Employees, Dept---Depts....ect

DbSqlQuery<Employee> employees = context.emps.SqlQuery("SELECT * FROM dbo.Employees");//.ToList();

                foreach (Employee item in employees)

                {

                    Console.WriteLine(item.EPhone);

                   

                }

Using StoredProcedure:

ALTER PROCEDURE [dbo].[stp_InsertEmp]

(

      @Name as VARCHAR(50),

    @Phone as Varchar(50),

    @Eaddress as int

) AS

 begin

INSERT INTO dbo.Employees(EName,EPhone,Eaddress)VALUES(@Name,@Phone,@Eaddress)

end

                               ===============================

    public class Conn : DbContext

    {

            public DbSet<Employee> emps { get; set; }

            public DbSet<Departments>  dept{ get; set; }

         public void AddEmp(Employee emp)

        {

            SqlParameter[] pars=new SqlParameter[3];

            pars[0]=new SqlParameter("@Name",emp.Ename);

            pars[1]=new SqlParameter("@Phone", emp.EPhone);

            pars[2]=new SqlParameter("@Eaddress",emp.Eaddress);

        base.Database.ExecuteSqlCommand("exec stp_InsertEmp @Name,@Phone,@Eaddress", pars);

       

        }

 }

  Conn context = new Conn();
{

           

Employee eobj = new Employee { Dept = new Departments { DName = "Dept2" }, Ename = "James", EPhone = "7744", Eaddress = 1 };

         

            context.AddEmp(eobj);

            Console.WriteLine("Saved Succesully ...storedprocedure");

           

}

ALTER procedure [dbo].[SelectEmp]

@eids int

as

begin

select * from dbo.Employees where EId=@eids

end

using (Conn  context= new Conn())

 {

            

 IList<Employee> emplist;

              

      sqlParameter categoryParam = new SqlParameter("@eids",7);

emplist = context.Database.SqlQuery<Employee>("exec SelectEmp @eids", categoryParam).ToList();

                    foreach (Employee item in emplist)

                    {                       

                        Console.WriteLine(item.Ename);

                        Console.WriteLine(item.Eaddress);

                    }

                    Console.ReadKey();

                }

Ex :


 DbSet<Employee> t = context.emps;

                Employee e= t.Find(1);

immediate window:


e.EmpDept
1
e.Ename
"raju"
e.Dept.DName
"Accounts"

            
Ex:


Employee t = context.emps.Find(4);--------single record along with dept details

t

    Dept: {EFCodeFirstDemo.Departments}

    Eid: 4

    EmpDept: 2

    Ename: "Dinesh"

    EPhone: "7174"

t.Dept.DName

"Business"

How to Creating in-memory object(Employee --> t)  ?

using create() method

            Conn context = new Conn();

        

            Employee t = context.emps.Create();

            t.Ename = Console.ReadLine();

            t.EPhone = Console.ReadLine();

            t.EmpDept = Convert.ToInt32(Console.ReadLine());          

            context.emps.Add(t);

            context.SaveChanges();