Tuesday, May 22, 2012

Stored Procedure vs Inline Query, SQL


A very basic post but still worth mentioning. Here are the major differences between the two:

1. Stored procedures are stored in a pre-complied form.That is once a Stored procedure is executed, the compiled code is used in subsequent calls. This is not possible with inline queries.

2. Stored procedures reduces network traffic.
Since Stored procedures are stored in the server, only the name of Stored procedure is required to pass to the server. But in the case of inline queries, the complete query has to be passed to the server. So inline queries will increase network traffic when the queries are very large.

3. Stored procedures support Deferred Name Resolution.That is we can create stored procedures for objects(eg:- tables) which are not yet created (and will be creating in the near future)

4. Stored procedures prevents SQL Injection Errors.

5. By using Stored procedures we can separate all the queries from the Business logic code. Therefore we can create a separate layer. But while writing inline queries, all the queries have to be written (mixed up) with the business logic code which in turns causes issues while debugging.

6.Developers and database designers can work simultaneously using stored procedures. While a programmer writes business logic, another one can create stored procedures at the same time. :)

Difference between Truncate and Delete statement, SQL

In SQL server there are 2 main keywords for deleting data - Truncate and Delete. Although the end result might be same but both work very differently. We should take into consideration the advantages, limitation and the consequences when using one of them.

Before moving to the details lets first understand the basic SQL statement types:

  1. DML - Data Manipulation Language: It is used to retrieve, store, modify, delete, insert and update data in database. Examples: SELECT, UPDATE, INSERT statements.
  2. DDL - Data Definition Language: It is used to create and modify the structure of database objects in database. Examples: CREATE, ALTER, DROP statements.
  3. DCL - Data Control Language: It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it. Examples: GRANT, REVOKE statements.
  4. TCL - Transactional Control Language: It is used to manage different transactions occurring within a database. Examples: COMMIT, ROLLBACK statments.

Difference - TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is release back to the server. However, DELETE is a DML command and can be rolled back.

When we use this command, SQL server deletes one row at a time. Each row is logged in the transaction LOG. This also means that the server will also maintain the Log Sequence number. This will consume more database resources and the process will  be slow. But this also gives an advantage. The transaction can be rolled back as there are transaction log.

Also you can use the where clause with the delete command but not with the truncate command. With truncate command it's all records or nothing.

Also one more advantage of the truncate command is that it also resets the identity seed of the table. Also the fact that deallocated pages is returned to the system for use in other areas.

Truncate statements cannot be used on the tables involved in log shipping or replication. This is because they both depend on the transaction log to keep the database consistent.

Truncate table cannot be used with table having foreign key references. As the truncate command do not fire any triggers. If you want to use the truncate command with a foreign key, you need to first drop the index and then add it again after using the truncate table command.

Friday, May 18, 2012

Start debugging F5 does not build automatically, Visual Studio

I was facing this issue from a long time and every time I overlook this issue by manually building required projects. I also posted the issue on stack overflow community but did not get any successful answer (Stackoverflow link).

Issue: Many times it happen that while working on application development, you use edit and continue option i.e. you write code while application is in debugging mode (like you hit breakpoint and start writing code from there on). I'm assuming that you've enabled Edit and Continue option from Option -> Debugging -> Edit and Continue dialog box. Now when you stop application and try to run it again using F5 the edited file is no longer compiled automatically and your changes are not reflected. So generally in this scenario we need to manually build the project having edited file from the solution explorer. But this will be a huge bottleneck to your performance if you need to make changes frequently in debugging mode.

Solution: If you're like me i.e. in case visual settings are going out of your bound then simply reset all settings to default one then you must have tried devenv/resetsettings command in order to fix this problem. But this does not solve our problem actual fix is :
1. Open the "Configuration Manager" from the Debug/Release dropdown and verify that Build checkbox is checked for all of the projects you want to build.
2.  Open Visual Studio settings from Debug -> Option & Settings. Then Project and Solutions -> Build and Run . Now in drop-down for "On Run, where projects are out of date:" Select 'Always Build' instead of 'Never Build'.

Hope this will fix your problem. It worked in my case.
   

Wednesday, May 16, 2012

Is it difficult to switch technology from .NET to Java?

I woke up in the midnight and started to think about how much difficult it is to switch from .NET technologies to Java? I think basics would be same in both technologies so there should not be much hurdles. After all both are based on the concepts of Object-oriented programming. Still I request to my readers for their inputs on this topic. I will update any information that I found.

22 April '12 4:00 AM: I don't know why this question wont let me sleep comfortably. Last time when I was thinking about this resulted into insomniac and today also. There are plenty of things running in parallel related to this. The major factor which is invoking me to shift to Java is the fact that it is being used in almost all major application which are quite large in terms of scalability, responsibility and involving novel cross-platform dependencies. As much I've read, in Java you need to have a better framework and libraries understanding in order to get the stuff done also the big plus factor that it is an open-source language so you have very much bright chances to learn the stuff happening inside assemblies. Also one more factor, all big software giants use Java for their application development. I want to have a hands on such applications after all I love programming so why not go for real challenging tasks :).
But at the same time, I think I've around 3 years professional experience on .NET technologies and have learnt a lot from basics to advanced concepts. In fact gave training sessions to juniors too. So why not carry on learning advanced concepts in .NET itself instead of spending time in learning the basics of Java and then intermediate level learning? I believe being good in one is better than OK in two. Now here comes the confusion, both seems good to me. Also one more thing which I believe is get comfortable with uncomfortable. So again pushing me to learn Java. It would be difficult to switch technologies on the professional level as there will be great expectations from a 3 years experienced profession. But that's the challenge and I should happily accept it.

Stopping it here only as this is not something which can be decided in 5-10 mins. Perhaps I'll start learning Java from the advanced topics on the side track. 

Friday, May 11, 2012

Code references completed its 1K views (:

I would like to share a good news :) . Code references has completed its 1000 views today and am glad to know that this happen in a very short time-span. Started from 0 views/week, code references has 15-30 views/day traffic these days. Started from sharing the information that I collect from various website to explaining the tweaks with my own code, I will try to more and more new stuff on this blog in order to make code-references more better. Thanks to all readers, for your suggestions and regular views which motivated me to write more.Hope you perceive more knowledge in upcoming posts.

As I'm a newbie in blogging as well as in programming I used to share knowledge already present over the internet but collating all useful information is also necessary. So I'll try my best to share all new things that come across my learning pathway.

Update 8th September 2012: Code references has now crossed 3000 page views. Cheers


Happy Reading - Rohit Kandhal

Extension Methods ( IfNotNull methods), C#

As per MSDN, Extension methods enables you to "add" methods to existing types without creating a new derived type. They are a special kind of static method, but they are called as if they were instance methods e.g. <objectName>.<extensionMethod>( ).
Usage: Many times it happens that you want to check an object whether it is null or not before calling any of its instance method. e.g. if(parent != null) { parent.XYZ ( ); }. Now it would be better if you can simply write parent.IfNotNull ( x => x.XYZ ( ); );. Here IfNotNull is an extension method. Similarly you may wish to have a function which automatically converts first letter of a string to Uppercase, something like value.UppercaseFirstLetter( );

Implementation: Extension methods are static methods and generally you should place all static methods of your application in a separate class such as ExtensionMethods.cs. ExtensionMethod class should be a static class and all methods in this class should be public static methods. You can invoke these methods from any class in the same way as you can call instance methods. The extension method is called like an instance method, but is actually a static method. In fact the instance pointer 'this' is received as a parameter. You must specify the 'this' keyword before the appropriate parameter you want the method to be called upon.
Following code is the demonstation of InNotNull extension method.

public static class ExtensionMethods
{
      // Extension Method 1: IfNotNull
      public static U IfNotNull < T, U > (this T t, Func <T, U> function)
      {
            return t != null ? function (t) : default (U);
       }

       // Extension Method 2: UppercaseFirstLetter
       public static string UppercaseFirstLetter (this string value)
       {
             if(value.Length > 0)
             {
                   char[ ] array = value.ToCharArray( );
                   array [0] = char.ToUpper ( array [0] );
                   return new string (array);
              }
        }
}

1. IfNotNull extension method: Since this is a generic extension method i.e. it is on T, where T has no constraints, it is always available to any class or struct instance, much like Equals and GetHashCode. You'll notice that the IfNotNull method is only an obscure way to call the specified delegate on the instance, except that we check to see whether the instance is null, and don't call the delegate in that case, returning null.

2. UppercaseFirstLetter extension method: This method first checks if the string length is greater than 0 then converts first letter of that string to upper case.

Using Extension Methods:
1. Suppose you've a Child with Parent property and you want to get Name of its parent.
string parentName = child.Parent.Name;
In case either Child or Parent Name is null then you'd like the name variable to be null. You can achieve this as:
if (child != null)
{
        if(child.Parent != null)
        {
               parentName = child.Parent.Name;
         }
}

Now using the extension method you can simplify the above code as:
parentName = child.IfNotNull ( x => x.Parent ).IfNotNull ( x => x.Name);


Note that we're using lambda expression in the IfNotNull extension method making it least efficient so only use  it when you think that increased code clarity outweighs the performance loss.

2. Similar to above usage, we can use UppercaseFirstLetter method as:
string value = "myFirstLetterIsUpperCase";
value = value.UppercaseFirstLetter ( );

More Parameters: Extension methods can have many arguments other than the 'this' parameter that is required. You can even use variables 'params' arguments with extension methods, as with any method. Because extension methods are actually static methods, there is no significant performance difference between them and other methods.

Extension methods affect the syntactic representation of the source. They do not make the execution of the source code substantially different.

Sources: MSDN, Stackoverflow, DotNetPerls, Code logos, Brad Wilson.