SQLBulkCopy

I was looking to copy some bulk data from one of the data table in memory to my SQL DB table. I tried using DBAdapter.Update but it didn’t work as in my source datatable none of the row was in updated/new stage. Then after searching a lot, I found “SQLBulkCopy”.

The SqlBulkCopy class can be used to write bulk data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance. To copy the source data to destination table in the sql server, need to set destination table name and execute “WriteToServer” method. Following code snippet

// create sqlBulkCopy object
SqlBulkCopy bulkCopy = new SqlBulkCopy(connString);

// set destination table name for sqlBulkcopy object
bulkCopy.DestinationTableName = "[Table Name]";

// write source data to destination sql table
bulkCopy.WriteToServer();

Performancewise using SQLBulkCopy is far better than using normal Insert/Update commands.

Reading csv file in datatable

Recently I had to work on one problem where I had to read the csv file and filling up the data table with the csv file data. I used the OLEDBAdapter to accomplish that with just few lines of code. Following is the code :

            string query = "SELECT Symbol, [Name of Company], FROM [just file name with extension]";
            string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + [csv file path without file name] + ";" + "Extended Properties=’text;HDR=YES;’";

            //create dataadapter object
            OleDbDataAdapter adapter = new OleDbDataAdapter(query, connStr);

            // create table
            DataTable dtSymbolDetails = new DataTable("ScriptDetails");
            dtSymbolDetails.Columns.Add("Symbol");
            dtSymbolDetails.Columns.Add("Name of Company");

            // fill the table with data using adapter
            adapter.Fill(dtDetails);

So above code fills up the details of the csv file in the data table for further use.

Also note the text in red above. If column name contains the space then in query the column name should be put between [ ]. I learned it in bit hard way. It shows I’m so poor in my sql knowledge.

Attached Properties

Attached property is a special type of dependency property that is intended to be used as global property. One of the purpose of Attached property is to allow child elements to set different value for property which has been defined on parent element. Examples of Attached properties are DockPanel.Dock, Grid.Row, Grid.Column, Canvas.Left, Canvas.Top etc. The use of Attached property is similar to static property and it can not be accessed through instance object.

Attached properties don’t implement wrappers for property because Attached property may not be part of the clr namespaces for the instances on which it is set. However, attached property implementer provides the accessors to access the property value.

DockPanel dockPanel = new DockPanel();

Button myButton = new myButton();

dockPanel.Childern.Add(myButton);

DockPanel.SetDock(myButton, Dock.Left);

Defining  attached property is almost same as defining the dependency property. The only change is during the registration instead of ‘Register’ use ‘RegisterAttached’ method of DependencyProperty. Also, don’t implement the property wrapper for the attached property. But instead of property wrapper implement the Get and Set accessor for the property.

Difference between String and string

This is what MSDN has to say about this :

In C#, the string keyword is an alias for String. Therefore, String and string are equivalent, and you can use whichever naming convention you prefer.

So in other words string is equivalent to System.String ( data type defined in .net framework).

using System;

…………..

string s1 = “ABC”;
String s2 = “DEF”;

above line of code will compile and work as expected.

// using System;

…………..

string s1 = “ABC”;
String s2 = “DEF”;

but above line of code will produce compile time error as it can not identify the “String” data type.

So in short, (in c#) use either “string” or “System.String”. Functionally, using any of these doesn’t make any difference. The same explaination applies to other data types i.e. char & Char, object & Object etc.

——————————————————-

P.S. : I have heard that this same question being asked many time during interviews. I really don’t see any logic in testing the knowledge of candidate with such question. Programmer will never be able to see any difference in using “String” or “string” unless he/she has not added the directive “using System;” which is there by default when new class is created. But anyway if someone doesn’t know the difference this post should help them.

Nullable Types

Nullable types are special types of value type variables (including struct) which can accept the correct range of values for its underlying value type, plus an additional null value. For example, a Nullable<Int32> (pronounced “Nullable of Int32″) can be assigned any value from -2147483648 to 2147483647, or it can be assigned the null value. A Nullable<bool> can be assigned the values true/false, or null.

Nullable type variables can only be defined for value types and not for the reference types because reference types any way accept the null as valid value. Each nullable type is constructed from the >)>Nullable<T> structure where T is underlying variable type for which Nullable type to be created. Nullable types can be defined by appending ‘?’ to datatype text in declaration as shown below :

int? nullableInt = 4;

double? nullableDouble = 9.2;

Properties :

Nullable<T> structure has two read only properties defined namely ‘HasValue’ and ‘Value’.

HasValue : returns true/false depending upon the value stored inside the nullable variable. If value of the variable is null it returns false else it will return true.

Value : returns underlying value of the nullable variable if it is not null. If underlying value for the variable is null then it will throw “InvalidOperationException”.

Method :

Nullable<T> structure has one method named as ‘GetValueorDefault”.

GetValueorDefault : This method has got two overloads.

GetValueorDefault() : This method returns the underlying value of the variable if ‘HasValue’ is true else it will return the default value of underlying variable. e.g. when this method is called on nullable<int> and ‘HasValue’ is false then this method will return 0. If the underlying data type is Boolean then default value returned will be ‘false’.

GetValueorDefault(T) : In this method, default value is passed as an argument. So whenever ‘HasValue’ is false, it will return the passed value as default value.

Boxing and UnBoxing :

Nullable type variable is also an example of boxing and unboxing. When value is assigned to Nullable type variable, CLR boxes this value assigned to variable and stores it. It means, if ‘HasValue’ property is true, boxed value for the variable is present and if it is false, null is boxed. When nullable varaiable’s value is retrieved, CLR UnBox the previously Boxed value and return.

Explicit Conversion :

int? x = null;

int y = x; (This statement gives compile time error because conversion to int data type from Nullable<int> data type requires explicit conversion i.e. int y = (int)x. If during explicit conversion value of nullable variable’s (y in our case), value is null then it will throw exception. So it is better practices to use int x = y.GetValueorDefault();

Alternatively, to get the default value for the underlying variable type ?? can be used.

int? i = null;

int y = i?? – 1;

Return value for the y in above code will be -1 because as i is null and i?? will return 0 (default value for int data type).

Attributes

Following post describes about the attributes in the .net framework.

MSDN says, Attribute is a method of associating declarative information with code entities (i.e. types, methods, properties, and so forth). Attributes add metadata information to program. There are two types of attributes : Pre Defined Attributes and Custom Attributes

Pre Defined Attributes :

These attributes are defined inside the .net framework e.g. Serializable, Obsolete etc.

[Serializable]

public class MyClass

{

public MyClass()

{

}

[Obsolete("Do not use. Use New Method", true)]

public void OldMethod()

{ }

public void NewMethod()

{ }

}

Custom Attributes :

Custom attribute is a declarative information created by programmer to meet varied needs. Custom Attribute is like any other class but requires following two conditions :

1. Custom Attribute class must declare “AttributeUsage” at class level (However, providing “AttributeUsage” attribute at class level is not must while declaring custom attribute)
2. Custome Attribute class must derive from System.Attribute or some other attribute ultimately derived from System.Attribute.

Following is the typical example of creating Custom Attribute and its use :

public class MyAttribute : Attribute

{

public MyAttribute() {}

}

[My]

public class MyClass

{

public MyClass() {}

}

Note: it is a convention to use the word Attribute as a suffix in attribute class names. However, when we attach the attribute to a program entity, we are free not to include the Attribute suffix. The compiler first searches the attribute in System.Attribute derived classes. If no class is found, the compiler will add the word Attribute to the specified attribute name and search for it.

AttributeUsage Attribute has three properties namely,

1. AttributeTargets : Through this property, user can define the entities/targets on which custom attribute can be applied. Valid target entities settings are

· Assembly,

· Module,

· Class,

· Struct,

· Enum,

· Constructor,

· Method,

· Property,

· Field,

· Event,

· Interface,

· Parameter,

· Delegate,

Targets can be defined in different combinations for custom attributes e.g.

[AttributeUsage(AttributeTargets.Class | AttributeTargets.Method | AttributeTargets.Constructor)]

public class MyAttribute : Attribute

{

public MyAttribute() {}

}

If user wants to allow custom attributes to be defined on all of above entities, user can use AttributeUsage(AttributeTargets.All)

2. AllowMultiple : This property defines whether this attribute can be defined on the same target multiple time or not. In following example custom attribute has been applied twice at the class level however this is valid as AllowMultiple property has been set to true. If AllowMultiple is false then following piece of code will result in compile time error.

[AttributeUsage(AttributeTargets.Class | AttributeTargets.Method | AttributeTargets.Constructor, AllowMultiple=true)]

public class MyAttribute : Attribute

{

public MyAttribute() {}

}

[My]

[My]

public class MyClass

{

public MyClass() {}

}

3. Inherited : This property defines whether the attributes applied to targets will get inherited to derived class or not.

If AttributeUsage attribute is not applied while creating custom attribute then it will use default values. Following is the table describing these default values :

Parameter	Description	Default
AttributeTargets	What element of the class the attribute is targetting	AttributeTargets.All
IsAttributeMultiUse	Can the attribute be set multiple times.	false
IsAttributeInherited	The attribute can be inherited from derived classes.	false

Let’s consider the combination of these properties in order to understand this better.

Attribute Targets	Allow Multiple	Inherited	Code	Default
All	false	False	[My(“Base”)] public class MyBase {} Public class MyDerived : MyBase {}	No attribute on MyDerived class
All	true	false		Still no attribute on MyDerived class as Inherited property is still false
All	false	true	[My(“Base”] public class MyBase {} [My(“Derived”] Public class MyDerived : MyBase {}	Although Inherited property is true but AllowMultiple is false, only attribute with “Derived” information will be applied to derived class
All	true	true		Attribute with both “Base” and “Derived” information will be applied to MyDerieved class

Positional vs. Named Parameters

Positional parameters are parameters of the constructor of the attribute. They are mandatory and a value must be passed every time the attribute is placed on any program entity. On the other hand Named parameters are actually optional and are not parameters of the attribute’s constructor.

[AttributeUsage(AttributeTargets.Class | AttributeTargets.Method | AttributeTargets.Constructor, AllowMultiple=true)]

public class MyAttribute : Attribute

{

protected string name;

protected string info;

public MyAttribute(string attName)

{

name = attName;

}

public string Name

{

get { return name; }

}

public string Info

{

get { return info; }

set { info = value; }

}

}

[My("Tau")]

public class MyClass

{

public MyClass() {}

}

[My("Tau", Info = "This is additional Information")]

public class AnotherClass

{

public AnotherClass() { }

}

In the above example, “Name” is positional parameter while “Info” is named parameter.

It is desriable to use Named parameters in attributes rather than using overloaded constructors in attribute definition.

Now, enough said about how to define attribute. Another important aspect is to how to access this declarative information. As mentioned in the beginning of the article, attribute values can be retrieved using Reflection. .Net framework provide “Attribute.GetCustomAttributes” method which returns the collection of attributes defined on given type. Overloads are available to retrieve the attributes applied to specific targets i.e. methods, constructor, etc. Following code snippet shows how to retrive the value of attribute properties.

Attribute[] attrs = System.Attribute.GetCustomAttributes(t); // reflection

foreach (Attribute attr in attrs)

{

if (attr is MyAttribute)

{

MyAttribute a = (MyAttribute)attr;

System.Console.WriteLine(“Name is {0}, Info is {1}”, a.Name, a.Info);

}

}

Few minds may have a question like the same functionality can be implemented by implementing the property in a class then why to use attribute? Following is the justification of using Attributes :

1. Attributes can be defined at different target levels i.e. methods, constructors, assembly etc. While properties can only be declared at class level.

2. Also, attribute is declarative information. So this information is assigned at the time of compilation and assigned value is not changed normally at runtime. While property value can be changed at runtime.