Sql server Questions

2 Tables will be there

Namely Employee having columns like Empid,Empname,Salary,Mgrid.

Phone Table having Empid and Phone number.

Based on these some questions like this

1) select all the Employees who does not have phone?

2)Dispaly all managers from table(Manager id is same as Empid)

3)How to know How many tables contain Empno as a column in database?

4)Find duplicate rows in a table or if we have table with one column which 
has many records which are not distinct. How to find out the distinct 
values from that column and number of times it's repeated?

5) How to delete the rows which are duplicate?(Don't remove both duplicate 
records.)

6)How to find the 6th highest salary?

Select MIN(s.salary) FROM 
(SELECT TOP 6 salary FROM Employee ORDER BY salary DESC)s 

How to know How many tables contain Empno as a column in 
database?

SELECT DISTINCT NAME FROM SYSOBJECTS WHERE ID IN (SELECT ID FROM SYSCOLUMNS WHERE NAME = 'EMPNO') 


SELECT COUNT(DISTINCT NAME)COUNT FROM SYSOBJECTS WHERE ID IN (SELECT ID FROM SYSCOLUMNS WHERE NAME = 'DEPOSITNO') AND NAME NOT LIKE 'SYNCOBJ%' 


1)select all the Employees who does not have phone?

SELECT * FROM EMPLOY WHERE EMPID NOT IN(SELECT EMPID FROM PHONE)


--2)Dispaly all managers from table(Manager id is same as Empid)

SELECT * FROM EMPLOY E WHERE E.EMPID=E.MGRID


--3)How to know How many tables contain Empno as a column in database?

SELECT name FROM sys.objects WHERE OBJECT_ID IN (SELECT object_id FROM sys.columns WHERE NAME like 'EMPNO')

--4)Find duplicate rows in a table or 
-- if we have table with one column which has many records which are not distinct. How to find out the distinct values from that column and number of times its repeated


SELECT * FROM (
SELECT *,ROW_NUMBER()OVER(PARTITION BY EMPNAME ORDER BY EMPID ) AS NUMS FROM EMPLOY ) A WHERE NUMS>1 

CREATE TABLE ONE(ID INT)
INSERT INTO ONE VALUES('1'),('2'),('3'),('4'),('5'),('1'),('2'),('3'),('4'),('5'),('1'),('2'),('3'),('4'),('5')
SELECT ID,COUNT(*) FROM ONE group by ID having COUNT(*)>1



--5) How to delete the rows which are duplicate?(Don't remove both duplicate records.)


WITH TA
AS
(
SELECT EMPID,EMPNAME,ROW_NUMBER() OVER(PARTITION BY EMPNAME ORDER BY SAL DESC ) AS Nums 
FROM EMPLOY
)
DELETE FROM TA WHERE Nums>1

WITH AT
AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY PHONENUMBER ORDER BY EMPID DESC)AS PHONENUMS FROM PHONE
)
DELETE FROM AT WHERE PHONENUMS>1

--6)how to Find 6th highest sal ?


SELECT MIN(SAL) FROM EMPLOY WHERE SAL IN(
SELECT DISTINCT TOP(6) SAL FROM EMPLOY ORDER BY SAL DESC
)
SELECT TOP 1 SAL FROM (SELECT DISTINCT TOP 6 SAL FROM EMPLOY ORDER BY SAL DESC) EMPLOY ORDER BY SAL

SELECT * FROM
( SELECT DISTINCT TOP 6 SAL , DENSE_RANK() OVER(ORDER BY SAL DESC ) AS RANKS FROM EMPLOY)
EMPLOY WHERE RANKS=6 


select all the Employees who does not have phone?

SELECT * FROM EMPLOY WHERE EMPID NOT IN(SELECT EMPID FROM PHONE)


select all the Employees who does not have phone?

select * from employees where empid not in (select empid 
from phone)

2)Dispaly all managers from table(Manager id is same as 
Empid)

select * from employees emp where emp.employeeid = emp.mgrid

3)How to know How many tables contain Empno as a column in 
database?

4)Find duplicate rows in a table or if we have table with 
one column which 
has many records which are not distinct. How to find out 
the distinct 
values from that column and number of times it's repeated?

select salary,count(salary) as Repeat from employees group 
by salary having salary > 1

5) How to delete the rows which are duplicate?(Don't remove 
both duplicate 
records.)
WITH [T ORDERED BY ROWID] AS

(SELECT ROW_NUMBER() OVER (ORDER BY product_name ASC) AS 
ROWID, * FROM product where product_name ='Scale')

DELETE FROM [T ORDERED BY ROWID] WHERE ROWID <> 1

6)How to find the 6th highest salary?

SELECT TOP 1 salary

FROM (

SELECT DISTINCT TOP 6 salary

FROM employee

ORDER BY salary DESC) a

ORDER BY salary 



How to know How many tables contain Empno as a column in
database?

select count(c.name) as Tables,c.name as Empno from
test.sys.tables as t inner join test.sys.columns as c on
c.object_id=t.object_id and c.name='Empno' group by c.name
having count (c.name) > 0 

Read More

SQL Server Questions

1. Write a SQL query to find out the most recently hired employee in each table?

table a: EMP_table
emp_id
dept_id
emp_name
emp_hiredate
emp_reportingto

table b: dept_table
dept_id
dept_name

2. Explain the two methods by which SQL can be retrieved?
3. Differentiate between a "where" clause and a "having" clause?
4. Write a SQL statement to read data out of a table?
5. Explain different types of joins?
6. What types of index data structures are used in SQL?
7. What is a functional dependency?
8. Can you give some examples of it?

Read More

SQL DBA questions

1.     What is SQL Server Architecture?

2.     What is page?

3.     What is extent?

4.     What are the different types of extents?

Ans: Uniform extents: consists pages from same object, mixed mode extents: consists from different objects

5.     What is the difference between those two extents?

6.     Which pages are available in extents?

7.     What is fill factor?

8.     How to take backup of  DB when the db is in Log shipping by taking that backup without changing LSN?

Ans: By taking copy only backups

9.     How to change port number for sql server?

Ans: To assign a TCP/IP port number to the SQL Server Database Engine
In SQL Server Configuration Manager, in the console pane, expand SQL Server 2005 Network Configuration, expand Protocols for, and then double-click TCP/IP.

In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear, in the format IP1, IP2, up to IPAll. One of these are for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. Right-click each address, and then click Properties to identify the IP address that you wish to configure.

10.  Is it possible to change port for mirroring after configuring mirroring?

Ans: Yes it is possible, by using above mechanism

11.  What is the major difference between Merge replication and Transactional replication?

Ans: In Merge replication the both publisher and subscriber can work independently

12.  How to resolve conflicts in merge replication?

13.  What is quorum in clustering?

14.  What happened when quorum gone?

15.  Is it possible to start service when quorum is gone?

Ans: -No quorum

16.  How to take backup of 400GB DB with in less time?

Ans: Generally 1 GB take 1 min, If we use stripped backups we can reduce time, If we have 4 processors and 4 drives.

17.  How to find what are the driver’s available in our machine?

Ans: EXEC master.sys.xp_fixeddrives

18.  What is check point?

Ans:Check point is raised when we take backup of database or restarting sql server service

19.  Is it posible to raise chaeckpoint our self?

Ans:Yes

sp_configure 'recovery interval', 32767

go

reconfigure with override

20.  What is the difference between procedure and function?

21.  How to call procedure?

22.  What is the advantage of recompile statement in procedure?

Ans: I have noticed that after inserting many rows in one table many times the stored procedure on that table executes slower or degrades. This happens quite often after BCP or DTS. I prefer to recompile all the stored procedure on the table, which has faced mass insert or update. sp_recompiles marks stored procedures to recompile when they execute next time.

23.  How to know the current connected connections?

24.  How to set maximum connections?

Ans: SP_Configure

25.  How to start sql service without raising checkpoint?

SHUTDOWN WITH NOWAIT

26.  Have you work on cmd prompts?

Ans:

Runà CMDà net start mssqlserver

Runà CMDà net stop mssqlserver

27.  What is transaction?

Ans: IT is nothing but sequence of actions

28.  Can you say syntax of transaction?

Ans: Begin Tran

           Statements

        Commit Tran

 

29.  What is difference between cascade drop of table?

Ans: Cascade option allows the user to delete all the tables which are defined by foreign key relation

 

30.  What happened if we issue drop table command?

Ans: If the tables are involved in foreign key relation, if we try to delete those tables. It will not allow deleting. If we delete under any circumstances we define that as cascade

 

31.  What are the several recovery models?

Ans: Full, simple, Bulk logged recovery model

 

32.  Why log shipping is not supported in simple recovery model?

 

33.  What is the default port for mirroring?

Ans: 5022

 

 

34.  How to change default port for sqlserver?

Ans: Go to sqlserver configuration managerà serviceà protocolsà choose TCP/IPà right clickà Take propertiesà Go to advancedà Change IP for which are not loop backed ip

35.  What is resource governor?

36.  What is heart beating in clustering?

37.  Which cable is used for heart beat, Is it cross cable or plain cable?

 ANS: Cross cable

 

38.  What are the different types of indexes?

39.  What are the limits for cluster and non cluster indexes? ANS: 1- 255

40.  Why we have only one cluster index for table?

41.  What is the importance of statistics?

42.  Scenario: My server running fast upto the yester day, today onwords slows, As a DBA what you have to do?

43.  How to find what are the queries that are running in particular SPID?
sys.dm_exec_sql_text

44.  How to find sql server version?

Ans: select @@version

45.  What is another port number for sqlserver?

Ans: for TCP/IP: 1433, UDP: 1434

46.  I have backup and I want to restore upto particular time only?

Ans: Restore database wowzzy to disk=’’ with stopat=’time stamp’

47.  How to create user, how to assign read and write permissions only for that user?

48.  What is Super admin, who is having total rights in system level? Ans: SA

49.  What is full form of SA? Ans: System Administrator

50.  What are the different locks?

51.  What are different isolation levels?

52.  What is serelizable?

53.  What is exclusive lock?

54.  I have done some schema change (Add column for table) in merge replication for an article, is it applied for subscriber?

55.  Can you list some system tables?

         56.Difference between shrink and truncate?

o    Truncate cannot remove empty space

o    Shrink can remove empty space, so space can recollect

Read More

SQL Server Interview Questions

1.what is use of SQL browser service ?  if Sql browser is down will sql server run ?

2.How many authentication modes in SQL Server ? and whom we give  Windows authentication 
    and to whom we give SQL Authentication ?

3.How to restrict Wintel admins to access SQL Server ? 
   (My client is saying only SQL team should have access no one else especially Wintel/Windows
     Admins  bcz there is a chance of misusing/removing any logins/DB's by wintel admins )

4. How many types of backups? 

5.When we create a DB what are the files will be created ? 

6. can we add more than one log file and mdf file to database ?

7. what is the use of MSDB system database ?

8. what is the differences bw SQL server 2000 and 2005 ?

9. when you upgrade  SQL server from 2000 to 2005 what is the process/ steps you followed ?
  
10. Client raised P1(high priority) incident that one of the application is  running very slowly how    
     will you identify the reason (or) where you see the logs and how to  solve that issue?
  (Here there are no transactions locked on this application ?

11. How/where will you identify the  Tcp/Ip network error for failed backup job ?   

12. when you are applying service packs on Cluster what are the steps/process you followed ?

13. How load balancing works in Clustering when Instance1 is installed in NodeA  and Instance2 is
      installed in NodeB ?

 14.  Do you know how to rebuild indexes ?
15. How Query optimization will works ?
16. Do you know about SQL Express ? and SSIS ? 
17. what is the Incident management and problem management ?

Read More

what is closure in java script?

A closure is an inner function that has access to the outer (enclosing) function's variables—scope chain. The closure has three scope chains: it has access to its own scope (variables defined between its curly brackets), it has access to the outer function's variables, and it has access to the global variables.

Read More

Change Table Schema Name

ALTER SCHEMA dbo TRANSFER trainee.[tablename] 

Read More

Using the Repository Pattern with ASP.NET MVC and Entity Framework

Introduction

Data driven web applications need to have a neat strategy for data access. One of the important aspects of this strategy is the separation between the physical database, queries and other data access logic from the rest of the application. Repository pattern is a popular way to achieve such an isolation. This article discusses the basics of Repository pattern in the context of Entity Framework and ASP.NET MVC. It also illustrates how a repository can be built around your entities.

Overview of the Repository Pattern?

Most of the business applications need to access data residing in one or the other data store. The simplest approach is to write all the data access code in the main application itself. Consider, for example, that you have an ASP.NET MVC controller named CustomerController. The Customer controller class has several action methods that ultimately perform typical CRUD (Create, Read, Update and Delete) operations on the underlying database. Let's further assume that you are using Entity Framework for database access. In this case your application would do something like this:

As you can see, various actions of the Customer controller (not all are shown in the figure) directly instantiate the EF data context and fire queries to retrieve the data. They also INSERT, UPDATE and DELETE data using the data context and DbSet. The EF in turn talks with the underlying SQL Server database. Although the above application works as expected it suffers from the drawback that the database access code (creating data context, writing queries, manipulating data, persisting changes etc.) is embedded directly inside the action methods. This design can cause code duplication and the controller is susceptible to change even for a minute change in the data access logic. For example, if an application is modifying a customer from two controllers, each controller will repeat the same code. And any future modifications also need to be done at two places.

To tackle this shortcoming Repository pattern can be introduced. The following line summarizes the purpose of the Repository pattern:

Mediates between the domain and data mapping layers using a collection-like interface for accessing domain objects.

(Read more about repository pattern here.)

Thus a repository acts like a middleman between the rest of the application and the data access logic. A repository isolates all the data access code from rest of the application. In doing so you are benefited by having a simple point of change in case modifications are necessary. Additionally, testing your controllers becomes easy because the testing framework need not run against the actual database access code. An in-memory or local pseudo-database would do the trick. With a repository introduced, the above figure can be changed to:

With the changed design, the Customer controller won't talk with EF data context directly. Additionally, there won't be queries or any other database operations in the action methods. All these operations are wrapped by the Customer repository. The Customer repository in turn uses EF data context to get the job done. Notice that the Customer repository has methods such as SelectAll(), SelectByID(), Insert(), Update() and Delete(). The Customer controller uses these methods to get its job done. If you see the Customer repository - it is offering an in-memory collection like interface to its consumer (for example, many collection classes expose Add() and Remove() methods and allow you to query them).

Creating Model using Entity Framework

Now that you understand the basics of Repository pattern, let's create a sample application that illustrates what we've discussed so far. Create a new ASP.NET MVC web application based on the empty template. Right click on the Models folder and add an ADO.NET Entity Data Model for the Customers table of the Northwind database. The following figure shows how the Customer entity looks:

Various views of the Customer controller need the Customer entity for their display so it needs to be passed between repository, controller and the views. 

Creating Customer Repository

A repository typically does at least five operations - Selecting all records from a table, selecting a single record based on its primary key, Insert, Update and Delete. This list is, however, not rigid. You may have more or fewer methods in the repository. For the sake of our example let's decide that these five operations are needed from the Customer repository. To enforce that all the repository classes have these five methods we will define an interface - ICustomerRepository - that has these methods and then we will implement this interface in a class. Creating an interface will also help us during testing where we may be required to define an alternate in-memory repository for the sake of simplicity. The ICustomerRepository interface looks like this:

public interface ICustomerRepository
{
  IEnumerable<Customer> SelectAll();
  Customer SelectByID(string id);
  void Insert(Customer obj);
  void Update(Customer obj);
  void Delete(string id);
  void Save();
}

The ICustomerRepository interface has five methods as listed below:

• SelectAll() : This method is intended to return all the Customer entities as an enumerable collection (such as a generic List).
• SelectByID() : This method accepts a string representing a customer ID (CustomerID is a character column in the database) and returns a single Customer entity matching that ID.
• Insert(): This method accepts a Customer object and adds it to the Customers DbSet.
• Update() : This method accepts a Customer object and marks it as a modified Customer in the DbSet.
• Delete() : This method accepts a CustomerID and removes that Customer entity from the Customers DbSet.
• Save() : This method saves the changes to Northwind database.

Next, add CustomerRepository class to the project and implement ICustomerRepository in it. The following code shows the completed CustomerRepository class.

public class CustomerRepository:ICustomerRepository
{
    private NorthwindEntities db = null;
 
    public CustomerRepository()
    {
        this.db = new NorthwindEntities();
    }
 
    public CustomerRepository(NorthwindEntities db)
    {
        this.db = db;
    }
 
    public IEnumerable<Customer> SelectAll()
    {
        return db.Customers.ToList();
    }
 
    public Customer SelectByID(string id)
    {
        return db.Customers.Find(id);
    }
 
    public void Insert(Customer obj)
    {
        db.Customers.Add(obj);
    }
 
    public void Update(Customer obj)
    {
        db.Entry(obj).State = EntityState.Modified;
    }
 
    public void Delete(string id)
    {
        Customer existing = db.Customers.Find(id);
        db.Customers.Remove(existing);
    }
 
    public void Save()
    {
        db.SaveChanges();
    }
}

The CustomerRepository class implements all the five methods discussed above. Notice that it has two constructor definitions - one that takes no parameters and the one that accepts the data context instance. This second version will be useful when you wish to pass the context from outside (such as during testing or while using the Unit of Work pattern). All the method implementations of CustomerRepository are quite straightforward and hence we won't go into detailed discussion of these methods.

Using Customer Repository in a Controller

Now that you have built the Customer repository, let's use it in a controller. So, add a controller class inside the Controllers folder and name it CustomerController. The following code shows how CustomerController looks:

public class CustomerController : Controller
{
    private ICustomerRepository repository = null;

    public CustomerController()
    {
        this.repository = new CustomerRepository();
    }

    public CustomerController(ICustomerRepository repository)
    {
        this.repository = repository;
    }
 
 
    public ActionResult Index()
    {
        List<Customer> model = (List<Customer>)repository.SelectAll();
        return View(model);
    }
 
    public ActionResult New()
    {
        return View();
    }
 
    public ActionResult Insert(Customer obj)
    {
        repository.Insert(obj);
        repository.Save();
        return View();
    }
 
    public ActionResult Edit(string id)
    {
        Customer existing = repository.SelectByID(id);
        return View(existing);
    }
 
    public ActionResult Update(Customer obj)
    {
        repository.Update(obj);
        repository.Save();
        return View();
    }
 
    public ActionResult ConfirmDelete(string id)
    {
        Customer existing = repository.SelectByID(id);
        return View(existing);
    }
 
    public ActionResult Delete(string id)
    {
        repository.Delete(id);
        repository.Save();
        return View();
    }
 
}

The Customer controller has two versions of the constructor and seven action methods. Notice that there is a private variable of type ICustomerRepository at the class level. The parameter less constructor sets this variable to an instance of CustomerRepository. The other version of the constructor accepts an implementation of ICustomerRepository from the external world and sets it to the private variable. This second version is useful during testing where you will supply a mock implementation of Customer repository from the test project.

The seven methods defined by the Customer controller are as follows:

• Index() : Displays Index view and passes a List of Customer entities as its model.
• New() : Displays New view.
• Insert() : New view submits data to this method. It receives the data as a Customer instance and then inserts a customer using the repository.
• Edit() : Displays Edit view. It accepts a CustomerID as an id route parameter and populates the Edit view with the data of the existing Customer.
• Update() : Edit view submits data to this method. It receives the data as a Customer instance and then updates a customer using the repository.
• ConfirmDelete() : Displays ConfirmDelete view.
• Delete() : ConfirmDelete view submits to this action method. The action then deletes the Customer using the repository.

We won't go into the details of the four views mentioned above (Index, New, Edit and ConfirmDelete). They are quite simple and you can add them on your own for the sake of testing.

You just created and successfully used Repository pattern! As you can see from the controller code, nowhere did you use data context or EF operations. You always called some or the other method of the CustomerRepository to get the job done. Thus all your data access code is now separated into the repository.

Testing a Controller

Earlier we mentioned that repository pattern also helps during the testing phase. Let's see how by creating a simple test. Add a new Test project to the same solution and refer the MVC project into it. Inside the test project we will define another repository that works on some in-memory collection instead of the actual database. To create the repository add a class to the test project and write the following code to it:

class TestCustomerRepository:ICustomerRepository
{
    private List<Customer> data = new List<Customer>();
 
    public IEnumerable<Customer> SelectAll()
    {
        return data;
    }
 
    public Customer SelectByID(string id)
    {
        return data.Find(m => m.CustomerID == id);
    }
 
    public void Insert(Customer obj)
    {
        data.Add(obj);
    }
 
    public void Update(Customer obj)
    {
        Customer existing = data.Find(m => m.CustomerID == obj.CustomerID);
        existing = obj;
    }
 
    public void Delete(string id)
    {
        Customer existing = data.Find(m => m.CustomerID == id);
        data.Remove(existing);
    }
 
    public void Save()
    {
        //nothing here
    }
}

As you can see TestCustomerRepository class implements the same ICustomerRepository interface defined in the MVC project. It then implements all five methods against an in-memory List of Customer entities. Although not shown in the above code, you could have pre-populated the List with some mock data.

Once TestCustomerRepository is created you can instantiate it in a test method and pass it to the CustomerController like this:

[TestMethod]
public void TestMethod1()
{
    TestCustomerRepository repository = new TestCustomerRepository();
    CustomerController controller = new CustomerController(repository);
    var result = (ViewResult)controller.Index();
    List<Customer> data = (List<Customer>)result.ViewData.Model;
    Assert.IsFalse(data.Count <= 0);
}

Recollect that CustomerController has an overloaded version of the constructor that takes any implementation of ICustomerRepository from the external world. The above code creates an instance of TestCustomerRepository and passes it to the CustomerController. It then invokes the Index() action method of the controller. The model of the Index view is obtained using the ViewData.Model property. The Assert checks whether there are any items in the model collection using IsFalse() method. In this case since no data is added to the generic List of Customer, the Count will be 0 and hence the condition will evaluate to true causing the assertion to fail.

Making the Repository Generic

Although the above example works great, it has a drawback. It expects you to have a separate repository for each entity in the application. For example, CustomerRepository for Customer entity, EmployeeRepository for Employee entity and so on. This can be too much work, especially if all the repositories are doing the same kind of operations (typical CRUD as in our example). Wouldn't it be nice to create a generic repository that can be used with any entity? Let's attempt to do just that.

Add the following interface to the ASP.NET MVC project:

public interface IGenericRepository<T> where T:class
{
    IEnumerable<T> SelectAll();
    T SelectByID(object id);
    void Insert(T obj);
    void Update(T obj);
    void Delete(object id);
    void Save();
}

The IGenericRepository interface is a generic interface that defines the same set of methods as before. Notice that this time instead of Customer entity it uses T everywhere. Also notice that SelectByID() and Delete() methods now accept object parameter instead of string. This is necessary because different tables may have different types of primary keys (Customers table has a string primary key whereas Employees table has an integer primary key).

Now, add a class to the ASP.NET MVC project that implements IGenericRepository interface. This class is shown below:

public class GenericRepository<T>:IGenericRepository<T> where T : class
{
    private NorthwindEntities db = null;
    private DbSet<T> table = null;
 
    public GenericRepository()
    {
        this.db = new NorthwindEntities();
        table = db.Set<T>();
    }
 
    public GenericRepository(NorthwindEntities db)
    {
        this.db = db;
        table = db.Set<T>();
    }
 
    public IEnumerable<T> SelectAll()
    {
        return table.ToList();
    }
 
    public T SelectByID(object id)
    {
        return table.Find(id);
    }
 
    public void Insert(T obj)
    {
        table.Add(obj);
    }
 
    public void Update(T obj)
    {
        table.Attach(obj);
        db.Entry(obj).State = EntityState.Modified;
    }
 
    public void Delete(object id)
    {
        T existing = table.Find(id);
        table.Remove(existing);
    }
 
    public void Save()
    {
        db.SaveChanges();
    }
}

The GenericRepository is a generic class and implements IGenericRepository. Notice that since this class uses generic type T you can't access a DbSet as a property of data context. That's why a generic DbSet variable is declared at the top that points to an appropriate DbSet based on the type of T.

Once GenericRepository is ready you can use it in the Customer controller like this:

public class CustomerController : Controller
{
    private IGenericRepository<Customer> repository = null;
 
    public CustomerController()
    {
        this.repository = new GenericRepository<Customer>();
    }
 
    public CustomerController(IGenericRepository<Customer> repository)
    {
        this.repository = repository;
    }
    ...
    ...
}

As shown above, the IGenericRepository variable is declared with Customer as its type. The constrictor then assigns an instanced of GenericRepository() or some other implementation of IGenericRepository to this variable. 

Testing a Controller using Generic Repository

Just like you tested the CustomerController by creating a mock implementation of ICustomerRepository, you can also test it by creating a mock implementation of IGenericRepository. The following code shows one such implementation:

class TestGenericRepository<T>:IGenericRepository<T> where T:class
{
    private List<T> data = new List<T>();
 
    public IEnumerable<T> SelectAll()
    {
        return data;
    }
 
    public T SelectByID(object id)
    {
        return data.FirstOrDefault();
    }
 
    public void Insert(T obj)
    {
        data.Add(obj);
    }
 
    public void Update(T obj)
    {
        T existing = data.FirstOrDefault();
        existing = obj;
    }
 
    public void Delete(object id)
    {
        data.RemoveAt(0);
    }
 
    public void Save()
    {
        //nothing here
    }
}

The TestGenericRepository class creates an implementation of IGenericRepository that works against an in-memory List of entities. Notice that just for the sake of testing, SelectByID(), Update() and Delete() methods return the first item from the List.

Now you can write another test method that uses TestGenericRepository repository.

[TestMethod]
public void TestMethod2()
{
    TestGenericRepository<Customer> repository = new TestGenericRepository<Customer>();
    CustomerController controller = new CustomerController(repository);
    var result = (ViewResult)controller.Index();
    List<Customer> data = (List<Customer>)result.ViewData.Model;
    Assert.IsFalse(data.Count <= 0);
}

As you can see this time TestGenericRepository is instantiated for the Customer entity and passed to the CustomerController. The remaining part of the test is identical to the earlier test method you wrote.

Read More