RSS

.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();

 
Leave a comment

Posted by on August 24, 2015 in .NET

 

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

 
Leave a comment

Posted by on August 18, 2015 in SQL SERVER

 

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 stringparameter 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. Multicast delegates must contain only methods that return void, else there is a run-time exception. 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)
      {
         //create delegate instances
         NumberChanger nc;
         NumberChanger nc1 = new NumberChanger(AddNum);
         NumberChanger nc2 = new NumberChanger(MultNum);
         nc = nc1;
         nc += nc2;
         
         //calling multicast
         nc(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: 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;
      
      // delegate declaration
      public delegate void printString(string s);

      // this method prints to the console
      public static void WriteToScreen(string str)
      {
         Console.WriteLine("The String is: {0}", str);
      }
      
      //this method prints to a file
      public static void WriteToFile(string s)
      {
         fs = new FileStream("c:\\message.txt",
         FileMode.Append, FileAccess.Write);
         sw = new StreamWriter(fs);
         sw.WriteLine(s);
         sw.Flush();
         sw.Close();
         fs.Close();
      }
      
      // this method takes the delegate as parameter and uses it to
      // call the methods as required
      public static void sendString(printString ps)
      {
         ps("Hello World");
      }
      static void Main(string[] args)
      {
         printString ps1 = new printString(WriteToScreen);
         printString ps2 = new printString(WriteToFile);
         sendString(ps1);
         sendString(ps2);
         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.

 
Leave a comment

Posted by on August 14, 2015 in .NET

 

The Global Assembly Cache (GAC)

  • Global – Applies to the entire machine
  • Assembly – code-libraries (DLLs)
  • Cache – Place to store things for common access
  1. GAC is a common place to store code libraries (assemblies), so they’re accessible to all applications running on the machine.
  2. C:\Windows\assembly for .NET 2.0 ~ 3.5, C:\WINDOWS\Micorosoft.NET\assembly (for .NET 4.0)
  3. Normal Assemblies can be shared among multiple applications on the machine by registering them in global Assembly cache (GAC).
  4. A DLL is identified by 5 parts:

Name, Version, Architecture, Culture, Public Key.

Although the first 3 are generally the big ones.

  1. To install an assembly into GAC (as Administrator)

1) Drag and Drop

2) Use GacUtil.exe with Visual Studio Command Prompt

gacutil -i [Path][Assembly Name].dll

Note: To install an assembly into the GAC, the assembly must be strongly named. Otherwise you get an error like this: Failure adding assembly to the cache: Attempt to install an assembly without a strong name.

  1. To uninstall an assembly from GAC (as Administrator)

gacutil -u [Assembly Name], Version=1.0.0.0, PublickeyToken=7896a3567gh

Note: has no extention, .dll. Version and PublickeyToken can be omitted and be checked in GAC assembly.

  1. The only requirement for an assembly to be deployed in GAC is that it should have strong name. The CLR (Common Language Runtime) refers an assembly based on specific version mentioned by the calling application/method.
  2. The two tools related to GAC are GAC Tool (gacutil.exe) and Assembly Cache Viewer (shfusion.dll). GAC Tool is used to check for assembly existence, register a shared assembly, view and manipulate contents of GAC, etc. Being a system folder, it requires administrator privileges to be used. Assembly Cache Viewer is used to display the details (version, culture, etc.) associated with the assemblies contained in the cache.
  3. GAC provides the benefits of code reuse, file security (due to its installation in ‘systemroot’ directory and hence deletion is only by users with Administrator privileges), side-by-side execution (allowing multiple versions of an assembly maintained in the same folder)
  4. Unlike in COM, there is no need for the assembly in GAC to be registered before its use. Each assembly is accessed globally without any conflict by identifying its name, version, architecture, culture and public key.
 
Leave a comment

Posted by on August 12, 2015 in .NET

 

Struct Vs Class in C#

  1. Structs may seem similar to classes
  2. Members of a class are private by default and members of struct are public by default.
  3. When deriving a struct from a class/struct, default access-specifier for a base class/struct is public. And when deriving a class, default access specifier is private.
  4. Structures can’t have modifiers like abstract, sealed, and static whereas classes can have.
  5. Both structure and class can have partial modifiers.
  6. A structure couldn’t have a destructor but in class it is possible.
  7. Classes can be inherited whereas structures not. By default structures are sealed, that is the reason structures are not supporting inheritance.
  8. You will always be dealing with instance of a class. But you can’t deal with instance of a struct (but dealing directly with them).
  9. Field/Member can’t be instantiated within structures but classes allow as in the following:
    struct myStructure

    {

    string x = 2;//Not allowed

    }

    class myClass

    {

    string x = 2;//Allowed

    }

  10. Process of converting structure type into object type is called boxing and process of converting object type into structure type is called unboxing.
    Ex: int a=10;
    Object ob = (object) a; //Boxing
    a= (int) obj; //Unboxing
  11. Classes are Reference types (When you instantiate a class, it will be allocated on the heap) and Structures are Values types (lives on stack). So a class variable can be assigned null. But we cannot assign null to a struct variable.
    Static Public void Main (string [] arg)

    {

    MyClass _myClassObject1 =new MyClass ();
    _ myClassObject1.DataMember=10;
    MyClass _myClassObject2 =_myClassObject1;
    _ myClassObject2.DataMember=20;

    }
    In the above program, “MyClass _myClassObject2 =_myClassObject1” instruction indicates that both variables of type MyClass myClassObject1and myClassObject2will point to the same memory location. It basically assigns the same memory location into another variable of same type.
    So if any changes that we make in any one of the objects type MyClasswill have an effect on another since both are pointing to the same memory location.
    “_ myClassObject1.DataMember=10” at this line both the object’s data members will contain the value of 10. _ myClassObject2.DataMember=20at this line both the object’s data member will contains the value of 20. Eventually, we are accessing datamembers of an object through pointers.
    Unlike classes, structures are value types. For example:
    Structure MyStructure

    {

    Public Int DataMember; //By default, accessibility of Structure data
    //members will be private. So I am making it as
    //Public which can be accessed out side of the structure.

    }

    Static Public void Main (string [] arg)

    {

    MyStructure _myStructObject1 =new MyStructure ();
    _ myStructObject1.DataMember=10;
    MyStructure _ myStructObject2 =_ myStructObject1;
    _ myStructObject2.DataMember=20;

    }
    In the above program, instantiating the object of MyStructuretype using newoperator and storing address into _myStructObjectvariable of type MyStructureand assigning value 10to data member of the structure using “_ myStructObject1.DataMember=10”. In the next line, I am declaring another variable_myStructObject2of type MyStructureand assigning _myStructObject1into that. Here .NET C# compiler creates another copy of _myStructureObject1object and assigns that memory location into MyStructurevariable _myStructObject2.
    So whatever change we make on _myStructObject1will never have an effect on another variable _myStructObject2of type MyStructrue. So that’s why we are saying Structures are value types.

  12. All struct types implicitly inherit from the class System.ValueType
  13. In general, classes can be used when you have more complex data. And if you think that these data to be modified after creating an instance of class, then classes are absolute methods.
  14. Structures can be used for small data structures. If developer feels that data members of structure cannot to be modified after creating structure, then having structure will suit.
  15. The “this” keyword gives different meaning in structure and class. How?
    In class, “this” keyword is classified as value type of class type within which it is used like inside instance constructor or instance method.
    In structure, “this” keyword is classified as variable type of structure type within which it is used.
  16. Classes can have explicitly parameterless constructors whereas structures can’t. A structure must always have the default parameter less constructor defined as public but a class might have one, so you can’t define a private parameter-less constructor as in the following:
    struct Me

    {

    private Me()// compile-time error
    {
    }

    }

    class Me

    {

    private Me()// runs Ok
    {
    }

    }

  17. A structure can’t be abstract, a class can.
  18. Structure can inherit from interface not from any class or from other structure
 
Leave a comment

Posted by on August 7, 2015 in .NET

 

Sql Query vs Linq to Sql Query VS Entity Query

select ORG_NAME from dbo.ORG_MASTER where ORG_NAME like #value#

IList<string> exp = (from OM in dbo.MASTER where OM.NAME.Contains(_NAME) select OM.NAME).ToList<string>();

IList<string> expected = dbo.MASTER.Where(x => x.NAME.Contains(_NAME)).Select(x => x.NAME).ToList<string>();

 
Leave a comment

Posted by on May 21, 2015 in .NET

 

Left vs right join in linq to sql

here’s how you do a left join in Linq:

var results = from data in userData

join growth in userGrowth

on data.User equals growth.User into joined

from j in joined.DefaultIfEmpty()

select new

{

UserData = data,

UserGrowth = j

};

If you want to do a right join, just swap the tables that you’re selecting from over, like so:

var results = from growth in userGrowth

join data in userData

on growth.User equals data.User into joined

from j in joined.DefaultIfEmpty()

select new

{

UserData = j,

UserGrowth = growth

};

The important part of the code is the into statement, followed by the DefaultIfEmpty. This tells Linq that we want to have the default value (i.e. null) if there isn’t a matching result in the other table.

A right outer join is not possible with LINQ. LINQ only supports left outer joins. If we swap the tables and do a left outer join then we can get the behavior of a right outer join.

 
Leave a comment

Posted by on May 21, 2015 in .NET

 
 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: