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;
Unboxingo = 123;
i = (int)o;
// unboxing
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
using System;public class Singleton
{
private static Singleton instance; private Singleton() {} public static Singleton Instance
{
get
{
if (instance == null)
{
instance = new Singleton();
}
return instance;
}
}
}
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.
“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
public class ProductMD {
[StringLength(50),Required]
public object Name { get; set; }
[StringLength(15)]
public object Color { get; set; }
[Range(0, 9999)]
public object Weight { get; set; }
// public object NoSuchProperty { get; set; }
}The following example shows the data class named Product with data annotations applied
to the members.
Reference: http://msdn.microsoft.com/en-us/library/dd901590%28VS.95%29.aspxpublic class Product
{
[Display(Name="Product Number")]
[Range(0, 5000)]
public int ProductID { get; set; } [Display(Name="Name")]
[Required]
public string ProductName { get; set; } [Display(Name="Price")]
[DataType(DataType.Currency)]
public double ListPrice { get; set; } [EnumDataType(typeof(ProductColor))]
public ProductColor Color { get; set; } [Display(Name="Available")]
public bool InStock { get; set; } public Product() { } public Product(int _productID, string _productName,
double _listPrice, ProductColor _color, bool _inStock)
{
this.ProductID = _productID;
this.ProductName = _productName;
this.ListPrice = _listPrice;
this.Color = _color;
this.InStock = _inStock;
}
}public enum ProductColor
{
Red, White, Purple, Blue
}
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();
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open(); // Start a local transaction.
SqlTransaction sqlTran = connection.BeginTransaction(); // Enlist a command in the current transaction.
SqlCommand command = connection.CreateCommand();
command.Transaction = sqlTran; try
{
// Execute two separate commands.
command.CommandText =
"INSERT INTO Production.ScrapReason(Name) VALUES('Wrong size')";
command.ExecuteNonQuery();
command.CommandText =
"INSERT INTO Production.ScrapReason(Name) VALUES('Wrong color')";
command.ExecuteNonQuery(); // Commit the transaction.
sqlTran.Commit();
Console.WriteLine("Both records were written to database.");
}
catch (Exception ex)
{
// Handle the exception if the transaction fails to commit.
Console.WriteLine(ex.Message); try
{
// Attempt to roll back the transaction.
sqlTran.Rollback();
}
catch (Exception exRollback)
{
// Throws an InvalidOperationException if the connection
// is closed or the transaction has already been rolled
// back on the server.
Console.WriteLine(exRollback.Message);
}
}
}Reference for Code:
http://msdn.microsoft.com/en-us/library/2k2hy99x%28v=vs.110%29.aspx
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:
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
private int myWidgetID;
private Widget myWidget = null;
public Widget MyWidget
{
get
{
if (myWidget == null)
{
myWidget = Widget.Load(myWidgetID);
}
return myWidget;
}
}private int myWidgetID;
private Widget myWidget = null;
public Widget MyWidget
{
get { return myWidget = myWidget ?? Widget.Load(myWidgetID); }
}class SnookerPlayer
{
/**
* @var SnookerCue
*/
protected $_snookerCue;
/**
* Lazy-load snooker cue if one is not assigned to the player
* @return SnookerCue
*/
public function getSnookerCue()
{
if (is_null($this->_snookerCue)) {
$this->_snookerCue = new SnookerCue();
}
return $this->_snookerCue;
}
}
