Some .Net/MSSQL Server Questions (and Answers)

Some .Net/MSSQL Server Questions and answers.

1. What are some differences between Abstract Classes and Interfaces. When will you use them and why?

2. What is Boxing?

3. What is Singleton?

4. What are Action Filters in MVC?

5. What are the differences between Response.Redirect, Response.PermanentRedirct?

6. Why do you need Command objects in .Net/C#? What are command objects?

7. What are Data Annotations?

8. What are POCO classes in relation to Entity Frameworks?

9. What are advantages/differences of LINQ over SQL and Entity Frameworks?

10. How do you write Transactions in .Net?

11. Assume you have an Employee table. Write an SQL to find out the even employees.

12. How will you debug/Find errors for SQL queries in MS SQL Server?

13. What is Lazy Loading?

14. What is NOLOCK

Answers (Just will give short answers)

1. What are some differences between Abstract Classes and Interfaces. When will you use them and why?

We had a short-note on that:

http://sitestree.com/strategy-pattern-interface-vs-abstract-classes/

From http://www.javaworld.com/article/2077421/learn-java/abstract-classes-vs-interfaces.html

“If you need to change your design, make it an interface. However, you may have abstract classes that provide some default behavior. Abstract classes are excellent candidates inside of application frameworks.” I answered something similar

2. What is Boxing?

Our article where it is defined and/or referenced with resource

http://sitestree.com/net-knowledge/

“Boxing is the process of converting a value type to the type object or to any interface type implemented by this value type. “

Boxing

int i = 123;

// The following line boxes i.

object o = i;

3. What is Singleton?

Only one instance of the Object in the Application such as Logger.

Implementing Singleton in .Net. Reference: http://msdn.microsoft.com/en-us/library/ff650316.aspx

4. What are Action Filters in MVC?

You use filters in C# MVC as you use in other languages such as in PHP Laravel or also in Java EE frameworks. You use xml configurations sometimes in Java EE to bring a Filter in Action. You mention a filter as an interruption/per-requisite to one or more or the whole set of operations. Then the filter operation will be executed before those operations.

From: http://www.asp.net/mvc/tutorials/older-versions/controllers-and-routing/understanding-action-filters-cs

“An action filter is an attribute that you can apply to a controller action — or an entire controller — that modifies the way in which the action is executed. The ASP.NET MVC framework includes several action filters:

  • OutputCache — This action filter caches the output of a controller action for a specified amount of time.
  • HandleError — This action filter handles errors raised when a controller action executes.
  • Authorize — This action filter enables you to restrict access to a particular user or role.”

5. What are the differences between Response.Redirect and Response.RedirctPermanent?

301 response (RedirectPermanent) just like .htaccess Permanent Redirect. In some cases good for SEO.

You can check a discussion: https://stackoverflow.com/questions/16537955/response-redirect-vs-response-redirectpermanent

Response.Redirect() returns 302

Response.RedirectPermanent() returns 301. browser will not ask for the same resource from old URL anymore.

You can check: http://weblogs.asp.net/gunnarpeipman/asp-net-4-0-seo-features-response-permanentredirect

6. Why do you need Command objects in .Net/C#? What are command objects?

Can be used to execute queries or stored procedures in C#. First, you create connection object, then create a command object, associate this command object with the connection object, then you can execute a query using this command object. You can mention/assign the query text to a property (commandText) of that command object as well.

Check the details at:

“Defines a specific command that you intend to execute against a data source.”

http://msdn.microsoft.com/en-us/library/windows/desktop/ms677502%28v=vs.85%29.aspx

7. What are Data Annotations?

Declarative Programming, can be used to define data attributes and later to validate data.

Example:How to: Validate Model Data Using DataAnnotations Attributes: Reference: http://msdn.microsoft.com/en-us/library/ee256141%28VS.100%29.aspx

8. What are POCO classes in Relation to Entity Frameworks?

Plain Old CLR Object or POCO is a play on the term POJO, from the Java EE programming world (which was coined by Martin Fowler in 2000 [1]), and is used by developers targeting the Common Language Runtime of the .NET Framework. Simply put, a POCO object does not have any dependency on an external framework. It is PLAIN.

Reference: https://en.wikipedia.org/wiki/Poco_%28disambiguation%29

Working with POCO Entities

“The Entity Framework enables you to use custom data classes together with your data model without making any modifications to the data classes themselves. This means that you can use “plain-old” CLR objects (POCO), such as existing domain objects, with your data model. These POCO data classes (also known as persistence-ignorant objects), which are mapped to entities that are defined in a data model, support most of the same query, insert, update, and delete behaviors as entity types that are generated by the Entity Data Model tools.”

Reference:Working with POCO Entities:

http://msdn.microsoft.com/en-ca/library/vstudio/dd456853%28v=vs.100%29.aspx

9. What are advantages/differences of LINQ to SQL and Entity Frameworks?

LINQ to SQL: Works only with MS SQL Server. LINQ to SQL has very limited mapping capabilities and L2S classes must be one-to-one with the database with some exceptions.

Entity Framework: Supports all other major databases including MS SQL Server. Entity Framework supports complex mapping and inheritance structure.

I found that “Entity Framework is Microsoft’s officially recommended .NET data access solution

Check http://blogs.msdn.com/b/dsimmons/archive/2008/05/17/why-use-the-entity-framework.aspx for the detail explanations on similarities and differences.

Check: http://parassanghani.blogspot.ca/2011/01/entity-framework-vs-linq-to-sql.html as well

10. How do you write Transactions in .Net?

As usual

Begin Transaction: SqlTransaction sqlTran = connection.BeginTransaction();

Commit Transaction/End Transaction: sqlTran.Commit();

if Issues/errors, RollBack: sqlTran.Rollback();

11. Assume you have an Employee table. Write an SQL to find out the even employees.

12. How will you debug/Find errors for SQL queries in MS SQL Server?

You can use Transact SQL debugger to debug SQL code the way you debug C# code in visual studio. Check the details on how to debug using Transact SQL debugger at: http://msdn.microsoft.com/en-us/library/cc645997.aspx , Configuring Transact SQL debugger: http://msdn.microsoft.com/en-us/library/cc646024.aspx

You can use SQL Server profiler to analyze, understand, and find queries/problem queries. It also allows you to step through problem queries, find and diagnose problem queries, capture the series of SQL that lead to problem, monitor the performance of SQL Server. Check the details at: http://msdn.microsoft.com/en-ca/library/ms181091.aspx . Note SQL Server profiler will be deprecated; so use extended events instead. Details on extended events: http://www.sqlskills.com/blogs/jonathan/category/extended-events/

Reference for Extended Events: https://stackoverflow.com/questions/16108586/sql-server-profiler-deprecation-replacement

Use the SQL Management Studio’s integrated Debugger as mentioned at http://www.diaryofaninja.com/blog/2010/11/23/debugging-sql-queries-function-amp-stored-procedures-with-sql-management-studio

Transact SQL Debugger:

http://blogs.msdn.com/b/billramo/archive/2009/04/11/transact-sql-debugger-for-sql-server-2008-part-1.aspx

13. What is Lazy Loading?

“Lazy loading is a design pattern commonly used in computer programming to defer initialization of an object until the point at which it is needed” Reference: https://en.wikipedia.org/wiki/Lazy_loading

You can get good definitions here

https://stackoverflow.com/questions/1299374/what-is-eager-loading

Related:

Lazy Inheritance

https://en.wikipedia.org/wiki/Lazy_inheritance

Lazy Evaluation

https://en.wikipedia.org/wiki/Lazy_evaluation

Lazy Initialization:

https://en.wikipedia.org/wiki/Lazy_initialization

Virtual proxy

Virtual Proxy is an object with the same interface as the real object. The first time one of its methods is called it loads the real object and then delegates. Reference: https://en.wikipedia.org/wiki/Lazy_loading

Ghost

A “ghost” is the object that is to be loaded in a partial state. It may only contain the object’s identifier, but it loads its own data the first time one of its properties is accessed. Reference: https://en.wikipedia.org/wiki/Lazy_loading

Value holder

A value holder is a generic object that handles the lazy loading behavior, and appears in place of the object’s data fields: Reference: https://en.wikipedia.org/wiki/Lazy_loading

15. What is NOLOCK

Do not put any lock on anything. Will not block other operations in anyway.

Is used as part of SQL Server Hints sometimes.

“This table hint, also known as READUNCOMMITTED, is applicable to SELECT statements only. NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.”

Reference and Details:

http://www.techrepublic.com/article/using-nolock-and-readpast-table-hints-in-sql-server/

http://msdn.microsoft.com/en-ca/library/ms187373.aspx

I had a short-note on SQL Server hints from long back at http://www.SaLearningSchool.com:

Appendix:

Lazy Loading Example. Reference: https://en.wikipedia.org/wiki/Lazy_loading

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Justetc Social Services (non-profit)

All proceeds from Medium will go to Justetc Social Services ( non-profit). Justetc Social Services provides services in the Training and Education Areas.