Web service with DB interaction (Entity framework 6), Part 1

Lot of people have problems with database interaction with Web services. connection problems with databases , retrieving, inserting etc. In this post I will show you hoe to connect DB and manage connections efficiently with Web service.

To archive this goal entity frame work is very helpful tool.


Lets see how to use it.

Here is my SQL server table that im going to use. Insted of SQL server You can use any Database type such as Oracle, Mysql etc.

Lets Code . :)

1. Create new WCF application in Visual Studio

2. R-Click on Solution and add -> new Item -> ADO.NET Entity data Model

 

 3. then select EF designer from database

 4. Connect your database with New Connection button and select relevant schema that you going to use

5. Then -> NEXT and select Entity framework version.. here im using 6.0 then click NEXT again

 6. Select relevant Tables , Views or stored procedures you need to use and click FINISH
 

 7. Then you can see that it automatically added classes and additional libraries that need to use the database . and also it will have separate classes for each Tables, Stored procedures and Views that you selected in the initialization space. Check  those classes in your project

8. Then create separate function for our database select and add it to interface([OperationContract]). In here I need to output all the data in the Contact table. Entity Framework creates relevant mapped class for my table. I just need to do is use that class as my return type. Like this , My function will return all the contacts because of that I'm using list of that contact objects

IEFDemo.cs:

 public interface IEFDemo
    {
        [OperationContract]
        string DoWork();


        [OperationContract]
        List<Contact> myContacts();
    }

EFDemo.svc.cs:

 public List<Contact> myContacts()
        {

        }

9. Then we need to create instance of database connection and initialize the instance. than open the connection using that instance and get the data from database then close the connection. We are done
Here is full code.

 public class EFDemo : IEFDemo
    {
        //instance of database entities 
        private demoEntities DB;
        public string DoWork()
        {
            return "hello";
        }

        public List<Contact> myContacts()
        {
            DB = new demoEntities();

            //database connection open and close
            DB.Database.Connection.Open();

            //add all items in DB to items variable
            var items = DB.Contacts;
            DB.Database.Connection.Close();

            return items.ToList();
        }
    }

Run it and test ...

Here is my results,

Enjoy.







Full Code In Media Fire 
http://bit.ly/1qDPrBF