December 26, 2012

Import Excel file into Dataset …



In this simple blog I’m going to explain within few steps how we can connect to a MS Excel file and read the first sheet content in a separated dataset…

Right ..this is a very useful strategy …where we really take advantages from this in improving our software’s …


Connect to excel …

We can connect to Excel file in the same way we connect to any other data source such as MSSQLServer …in general all we need is a valid connection-string and connection instance to connect …

Below code sample illustrate a connection-string example to connect Excel file …

string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strPath + ";Extended Properties=Excel 12.0;";

Note that the variable strPath is the actually path for the excel file …now we can use the connection instance to open the connection directly to the excel file…as illustrated below ….

OleDbConnection MyConnection = new OleDbConnection(connStr);


 Import to Dataset …

Below code illustrate how we can read the first sheet in the excel file and fill it into a dataset instance …

// Declarations :
DataTable dtFullData;
OleDbDataAdapter MyAdapter;
OleDbCommand CmdSelect;

// Connect to the excel file :
OleDbConnection MyConnection = new OleDbConnection(connStr);

// Prepare Command :
CmdSelect = new OleDbCommand(@"SELECT * FROM [Sheet1$]", MyConnection);

// Fill DataSet :
dtFullData = new DataTable("ExcelSheet1");
MyAdapter.FillSchema(dtFullData, SchemaType.Source);
MyAdapter.Fill(dtFullData);


From the code above it’s a very simple …all we need is to execute the command in the excel file (such as this command or query SELECT * FROM [Sheet1$] ) and fill the dataset with the result using the adapter …


Hope this help …


March 27, 2012

WMI – Query to get Network Adapter information



It was great day when I discover WMI in .Net framework, many thanks for .Net framework J , you can find everything you need there ….

The story began when I face an issue required me to save some information related to the current machine that run my windows application. Sometimes it’s important to get some information related to the current machine such as the current IP Address, or the current Hostname …may be some processor information too …etc.

In my case it was important to get IP Addresses information to implement some business security on my Windows Application, at this moment I start searching for different techniques to do that …until I found the “WMI” …

Hey… Why don’t ask your windows about the information you need? Yes it’s really like that …or in technical word sends a Query the windows…!!
WMI stand for “Windows Management Instrumentation”, below is a definition I read it from MSD …

“   Windows Management Instrumentation (WMI) is the infrastructure for management data and operations on Windows-based operating systems. You can write WMI scripts or applications to automate administrative tasks on remote computers but WMI also supplies management data to other parts of the operating system and products, for example System Center Operations Manager, formerly Microsoft Operations Manager (MOM), or Windows Remote Management    “



In the below example I will show how it was easy to get some of IP information from My PC , first of all you need to the reference “system.managment” to your application since we need to include this reference on our form class ….

Now what’s required is to Query the windows about my Network Adapter Configuration, since the IP come as a part of “Network Adapter” ….

I assume you will build a windows form with listbox control to fill it with the required IP information …

We will use “ManagementObjectSearcherclass to execute our query in windows, note that   “ManagementObjectSearcherwill represent the WMI class …below method will execute a query that ask for enabled IP on the machine , taking in the consideration that the machine was windows -32.


private void ListNetworkInformation()
        {

            // Search on all network-adapters (only enabled) : 
            ManagementObjectSearcher wmiSearch = new ManagementObjectSearcher
                       ("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = 'TRUE'");

            ManagementObjectCollection mgmCollection = wmiSearch.Get();


            foreach (ManagementObject mgmItem in mgmCollection)
            {
                // Get HostName :
                string HostName = mgmItem["DNSHostName"].ToString();
                this.listBox1.Items.Add("DNSHostName : " + HostName);

                // Get Description :
                string Description = mgmItem["Description"].ToString();
                this.listBox1.Items.Add("Description : " + Description);               

                // List IP Addresses :
                string[] IPAddrs = (string[])mgmItem["IPAddress"];
                foreach (string ipAdd in IPAddrs)
                    this.listBox1.Items.Add("IP-Address : [ " + ipAdd + " ]");

                // IPSubnets :
                string[] IPsub = (string[])mgmItem["IPSubnet"];
                if (IPsub != null)
                   foreach (string ipsubnet in IPsub)
                    this.listBox1.Items.Add("IPSubnet : [ " + ipsubnet + " ]");

                // Default IP Gateways
                string[] IPgateways = (string[])mgmItem["DefaultIPGateway"];
                if (IPgateways != null)
                    foreach (string defipgateway in IPgateways)
                      this.listBox1.Items.Add("Default IP Gateway : [ " + defipgateway + " ]");

                this.listBox1.Items.Add("==============================================");
            } 
        }



Calling the above method will get different information such as: HostName, IP Addresses, IP Subnets & IP-Subnets …etc.

As you see we execute this query in windows management (SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = 'TRUE'), which a WMI instance ….looping the management collection to get the required item.

Hope this sample help in get an idea about WMI …for more information you can check the MSDN ….