28 May 2016

Types of Stored Procedures in SqlServer

Stored procedure is a precompiled set of one or more SQL statements that is stored on Sql Server. stored Procedures is that they are executed on the server side, to reduce the network traffic.


Types of Stored Procedures




  1. System Defined Stored Procedure


These stored procedures are already defined in Sql Server. These are physically stored in hidden Sql Server. These procedure starts with the sp_ prefix. Hence we don't use this prefix when naming user-defined procedures.  Its manage SQL Server through administrative tasks. These are in master and msdb database. Here is a list of some useful system defined procedure.


































System ProcedureDescription
sp_rename

 
It is used to rename an database object like stored procedure,views,table etc.
sp_changeowner

 
It is used to change the owner of an database object.
sp_helpIt provides details on any database object.
sp_helpdb

 
It provide the details of the databases defined in the Sql Server.
sp_helptext

 
It provides the text of a stored procedure reside in Sql Server
sp_depends

 
It provide the details of all database objects that depends on the specific database object.


  1. Extended Procedure


These are Dynamic-link libraries (DLL's) that are executed outside the SQL Server environment. They are identified by the prefix xp_ Extended procedures provide an interface to external programs for various maintenance activities. These are stored in Master database


Ex. EXEC xp_logininfo 'BUILTIN\Administrators'




  1. User Defined Stored Procedure


These procedures are created by user for own actions and stored in the current database




  1. CLR Stored Procedure


CLR stored procedure are special type of procedure that are based on the CLR (Common Language Runtime) in .net framework. CLR integration of procedure was introduced with SQL Server 2008 and allow for procedure to be coded in one of .NET languages




  1. Temporary Stored procedures


The temporary stored procedures have names prefixed with the # symbol. Temporary stored procedures stored in the tempdb databases. These procedures are automatically dropped when the connection terminates between client and server




  1. Remote Stored Procedures


The remote stored procedures are procedures that are created and stored in databases on remote servers. These remote procedures can be accessed from various servers, provided the users have the appropriate permission


     7. Dynamic Stored Procedures


Sp_executesql can be used to call any statement or batch, not just a stored procedure. Executing SQL statements that are built "on the fly" is referred to asdynamic SQL. You can use either sp_executesql or the EXECUTE command to execute your dynamic SQL statements.



DECLARE @string NVARCHAR(100)  
SELECT @string = 'select * from authors'
EXEC sp_executesql @string --or EXEC(@string)

27 May 2016

Async call

$.ajax({
type: 'POST',
url: "webMethods.asmx/fngetuser",
data: "{'userid':'" + userid + "'}",
contentType: 'application/json; charset=utf-8',
dataType: 'json',
 async: true,
success: function (msg) {
$('[id$="Label1"]').html(msg.d[0]);
$('[id*=ur_]').removeAttr('checked');
},
error: function (msg, a, b) { alert('swe'); }
});


$.getJSON("@Url.Action("GetLat", "Location")", { addr: varaddr },
function (msg) {
if (msg !== null && msg.Lat != 0) { }
else { alert('Invalid address'); }
});

18 May 2016

Variance in Delegates

When you assign a method to a delegate, covariance and contravariance provide flexibility for matching with method signature.


Covariance permits a method to have return type as more derived than that defined in the delegate.


Contravariance permits a method that has parameter types that are less derived than those in the delegate type.


 

26 April 2016

Allow only alphanumeric in textbox using javascript and regular expression

function validate(evt) {
var theEvent = evt || window.event;
var key = theEvent.keyCode || theEvent.which;
key = String.fromCharCode(key);
var regex = /^[0-9a-zA-Z\s\b]+$/;
if (!regex.test(key)) {
theEvent.returnValue = false;
if (theEvent.preventDefault)
theEvent.preventDefault();
}
}

<asp:TextBox ID="txtVal" runat="server" onKeyPress="validate(e);"></asp:TextBox>

var re = /^-?\d+$/;
var matches = re.test(args.Value);

function isNumberKey(evt) {
var charCode = (evt.which) ? evt.which : event.keyCode
if (charCode > 31 && (charCode < 48 || charCode > 57))
return false;

return true;
}

16 March 2016

Extending using Extension methods

static class MyExtensionMethods
{
public static int Negate(this int value)
{
return -value;
}

public static int Multiply(this int value, int multiplier)
{
return value * multiplier;
}
}

static void Main(string[] args)
{
// Passing arguments in extension methods
int i3 = 10;
Console.WriteLine(i3.Multiply(2));
}

14 March 2016

Stored Procedure vs Function














































Stored ProcedureFunction
Return type is not must, Can return zero, single or multiple values or table(s)Return type is must, and it can return one value which is mandatory. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
SP can have input/output parametersSupport only input parameters
Allow Select as well as DML statements.

Allow Select but not DML statements.


Note: In case of multi-table valued functions it can contain DML statements affecting Table Variables.


Stored procedure can execute function.Function cannot execute stored procedure.
Cannot be the part of Select query as a column.Can be the part of select query as a column
Stored Procedures cannot be embedded in the SQL statements like WHERE/HAVING/SELECTFunctions can embedded in the SQL statements like WHERE/HAVING/SELECT
We can use exception handling using Try....Catch block in SP.We can’t use Try....Catch block in UDF
We can use transactions in SPWe can’t user transaction in UDF
Can have up-to 21000 input parametersUDF can have up-to 1023 input parameters

Why we can't execute stored procedure inside a function?

Answer:

  1. Stored Procedure may contain DML statements.

  2. Function can't contain DML statements.
    So executing Function inside stored procedure will never break rule 1.
    But executing stored procedure inside function may break rule no 2.


Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e. no INSERT or UPDATE statements allowed). It returns error like “Invalid use of a side-effecting operator 'INSERT' within a function.” So ultimately strict rule is made by Sql team: we can't execute stored procedures inside function.

05 February 2016

Read XML Data for datasource

using System.Xml.Linq;

<?xml version="1.0" encoding="utf-8" ?>
<StatusTypes>
<StatusType Name ="All" value="0"/>
<StatusType Name ="Over-vote" value="1"/>
<StatusType Name ="Mixed Status" value="2"/>
<StatusType Name ="Not Received" value="3"/>
<StatusType Name ="Over Reported" value="4"/>
<StatusType Name ="Under Reported" value="5"/>
<StatusType Name ="In Balance" value="6"/>
</StatusTypes>

string fullName = HttpContext.Current.Server.MapPath(
"~/Modules/Pxy/StatusDataSource.xml");
XDocument doc = XDocument.Load(fullName);
return (from d in doc.Root.Elements("StatusType")
select new KeyValuePair<string,int>( d.Attribute("Name").Value, int.Parse( d.Attribute("value").Value))
).ToList();

statusDataSource DataSource = new statusDataSource();
if (ddlStatus.Items.Count > 0)
ddlStatus.Items.Clear();

ddlStatus.DataSource = null;
foreach (KeyValuePair<string, int> St in DataSource.Status)
ddlStatus.Items.Add(new ListItem(St.Key, St.Value.ToString()));

07 October 2015

Shadowing in C#


  • In Method override  both methods (base & child class methods) have the same name, same number and same type of parameter in the same order with the same return type. The overridden base method must be virtual, abstract or override

  • but without these we can do using new keyword, this mechanism is called Shadowing

  • In the shadowing or method hiding, the child class has its own version of the function, the same function is also available in the base class.

  • Using this concept we can provide a new implementation for the base class method without overriding it.

  • Showing is used to protect against subsequent base class modification, We can change the access modifier

  • There is no control of a base class on shadowing

  • We can also use shadowing and method overriding together using the virtual and new keywords



public class BaseClass 
{
public string GetMethodOwnerName()
{
return "Base Class";
}
}
public class ChildClass : BaseClass
{
public new virtual string GetMethodOwnerName()
{
return "ChildClass";
}
}
public class SecondChild : ChildClass
{
public override string GetMethodOwnerName()
{
return "Second level Child";
}
}

static void Main(string[] args)
{
ChildClass c = new ChildClass();
Console.WriteLine(c.GetMethodOwnerName());
}


Output: ChildClass

We can't use the new and override keywords together. If you do then the compiler throws a compilation error

Shadowing  Example (using new keyword in child class method)

static void Main(string[] args) 
{
BaseClass c = new ChildClass();
Console.WriteLine(c.GetMethodOwnerName());
}


Output: Base Class

10 September 2015

ref vs out in C#

While writing a code, we often come across situations where we need to return multiple values from a single function/method. But a method can only return a single value.The question is how do we overcome such situation.Answer is simple, use reference types.


Parameters are always passed by value to a method by default.If we want to pass them by reference then we can use either out or ref keyword.


Ref


The ref keyword is used to pass an argument as a reference. This means that when value of that parameter is changed in the method, it gets reflected in the calling method. An argument that is passed using a ref keyword must be initialized in the calling method before it is passed to the called method.


Ref keyword will pass parameter as a reference this means when the value of parameter is changed in called method it get reflected in calling method also. There is no "boxing" when a value type is passed by reference


Out


The out keyword is also used to pass an argument like ref keyword, but the argument can be passed without assigning any value to it. An argument that is passed using an out keyword must be initialized in the called method before it returns back to calling method.


Out keyword also will pass parameter as a reference but here out parameter must be initialized in called method before it return value to calling method



public ActionResult Index()
{
int val1 = 0; //must be initialized for ref
int val2 = 0; //optional for out
Example1(ref val1);
ViewBag.Message += val1 + "\n"; // val1=0
Example2(out val2);
ViewBag.Message += val2 + "\n"; // val1=2
return View();
}

static void Example1(ref int value) //called method
{
//value = 1; // optional
}
static void Example2(out int value) //called method
{
value = 2; //must to assign value
}

These ref and out parameters are useful whenever your method wants to return more than one value.


Both the method definition and the calling method must explicitly use the ref / out keyword


Several inbuilt methods as "TryParse" (one of my favourite) use out and not ref, may be the internal implementation of library mainly uses ref.


Properties/Indexers/Dynamic member access cannot be passed to ref or out parameters since internally they are functions/methods and not members/variables


ref tells the compiler that the object is initialized before entering the function, while out tells the compiler that the object will be initialized inside the function.


Ref and out in method overloading


Both ref and out cannot be used in method overloading simultaneously. However, ref and out are treated differently at run-time but they are treated same at compile time (CLR doesn't differentiates between the two while it created IL for ref and out). Hence methods cannot be overloaded when one method takes a ref parameter and other method takes an out parameter. The following two methods are identical in terms of compilation.



class MyClass
{
   public void Method(out int a) // compiler error “cannot define overloaded”
   {
       // method that differ only on ref and out"
   }
  public void Method(ref int a)
   {
     // method that differ only on ref and out"
  }
}

However, method overloading can be done, if one method takes a ref or out argument and the other method takes simple argument. The following example is perfectly valid to be overloaded.



class MyClass
{
          public void Method(int a)
          {
          }
          public void Method(out int a)
          {
                   // method differ in signature.
          }
}

Ex:
public static void Foo()
{
int val = 0;
Example1(val);
Console.WriteLine(val); // Still 0!
Example2(ref val);
Console.WriteLine(val); // Now 2!
Example3(out val);
Console.WriteLine(val); // Now 3!
}

static void Example1(int value)
{
value = 1;
}
static void Example2(ref int value)
{
value = 2;
}
static void Example3(out int value)
{
value = 3;
}


































RefOut
The parameter or argument must be initialized first before it is passed to ref.It is not compulsory to initialize a parameter or argument before it is passed to an out.
It is not required to assign or initialize the value of a parameter (which is passed by ref) before returning to the calling method.A called method is required to assign or initialize a value of a parameter (which is passed to an out) before returning to the calling method.
Passing a parameter value by Ref is useful when the called method is also needed to modify the pass parameter.Declaring a parameter to an out method is useful when multiple values need to be returned from a function or method.
It is not compulsory to assign a parameter value before leaving calling method.A parameter value must be assigned within the calling method before its use.
When we use REF, data can be passed bi-directionally.When we use OUT data is passed only in a unidirectional way (from the called method to the caller method).
Both ref and out are treated differently at run time and they are treated the same at compile time.
Properties are not variables, therefore it cannot be passed as an out or ref parameter.

The out and ref keywords are useful when we want to return a value in the same variables as are passed as an argument

09 September 2015

.js vs .min.js

They are both the same functionally but the .min has all unnecessary characters (white-spaces and comments stripped out, shorter variable names, ...)  removed in order to make the file size smaller.


Just to point out as well, you are better using the minified version (.min) for your live environment as Google are now checking on page loading times. Having all your JS file minified means they will load faster and will score you more brownie points.

Extension Methods in C#.Net


  • Extension methods are a new feature in C# 3.0

  • You can use extension methods to extend a class/interface, but not to override them.

  • Extension methods are static methods of a static class, and the 1st parameter is preceded by the “this” modifier (refers to class/interface/the called value), 2nd onward parameters act as passing parameters as in normal method calling

  • you need to add the "using System.Linq" directive to the top of the code

  • Extension methods allow existing classes to be extended without relying on inheritance or having to change the class's source code

  • If the class is sealed than there in no concept of extending its functionality

  • This feature is important for all developers, especially if you would like to use the dynamism of the C# enhancements in your class's design

  • An extension method must be defined in a top-level static class

  • An extension method is defined as static method but it is called like as instance method

  • Overuse of extension methods is not a good style of programming


Ex: With Multiple Parameters
public static class MyExtensions
{
static int WordCount(this String str, char[] charArray)
{
return str.Split(charArray).Length;
}
}

And it can be called from an application by using this syntax:
string str = "Hello Extension Methods";
int i = str.WordCount(new char[] { ' ', '.', '?' });

Extension methods at compile time:


An extension method with the same name and signature in an interface or class method (1st priority) will never be called.


Inherit the class and then implement the functionality in an instance method in the derived class.


Use aggregation instead of inheritance.

Aggregation example:

university owns various departments (e.g., chemistry), and each department has a number of professors. If the university closes, the departments will no longer exist, but the professors in those departments will continue to exist. Therefore, a University can be seen as a composition of departments, whereas departments have an aggregation of professors. In addition, a Professor could work in more than one department, but a department could not be part of more than one university.


Ex1: with Interface
public interface IMyInterface
{
        // Any class that implements IMyInterface must define a method 
        // that matches the following signature. 
        void MethodB();
}

public static class Extension
{
    public static void MethodA(this IMyInterface myInterface, int i)
    {
       Console.WriteLine("Extension.MethodA");
    }
}

public class A : IMyInterface
{
      public void MethodB() { Console.WriteLine("A.MethodB()"); }
}

class ExtMethodDemo
{
static void Main(string[] args)
{
A a = new A();
a.MethodA(1); //A contains no MethodA,
//so it calls the extension method that has a matching signature.
}
}

Output:
    Extension.MethodA

Ex2: with class
public class Class1
    {
        public string Display()
        {
            return ("Display");
        }

        public string Print()
        {
            return ("Print");
        }
    }

public static class XX
    {
         public static void NewMethod(this Class1 ob)
        {
            Console.WriteLine("NewMethod");
        }
    }

class Program
    {
        static void Main(string[] args)
        {
            Class1 ob = new Class1();
            ob.Display();
            ob.Print();
            ob.NewMethod();
            Console.ReadKey();
        }
    }
}

Output:
Display
Print
NewMethod

The compiler doesn't cause an error if two extension methods with same name and signature are defined in two different namespaces and these namespaces are included in same class file using directives. Compiler will cause an error if you will try to call one of them extension method.


One of the great reasons for Extension methods is behind LINQ. Without extension methods a lot of what you can do in LINQ would be very hard. The Where(), Contains(), Select extension methods means a lot more functionality is added to existing types without changing their structure


The biggest disadvantage is that there's no compiler error or warning if you have a regular method and an extension method with the same signature in the same context.


Extension methods fit very well into an OOP design: consider the simple method



bool empty = String.IsNullOrEmpty (myString)

in comparison to
bool empty = myString.IsNullOrEmpty ();

Ex3: With Multiple parameters in Extension Method
class Program
{
static void Main(string[] args)
{
List<Product> Products = new List<Product>()
{
new Product{ProductName = "White Shoe", ProductPrice = 10},
new Product{ProductName = "Green Shoe", ProductPrice = 5},
new Product{ProductName = "Black Shoe", ProductPrice = 15}
};

foreach (Product p in Products)
{
Console.WriteLine("{0} costs {1} (VAT: {2})",
p.ProductName, p.ProductPrice, p.CalculateVat());
}
}
}

public static class MyExtensions
{
public static double CalculateVat(this Product p)
{
return (p.ProductPrice * .25);
}
}

public class Product
{
public string ProductName { get; set; }
public double ProductPrice { get; set; }
}

However, you do not own or have control over the Product class since it is owned by someone else. So you can’t add a CalculateVat() method to the product class, And it can be done by using EXTENSION METHODS.

24 August 2015

.xlsx sheet data to datatable in C#

using System.Data.OleDb;

string qry = "Select * from [Sheet1$]";

string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + _xlsheet_filepath + ";Extended Properties=\"Excel 12.0;HDR=YES;\";Persist Security Info=False";
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();

DataTable dt = new DataTable();
OleDbDataAdapter exDA = new OleDbDataAdapter(qry, excelConnection);
exDA.Fill(dt);
excelConnection.Close();

18 August 2015

Search a column in a table

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%_ColumnName%'
ORDER BY schema_name, table_name

14 August 2015

Delegates

It is not compulsory to create delegates. It is just the easiest way in some situations to get the thing done.


If you have multiple methods with same signature (return type & number of parameters) and want to call all the methods with single object then using delegates is the best option.


C# delegates are similar to C++ function pointers and are type safe. A delegate is a reference type variable that holds the reference to a method. The reference can be changed at runtime.


All delegates are implicitly derived from the System.Delegate class. Delegates are especially used for implementing events and the call-back methods (Anonymous). Delegates allow methods to be passed as parameters. Delegate types are sealed and immutable type. Effective use of delegate improves the performance of application.


There are three types of delegates that can be used in C#.

  • Single Delegate

  • Multicast Delegate

  • Generic Delegate


Declaring Delegates


Whenever we want to create delegate methods we need to declare with delegate keyword. Delegate methods signature should match exactly with the methods signature (Parameter list) that Invokes.


For example, consider a delegate:
public delegate int MyDelegate (string s);

The preceding delegate can be used to reference any method that has a single string parameter and returns an int type variable.


Syntax for delegate declaration is:
delegate <return type> <delegate-name> <parameter list>

Singlecast Delegate


Once a delegate type is declared, a delegate object must be created with the new keyword and be associated with a particular method. When creating a delegate, the argument passed to the new expression is written similar to a method call, but without the arguments to the method.


Following example demonstrates declaration, instantiation, and use of a delegate that can be used to reference methods that take an integer parameter and returns an integer value.



using System;

delegate int NumberChanger(int n);
namespace DelegateAppl
{
class TestDelegate
{
static int num = 10;
public static int AddNum(int p)
{
num
+= p;
return num;
}

public static int MultNum(int q)
{
num
*= q;
return num;
}
public static int getNum()
{
return num;
}

static void Main(string[] args)
{
//create delegate instances
NumberChanger nc1 = new NumberChanger(AddNum);
NumberChanger nc2 = new NumberChanger(MultNum);

//calling the methods using the delegate objects
nc1
(25);
Console.WriteLine("Value of Num: {0}", getNum());
nc2
(5);
Console.WriteLine("Value of Num: {0}", getNum());
Console.ReadKey();
}
}
}

When the above code is compiled and executed, it produces the following result:
Value of Num: 35
Value of Num: 175

Multicast Delegate


It is a delegate which holds the reference of more than one method. Delegates can be chained together; for example, multiple methods can be called on a single event


Delegate objects can be composed using the "+" operator. A composed delegate calls the two delegates it was composed from. Only delegates of the same type can be composed. The "-" operator can be used to remove a component delegate from a composed delegate.


Using this property of delegates you can create an invocation list of methods that will be called when a delegate is invoked. This is called multicasting of a delegate. The following program demonstrates multicasting of a delegate:



using System;

delegate int NumberChanger(int n);
namespace DelegateAppl
{
class TestDelegate
{
static int num = 10;
public static int AddNum(int p)
{
num
+= p;
return num;
}
public static int MultNum(int q)
{
num
*= q;
return num;
}
public static int getNum()
{
return num;
}

static void Main(string[] args)
{
NumberChanger nc;
NumberChanger nc1 = new NumberChanger(AddNum);
NumberChanger nc2 = new NumberChanger(MultNum);
nc
= nc1;
nc
+= nc2;

nc
(5);//calling multicast
Console.WriteLine("Value of Num: {0}", getNum());
Console.ReadKey();
}
}
}

When the above code is compiled and executed, it produces the following result:
Value of Num: 75

Using Delegates


The following example demonstrates the use of delegate. The delegate printString can be used to reference method that takes a string as input and returns nothing.


We use this delegate to call two methods, the first prints the string to the console, and the second one prints it to a file:



using System;
using System.IO;

namespace DelegateAppl
{
class PrintString
{
static FileStream fs;
static StreamWriter sw;

public delegate void printString(string s);
public static void WriteToScreen(string str)
{
Console.WriteLine("The String is: {0}", str);
}
public static void WriteToFile(string s)
{
fs
= new FileStream("c:\\msg.txt",FileMode.Append, FileAccess.Write);
sw
= new StreamWriter(fs);
sw
.WriteLine(s);
sw
.Flush();
sw
.Close();
fs
.Close();
}
static void Main(string[] args)
{
printString ps1
= new printString(WriteToScreen);
printString ps2
= new printString(WriteToFile);
ps1("Hello World");
ps2
("Hello World");
Console.ReadKey();
}
}
}

When the above code is compiled and executed, it produces the following result:
The String is: Hello World

Generic Delegate


Generic Delegate was introduced in .NET 3.5 that don't require to define the delegate instance in order to invoke the methods.


There are three types of generic delegates:

  • Func

  • Action

  • Predicate


Func


The Func delegate defines a method that can be called on arguments and returns a result.



Action


The Action delegate defines a method that can be called on arguments that returns void. In the given code example, delegate Action<string> is defined with string as argument.



Action<string> MyAction = y => Console.Write(y);
MyAction("Hello");
Console.ReadKey();

Predicate


The Predicate delegate defines a method that can be called on arguments and always returns Boolean type result.