How to Export data from Excel to SQL using C#

The current example will show you how to export data from Excel to SQL database using C#. It requires OLEDB drivers need to be installed on your system(OLEDB drivers comes by default with office 2007 products) or you can download explicitly from http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en.

string strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0\"";

//OLEDB connection for Excel

OleDbConnection olConn = new OleDbConnection(strconn);
OleDbDataReader orr = default(OleDbDataReader);
olConn.Open();

DataTable dt = olConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);


OleDbCommand od = new OleDbCommand("SELECT * FROM ['" + ConfigurationManager.AppSettings["ExcelSheetName"].ToString() + "$']", olConn);
if (od != null)
{
orr = od.ExecuteReader();
//If Data Exists
if (orr.HasRows)
{
string strconn1 = null;
strconn1 = /* Provide connection string */
SqlBulkCopy sqCopy = new SqlBulkCopy(strconn1);
//Give the Destination table name
sqCopy.DestinationTableName = "SampleTable";
//Here the only condition is it takes the first character of first column of first row and creates column with it.
Eg: if there is a word like "Finance" in the first column of first row it takes the column names as Finance and the second column as F2 and 3rd as F3 .....

//So we need to create the destination table with the same column names and then do mapping
sqCopy.ColumnMappings.Add("F2", "F2");
sqCopy.ColumnMappings.Add("F3", "F3");
sqCopy.ColumnMappings.Add("F4", "F4");
sqCopy.ColumnMappings.Add("F5", "F5");
sqCopy.ColumnMappings.Add("F6", "F6");
sqCopy.ColumnMappings.Add("F7", "F7");
sqCopy.ColumnMappings.Add("F8", "F8");
sqCopy.ColumnMappings.Add("F9", "F9");
sqCopy.ColumnMappings.Add("F10", "F10");
//Then write to the server
sqCopy.WriteToServer(orr);

XMPP Gateway Works with OpenFire

Now the XMPPGateway works with Openfire.All you need to do is to install the latest patch available for XMPP Gateway at
http://support.microsoft.com/?kbid=977187
and configure openfire to support only TCPDIALBACK

Differences between Idictionary and Hashtable

Both Idictionary and Hashtable are use to store data in a (Key,Value) format.
Here " Key " is any unique token which is used to identify its corresponding data or value uniquely, "Value" is the data which you want to store it can be any thing string, integer, float etc..
Both(Idictionary and Hashtable) are derived from System.Collections Class. are used for

1) Inserting Data
2) Deleting data
3) Modifying Data
4) Searching for a particluar value

Idictionary:

In Idictionary we need to clearly define what type of value we want to store. let say we want to store both key and value as strings, here is the syntax


IDictionary < string,string > Name;
Name= new Dictionary < string,string >

So in the above we have created Name as Idictionary where Key and value both takes strings.

Hashtable:

In hashtable no need to define any signature i.e you can store any kind of values. Hashtable take every key and value as an object but not as the value you have entered means eventhough we have entered Key and value as strings Hashtable internally treats them as objects but not as strings.

In the same hashtable you can store different values let say we have store Ist entry as (string, int) 2nd entry can be any thing let say (int, float). Since Hashtable treat every key or Value as an object .

Hashtable namelist;
nameList= new Hashtable();

Differences between Hashtable and Idictionary:

• In Idictionary we need to explicitly define signature i.e what type of Key and value we want to store. Hashtable no need to define any structure or signature.

• In Idictionary once we have defined signature there is no chance of entering values other than the signature we have specified. where as within the same hashtable we can enter any type of key,value pair.

• In Hashtable we required Boxing and Unboxing to do any kind of manipulation with values. where as for Idictionary we don't require any kind of boxing and unboxing as we are already defined the signature.

• Hashtable is useful if you don't know what all kind of values you might be storing in it. Idictionary is used if you know exactly what kind of (key , value) pair you will be using.Let say if you know that both Key and value are always strings then use Idictionary.

• Hashtable is costly as every operation(inserting, modifying, deleting..) requires either Boxing or UnBoxing but where as Idictionary doesn't require any such things since we know the signature before hand.
  
  
  

How to Run any Application at System start up

The below lines will create a registry entry at following path "SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Run", this is the path from where windows will start all the applications at the system start up.

RegistryKey regKey = Registry.CurrentUser.OpenSubKey("SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Run", true);

//Give the name of exe as first parameter . Here Sample.exe is the name of exe. The second parameter should be path from where it will start executing the exe's. Here
"Application.ExecutablePath" will directly give the path of exe.

regKey .SetValue("Sample", Application.ExecutablePath.ToString());

How to Read and write to a Xml document in c#

Following things i will be covering in this blog

1) Creating a Xml file.
2) Writing in to a Xml file .

Creating a Xml File :

• To create a Xml file first we need to know the directory structure or the path where it has to create the Xml file. If the specified directory structure doesn't exist we need to create it.

Note : Make sure that you give only the path but not till the file Name.

Eg: If you want your xml file at

"C:\\Documents and Settings\\All Users\\Desktop\\Sample\\SampleConfig.xml"
give the path till SampleFolder if you give full path(till the file name in this
case Sampleconfig.xml) it will create the Folder by name SampleConfig.xml .

string xmlFilePath="C:\\Documents and Settings\\All Users\\Desktop\\Sample";

if (!Directory.Exists(xmlFilePath))
Directory.CreateDirectory(xmlFilePath);

//Now append the filename to the path
xmlFilePath+= "\\SampleConfig.xml";

So in the above code snippet it will create a folder by name Sample.

Once we know the path we can now create the xml file.

XmlTextWriter txtWriter = new XmlTextWriter(filePath, null);

//This method is used to validates whether we are writing well-formed xml document(its optional)

txtWriter.WriteStartDocument(true);

//To create the xml file with indentation if we don't give this it keeps writing as a single line.Its optional.

txtWriter.Formatting = Formatting.Indented;

txtWriter.WriteStartElement("EmpDetails");
txtWriter.WriteStartElement("EmpID");
txtWriter.WriteEndElement();
txtWriter.WriteStartElement("EmpName");
txtWriter.WriteEndElement();
txtWriter.WriteStartElement("Age");
txtWriter.WriteEndElement();
txtWriter.WriteEndElement();

It writes the following lines

<EmpDetails>

<EmpID />

<EmpName />

<Age />

</EmpDetails>"

Here the important to point to note is that how we are creating parent node and child nodes. Here "EmpDetails" is the root node and EmpID,EmpName and Age are the child nodes. To create Parentnode close its corresponding EndElement(WriteEndElement()) at the end of its Child nodes.

From the above code snippet we can see WriteEndElement() of EmpDetails node is written at the end,after all the child nodes are completed but where as for other nodes we are writing at the very next line. We can create nested nodes too.

• We can create Xml nodes with default values, by using "WriteElementString" method of XmlTextWriter

txtWriter.WriteElementString("Age", "24");

so here it will create a node with default value of age as 24.

• We have till now created only the nodes but still they are not written in to the file, to do so we need to make use of "Flush" method.

//This method writes above data in to Xml file

txtWriter.Flush();

Its always good practice to close the XmlTextWriter if its no longer in use.

txtWriter.Close();

Writing in to Xml:

• To write in to xml, first we need to know the path from where we need to get the xml file.
• Load the Xml in to XmlDocument.
• Traverse through each node and check whether its the same node for which we need to give the value.

Step1 :

XmlDocument xmlDoc = new XmlDocument();
filePath+=file://sampleconfig.xml/;

Step2 :

xmlDoc.Load(filePath);

Step3:

XmlNodeList xmlNodeList;

//This gives the list of child nodes

xmlNodeList = xmlDoc.DocumentElement.ChildNodes;

//Traverse through Each node and give the values

foreach (XmlNode childNode in xmlNodeList)
{
if (childNode.NodeType == XmlNodeType.Element)
{
if (childNode.Name == "EmpID")
childNode.InnerText = "12345";
if (childNode.Name == "EmpName")
childNode.InnerText = "OM";
if (childNode.Name == "Age")
childNode.InnerText = "24"; }
}

To save the configuration file use the save method of XmlDocument.

xmlDoc.Save(filePath);

How to Read and write Asynchronously to Socket ?

C# provides built in methods to read and write data asynchronously in to the sockets. All this methods are non-blocking methods so we can still continue processing other steps without waiting for Asynchronous callback to complete since they are executed in a different thread.

Read Data Asynchronously :

socket.BeginReceive is a method defined in System.Net.Sockets to read data from a connected socket.
Syntax:(one of the overloaded methods)

BeginReceive(byte [] buffer, int offset, int size, Socket flags, AsyncCallback, object state)

Here

buffer = array of system byte to store data
offset = position in buffer parameter from which it has to store data
size = number of bytes it can received
Socket flags = bitwise combination of socket flags
AsyncCallback = a delegate which is invoked when we have received something from socket
state = a user defined object which contians information about receive operation. This is also passed to EndReceive method.Generally it will have atleast the socket from which it has to read.

Example :

byte[] buffer = new byte[BufferSize];

public class StateObject
{
public Socket workSocket = null; // Client socket.
public byte[] buffer = new byte[BufferSize];
}
StateObject state=new StateObject();

Sock.BeginReceive(buffer, 0, 1024, 0, new AsyncCallback(ReadCallback), state);

So here buffer is the bytearray to store data starting from offest ,0 and to the maximun of 1024 bytes . ReadCallback is the delegate which is invoked when we received something on socket.

The asynchronous BeginReceive operation must be completed by calling the EndReceive method.

To cancel a pending BeginReceive, call the Close method.




public void ReadCallback(IAsyncResult ar)
{

// Retrieve the state object and the handler socket from the asynchronous state object.
state = (StateObject)ar.AsyncState;

if (state != null)
{
// Read data from the client socket.
int bytesRead = handler.EndReceive(ar);

//Convert the bytedata to string
string content = System.Text.ASCIIEncoding.ASCII.GetString(state.buffer, 0, bytesRead);

}

Send data Asynchronously:
A Method called BeginSend is used to start sending data asynchronously on a socket. We can create a callback method that implements the AsyncCallback delegate and pass its name to the BeginSend method.

The callback method make use of EndSend method used to send data.

Syntax:

BeginSend(byte [] buffer, int offset, int size, Socket flags, AsyncCallback, object state) ;

Example :

Let sey we need to send string "HelloWorld".

string str="HelloWorld";
byte[] byteData = Encoding.UTF8.GetBytes(str);

sock.BeginSend(byteData, 0, byteData.Length, 0, new AsyncCallback(SendCallback), sock);

So here SendCallback is used as delegate to send data

private void SendCallback(IAsyncResult ar)
{

// Retrieve the socket from the state object.
sock = (Socket)ar.AsyncState;

// Complete sending the data to the remote device.
int bytesSent = sock.EndSend(ar);
}

Threads Synchronization in C#

Following are the ways to handle Threads Synchronization in C#

1)Using Lock Mechanism with keyword "Lock"
2)Using Monitors
3)Using Wait and Event Handles

Thread Synchronization using Lock Key word:

Using "Lock" keyword allows only one thread to execute a piece of code while the other threads keep waiting until the lock is released. Lock takes object as an argument which is derived from System.object.

Eg:
1)
class Synchronization
{
private object objLock = new object();
void SampleMethod()
{
//Other threads keep waiting here until the other thread finishes the execution
lock (objLock)
{
//Block which need to be accessed by only one thread at a time
}
}
}

2) If we have multiple instances of the same class, the above example will not work as each instance(with Mutliple threads ofcourse) might have acquired lock separately.
To avoid this we need to declare the Object as "staic" and to avoid any misuse of the object(like some one assigning that object to something else) we can use "readonly" field.

Eg: so the above example is changed as below

class Synchronization
{
private static readonly object objLock = new object();

void SampleMethod()
{
//Other threads keep waiting here until the other thread finishes the execution
lock (objLock)
{
//Block which need to be accessed by only one thread at a time
}
}
}

Thread Synchronization using Monitor:

Monitor is same as Lock ,infact Lock uses Monitor mechanism internally. Monitor class is predefined in C#. Net it has two methods Enter and Exit. Enter allows only one thread to enter the piece of code.

class Synchronization
{
object monitorObj = new object();
public void SampleMethod()
{
System.Threading.Monitor.Enter(monitorObj);
//Piece of code which needs to be Synchronized
System.Threading.Monitor.Exit(monitorObj);
}
}
Lock are relatively more used than monitor as it is concise and easy to use.

Thread Synchronization using Events and Wait Handles:

The above two Process (Lock and Monitor) is used for Locking a Piece of code under the method in which the lock is specified but it is not usefull when we want the Lock the thread accross series of methods. For this we need to use Events mechanism in C#. There are two states for events "signalled " and "un-signalled", by default the events are in un-signalled state.There are two built in events in C# .

1) AutoResetEvent

2) Manual ResetEvent

Thread is activated only if we change the state from un-signalled state to signalled state

(i)AutoResetEvent:

In AutoResetEvent, once execution is completed it automatically changes its state back to un-signalled . So once a new thread comes again at the same block it again starts executing (since it is in un-signalled state) .

(ii)ManualResetEvent:

In ManualResetEvent ,once execution is completed it keeps the last state untill a explicit reset is done for that event. Let say event initially is in un-signalled state and one thread executes the process and set it to as Signalled state, so all the threads coming after this thread will never executes the same process as it is already set as signalled state by first thread until it is explicitly Reset.

Example:

class AutoResetEventExample
{
private System.Threading.AutoResetEvent autoEvent;
public void function1()
{
// One thread has entered
//Do the processing
//blocks the Thread untill it receives a signal
autoEvent.WaitOne();
//Starts processing once its receives the Signal
}
void Main()
{
//Creating a AutoResetEvent by initializing the event in un-signalled state(by passing false)
autoEvent = new System.Threading.AutoResetEvent(false);
//Creating a new thread
Thread t = new Thread(function);
t.Start();
Thread.Sleep(1000);
//Again signalling the worker thread as it is back to un-signalled state
autoEvent.Set();
}
}

How to Create Singleton Class in C#

Singleton Class:
Singleton class means a class having one and only one instance at any point of time irrespective of the number of times it is defined or used.
Steps to create Singleton class:
1) Declare the instance of a class as static
2) create a private constructor( so that nobody can create new object ) with body as empty
3)Define a static method to create the instance of a class. If the instance already exists return the same instance and if it doesn't exist create a new one.
Example:

class Sample
{
//Creating a private static instance of class
private static Sample inst = null;
//create a lock object
private static readonly object objLock = new object();
//Creating a private constructor
private Sample()
{
}
//Creating a Static method to create the instance of sample class
public static Sample CreateInstance()
{
//Locking as in mutlithreading scenario there is a chance to create mutliple instances
lock (objLock)
{
if (inst == null)
{
inst = new Sample();
}
}
return inst;
}
}