C# Getting Information about your Drives

by CameronM 11. July 2010 17:56

Many moons ago I wrote a series of posts on how to access the file system from your Access database using Scripting.FileSystemObject in VBA. Just for fun and partly as a learning experience, I thought it was time to look at how the same can be achieved in .NET and more specifically C#.

This first post I wrote covered how to get information about the drives currently available on the host system. Here’s the VBA to .NET comparison.

VBA: Scripting.FileSystemObject.Drives = .NET: System.IO.DriveInfo.GetDrives()

To get started, open Visual Studio 2010 and create a new Console Application. In the Program.cs file add a using statement for the System.IO namespace, which contains all of the methods you’ll need to access drives, directories and files.

Code Snippet
  1. using System.IO;

Now insert the following code in the Main method. This code will get an array of DriveInfo objects, which contain the basic information of all the drives available on your system.

Code Snippet
  1. // get a list of drives available on the system
  2. DriveInfo[] drives = DriveInfo.GetDrives();
  3.  
  4. foreach (DriveInfo drive in drives)
  5. {
  6.     Console.WriteLine(drive.Name);
  7. }

Some interesting properties of the DriveInfo object are Name, AvailableFreeSpace and DriveForm (FAT/NTFC).

Tags:

C#

Creating a Simple Collection Class

by Admin 25. March 2010 17:43

Creating a group of objects that share common properties if one of the key features of Object Orientated programming. An object may represent real-life 'things' such as a person or vehicle, or business related objects such as a documents or reports. One of the simplest ways to create and retrieve such as group of objects is to create a Class in .NET.

Creating a Class

Our sample class represents Avatar images that can be associated with users of our website. We will use the image name as the name of the Avatar and all the images are located in the  same folder on the web server.[hotellink:brisbane]

Select Add New Item from the Visual Studio IDE and create a new Class called Avatar. Visual Studio will create the shell for our new class, which includes a number of default Namespaces as well as the constructor for Avatar.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
 
/// <summary>
/// Summary description for Avatar
/// </summary>
public class Avatar
{
    public Avatar()
    {
        //
        // TODO: Add constructor logic here
        //
    }
}

As you already know methods in .NET can be overloaded. This means that we can create any number of public Avatar() methods depending on how we want the class to be called. In our example we will allow two ways to create a new Avatar, the first where the name will be set after the object is created and a second where the name is included in the initial call.

Add a private variable to the class that will hold the avatar name a new Property to enable the calling function to get and set the value of this variable. We could set the local variable _name to public so that we could access its value directly, however it is often better to wrap these values as Properties, so that we can add an validation or custom handling that may be required.

public class Avatar
{
    private string _name;
 
    ....
 
    public string Name
    {
        get { return _name;}
        set { _name = value; }
    }
}

We can know create a second constructor for Avatar with a parameter that sets the value of Name.

    public Avatar(string AvatarName)
    {
        this.Name = AvatarName;
    }

Create a Collection

A single Avatar object is not particularly useful, so we will build another class that enables us to quickly and easily add and remove Avatars to a list, or Collection. [silverlightslideshow:example]

Create a new Class called AvatarCollection. As we want this class to act as a collection of objects we need to ensure it inherits from System.Collections.CollectionBase.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
 
/// <summary>
/// Summary description for AvatarCollection
/// </summary>
public class AvatarCollection : System.Collections.CollectionBase
{
    public AvatarCollection()
    {
        //
        // TODO: Add constructor logic here
        //
    }
}

A simple collection needs to have functionality to add new items. remove a particular item and to return or retrieve a particular item. To enable this we add some very simple methods called Add, Remove and Item.

    public void Add(Avatar av)
    {
        List.Add(av);
    }
 
    public void Remove(int index)
    {
        List.RemoveAt(index);
    }
 
    public Avatar Item(int index)
    {
        return (Avatar)List[index];
    }

The Add method take an Avatar object as a parameter and adds it to the List (or Collection) or objects. As expected, the Remove method removes whatever object is located at the specified index position. Likewise the Item method returns an Avatar object located at the specified index position, notice that we have to explicitly cast the object to an Avatar as List can contain any object type. 

In our example, we would like to create a method that pre-populates the List with all Avatars located within a specific folder on the web server. We can again overload the constructor AvatarCollection to call whatever methods we require.

    public AvatarCollection(string FolderName)
    {
        GetAvatars(FolderName);
    }
 
    public void GetAvatars(string FolderName)
    {
        //retrieve an array of files from the directory
        DirectoryInfo dir = new DirectoryInfo(System.Web.HttpContext.Current.Server.MapPath(FolderName));
        FileInfo[] files = dir.GetFiles();
 
        //create a new Avatar for each file and add to list
        foreach (FileInfo file in files)
        {
            Avatar av = new Avatar(file.Name);
            this.List.Add(av);
        }
    }

The AvatarCollection constructor calls the GetAvatars method which creates an Avatar object for each file in the specified folder and then adds it to the list. By making the GetAvatars method public, it can be called later, if the blank constructor is used. You may need to add some logic to delete any objects already in the list should the user call GetAvatars more than once.

Using the Collection

You can now make use the Avatar object and related collection within your code as a quick and easy DataSource. For instance, the code below sets the datasource of a drop-down list to an AvatarCollection. 

            AvatarCollection av = new AvatarCollection("avatars");
 
            this.AvatarList.DataSource = av;
            this.AvatarList.DataTextField = "Name";
            this.AvatarList.DataBind();

Tags: , ,

ASP.NET | C#

Reading XML files with child elements

by CameronM 11. March 2010 17:41

In the previous post we looked at how to create a DataSet by reading from a simple XML file. In this port we will investigate how .NET handles more complex XML files and in particular how the .NET DataSet handles XML files containing multiple child elements under the root.

<?xml version="1.0" encoding="utf-8" ?>
<AppSettings>
  <setting name="CurrentInstance" serializeAs="String">
    <value>1</value>
    <currentStatus>
      <status>live</status>
      <lastUpdated>01-01-2010</lastUpdated>
    </currentStatus>
  </setting>
  <setting name="OutputDirectory" serializeAs="String">
    <value>\\SV-OCRMGR\mbrc\ocr\xmloutput</value>
    <currentStatus>
      <status>live</status>
      <lastUpdated>01-01-2010</lastUpdated>
    </currentStatus>
  </setting>
</AppSettings>

As we found in the previous post, when .NET parses the XML into the DataSet it creates a DataTable for the root element, in thise case the table is called AppSettings. With the more complex XML shown above, simple elements, like value are parsed into a column of the AppSettings table. Elements that contain child elements themselves, such as currentStatus are parsed into a new DataTable within the DataSet. .NET also creates a relationship, similar to a foreign key relationship you may be familiar with from database development.

We can modify the code from the previous post to take advantage of this functionality.

//declare a new DataSet
DataSet ds = new DataSet();
 
//read the XML file into the DataSet
ds.ReadXml("AppSettings.xml");
 
 
DataRow[] drChildren;
DataRelation dr;
 
//access each node in the XML file using a DataRow
//in this example the nodes we want are in the settings table
foreach (DataRow drResult in ds.Tables["setting"].Rows)
{
    //access the elements of the XML file using the DataRow columns
    //we will just write them to Trace for now
    System.Diagnostics.Trace.Write(drResult["name"].ToString() + " " + drResult["value"].ToString());
 
    //declare the relationship
    dr = ds.Relations[0];
 
    //although we only expect one row of data GetChildRows returns an array
    drChildren = drResult.GetChildRows(dr);
 
    //iterate thru the array of DataRows to access the values
    for (int i = 0; i < drChildren.Length; i++)
    {
        System.Diagnostics.Trace.Write(" " + drChildren[i]["status"].ToString());
    }
    System.Diagnostics.Trace.WriteLine("");
}

 The output from this file (show below) shows that we have returned the status element for each row in the setting table:

CurrentInstance 1 live
OutputDirectory \\SV-OCRMGR\mbrc\ocr\xmloutput live

What isn't clear is how all this works. To illustrate this better, we can write some code to reveal the relationship .NET created between our original setting table and the child element currentStatus.

//declare a new DataSet
DataSet ds = new DataSet();
 
//read the XML file into the DataSet
ds.ReadXml("AppSettings.xml");
 
//iterate through the relationships
foreach (DataRelation dr in ds.Relations)
{
    System.Diagnostics.Trace.WriteLine(dr.RelationName + " " + dr.ParentTable + " " + dr.ChildTable);
 
    foreach (DataColumn dc in dr.ParentColumns)
    {
        System.Diagnostics.Trace.WriteLine(dc.ColumnName);
    }
}

The output of this codes is:

setting_currentStatus setting currentStatus
setting_Id

.NET has created a DataRelation called setting_currentStatus which links the setting table to the currentStatus table in a field called setting_Id. You'll notice the XML file does not have a field called setting_Id, .NET created that when it parsed the file into the DataSet. In our earlier code we took advantage of this relationship by calling the GetChildRows method on each DataRow in setting. GetChildRows takes as its parameter a DataRelation object, which tells .NET everything it needs to know to grab the related child rows.

As we only had one complex child element in our XML, we were able to use the define the correct DataRelation by simply pointing to the first DataRelation in the collection using the code dr = ds.Relations[0]. Obvisously, when our XML has more than one complex child element, we need a way to determine the DataRelation we want to use. Lets modofy the XML file again and add a new element called currentScope.

<?xml version="1.0" encoding="utf-8" ?>
<AppSettings>
  <setting name="CurrentInstance" serializeAs="String">
    <value>1</value>
    <currentStatus>
      <status>live</status>
      <lastUpdated>01-01-2010</lastUpdated>
    </currentStatus>
    <currentScope>
      <scope>user</scope>
      <lastUpdated>01-01-2010</lastUpdated>
    </currentScope>
  </setting>
  <setting name="OutputDirectory" serializeAs="String">
    <value>\\SV-OCRMGR\mbrc\ocr\xmloutput</value>
    <currentStatus>
      <status>live</status>
      <lastUpdated>01-01-2010</lastUpdated>
    </currentStatus>
    <currentScope>
      <scope>application</scope>
      <lastUpdated>01-01-2010</lastUpdated>
    </currentScope>
  </setting>
</AppSettings>

We have a couple of options when defining the DataRelation, one is to use the index as we did previously, which will return the related children in the order they appear in the XML. The second is to use the name of the DataRelation, which as we saw earlier is created by combining the names of the parent and child tables. Either way, we need to know the structure of the XML file and be confident that it is not going to change too dramatically. Lets modify the code to use the DataRelation name to retrieve the child rows from the newly added currentScope element.

//declare a new DataSet
DataSet ds = new DataSet();
 
//read the XML file into the DataSet
ds.ReadXml("AppSettings.xml");
 
 
DataRow[] drChildren;
DataRelation dr;
 
//access each node in the XML file using a DataRow
//in this example the nodes we want are in the settings table
foreach (DataRow drResult in ds.Tables["setting"].Rows)
{
    //access the elements of the XML file using the DataRow columns
    //we will just write them to Trace for now
    System.Diagnostics.Trace.Write(drResult["name"].ToString() + " " + drResult["value"].ToString());
 
    //declare the relationship
    dr = ds.Relations["setting_currentScope"];
 
    //although we only expect one row of data GetChildRows returns an array
    drChildren = drResult.GetChildRows(dr);
 
    //iterate thru the array of DataRows to access the values
    for (int i = 0; i < drChildren.Length; i++)
    {
        System.Diagnostics.Trace.Write(" " + drChildren[i]["scope"].ToString());
    }
    System.Diagnostics.Trace.WriteLine("");
}

The output from this code:

CurrentInstance 1 user
OutputDirectory \\SV-OCRMGR\mbrc\ocr\xmloutput application

Tags: , , , , , , , , , , , , , , ,

ASP.NET | ASP.NET | ASP.NET | C# | ASP.NET | C# | C# | XML | C# | XML | XML | XML

N-Tier Architecture using Datasets

by Admin 6. November 2009 23:03

In our posts on using Datasets, Using Strongly Typed Datasets and Adding Additional Queries to a Dataset we outlined a simple 2-tier architecture. This may be suitable for many applications, however it is common to move larger and more complicated applications to an N-tier architecture. To achieve this we can add Business Logic Layer in between our application (the actual aspx pages) and the Data Access Layer containing our Datasets.

To seperate out layers in the web site, create a new folder called BLL under the App_Code folder. The image below shows the structure of the App_Code folder, with the documents that will be created in this post.

In the BLL folder add a new Class called CustomerData

We now need to add a reference to our DAL Customers dataset and implement a few methods to make the data available for use. The code snippet is shown below.

    public class CustomerData
    {
        public CustomerData()
        {
        }
 
        public Customers.CustomersDataTable GetAllCustomers()
        {
            CustomersTableAdapter adp = new CustomersTableAdapter();
            return adp.GetData();
        }
 
        public Customers.CustomersRow GetCustomerByCustomerID(string ID)
        {
            CustomersTableAdapter adp = new CustomersTableAdapter();
            return GetFirstRow(adp.GetCustomersByID(ID));
        }
 
        private Customers.CustomersRow GetFirstRow(Customers.CustomersDataTable table)
        {
            Customers.CustomersRow row;
            row = table.Rows[0] as Customers.CustomersRow;
            return row;
        }
    }

Basically we want to create methods in our BLL class that provide access to each method in the DAL dataset. So in our case we create firstly a method that returns all customers, calling the GetData() method. Secondly, we create a method that returns a single row based on the CustomerID and using the GetCustomersByID method of the Customers dataset. A helper method called GetFirstRow is used to grab just the first row from the DataTable that is returned my the GetCustomersByID method.

Once the CustomerData class has been built, it is a relatively simple task to bind the GridView on out CustomerList.aspx page to the class. Choose Configure Data Source from the ObjectDataSource and select Sample.BusinessLogicLayer.CustomerData from the dropdown list, and click next. In the Choose a method dropdown list, select the appropriate method, in this case GetCustomerByCustomerID.

The Parameters remain unchanged so you can click Next and then Finish. Run the page to see the results.

Tags: ,

ASP.NET | C#

Adding Additional Queries to a Dataset

by Admin 6. November 2009 22:31

In our first post on 2-tier architecure Using Strongly Typed Datasets, we created a very simple dataset based on the Customers table in the Northwind database. We are now going to expand upon this simple dataset and add the abilty to return records based on parameters, such as CustomerID.

Opne the Customers.xsd Dataset we created in the previous post and right mouse click on the CustomersTableAdapter heading to open the popup menu. Select Add Query to launsh the TableAdapter Query Configuration Wizard.

  1. Choose Use SQL statements
  2. Choose the SELECT with returns rows option
  3. Type the following SQL statement and click next

SELECT CustomerID, CompanyName, Address, City, PostalCode
FROM Customers
WHERE CustomerID=@ID

Unselect the Fill a DataTable option and rename the Method GetCustomersByID, click next and finish.

 

Build the web site to cofirm it compiles and then open the CustomerList.aspx file we created in the first post. To utilise our new query that expects a CustomerID, we need to add some extra fields to the page. Add a textbox called txtCustomerID and a button called btnSearch.

Next we need to bind the GridView to the new datatable, so select Configure Data Source from the ObjectDataSource Tasks popup. click through until you get the the Define Data Methods page of the wizard. In the Choose a Method dropdown list you will see that we now have two methods available. Select the GetCustmersByID() method.

 

Next we need to define where the data for the parameter @ID will come from. In the Define Parameters screen there are a number of source, including QueryString and Session objects, as well as an option to select a control. We will be getting our information from the textbox called txtCustomerID, so go ahead and define the parameter as shown below. Although it's not manditory, it will be easier to assign a default customerID so that when the page loads the GridView will display one record. If we did not want to select a default value, we would have to handle binding via code, so that we did not attempt to bind the GridView to an empty datatable.

Run the page and check the results. You should see the GridView populated with the record for customer Alfreds Futterkiste.

 

If you enter a different CustomerID, for instance BOTTM and click the button, the page will pstback and display the record for Bottom-Dollar Markets.

Obviously this is a very limited search option, as we need to know the exact customerID to search for. With a little more tweaking, it would be easy to add wildcards to our query or to even add the ability to search on more than one field, so have fun.

Tags: ,

C# | ASP.NET | C#

Using Strongly Typed Datasets

by Admin 6. November 2009 21:30

Creating and using a strongly-typed Dataset in ASP.NET provides an effective 2-tier architecture. Using the Northwind SQL Server database, in this post we will create a simple Dataset for use with some data-bound controls on an ASP.NET webpage.

To get started, add a folder called DAL, short for Data Access Layer, under the App_Code folder in a standard ASP.NET Web Site. In this folder, create a new Dataset called Customers.

Visual Studio will create the Dataset and launch the TableAdapter Configuration Wizard to guide you through the process of setting up the Dataset.

  1. Select an existing Connection, or if you haven't got a connection to Northwind already saed, hit the New Connection button.
  2. Choose the Use SQL statements option
  3. Enter the following SQL statement, or click the Query Builder button to visually create a simple SELECT query.
SELECT CustomerID, CompanyName, Address, City, PostalCode
FROM Customers

Visual Studio creates the Dataset complete with a TableAdapter. Build your web site, to make sure it compiles.

To show how easy it is to use this Dataset, create a new blank page in your ASP.NET web site called CustomerList and drag in a GridView control from the Toolbox. From the GridView Tasks popup, select <New Data Source> from the Choose Data Source drop-down list. The Data Source Configuration Wizard will open prompting you to select the source of your data. Normally you probably selected Database to connect to a SQL Server database, but to use our new Customers Dataset we need to select Object.

From the list of objects, select the CustomerTableAdapter.

Next, the wizard will display all the available methods that return DataTables. In our case there is only one, called GetData.

That's all there is to do. Our GridView is now bound to the DataSet. Fire up the webpage and see the results.

Tags:

ASP.NET | C#

Loops

by CameronM 4. February 2009 19:26

Loops enable blocks of code to be run a number of times depending on the results of an expression, sometimes called the loop-termination criteria.

while

As the name suggests, a while loop continues to execute while the condition expression remains true.

// while loop
int number = 0;
 
while (number < 3)
{
    Console.WriteLine(number);
    number++;
}

do

Similar to a while loop, the do loop executes a code block while an expression remains true. Unlike a while loop, a do loop checks the condition after running the code. This means that a do loop will always run at least once.

// do loop
int number = 0;
 
do
{
    Console.WriteLine(number);
    number++;
} while (number < 3);

for

The for loop runs a block of code until an expression is false. It is useful when you know the number of times to iterate through the code, such as when iterating through an array.

The basic syntax is as follows:

for (initializer; condition; iterator)

initializer: the initial condition

condition: a Boolean expression to evaluate

iterator: defines what to do after each iteration

In the example below, we first create a string array and then iterate through the array. A couple of things to note, arrays are zero-based, so your initializer i needs to be set to 0. The maximum value to compare against in the condition will be the length of the array, minus 1. After each iteration we’ll increase i by one. This will enable us to iterate through every value in the array.

// for loop
string[] people = new string[] { "Dave""Peter""Frank" };
 
for (int i = 0; i < people.Length; i++)
{
    Console.WriteLine(people[i]);
}

foreach

The foreach statement iterates through a collection of values and executes a block of code each time. It is useful for dealing with arrays and Collections types, such as List<>.

// foreach loop
string[] people = new string[] { "Dave""Peter""Frank" };
 
foreach(var person in people)
{
    Console.WriteLine(person);
}

Tags: ,

C#

Conditional Blocks

by CameronM 3. February 2009 18:27

Conditional blocks or selection statements provide a mechanism for choosing between alternate blocks of code.

if-then

The most common of the selection statements is the if-then statement that runs different blocks of code depending on the results of a condition. Conditions can be any expression that returns a Boolean, such as comparing two numbers.

// using a single comparison condition
if(myVariable1 > 0)
{
    Console.WriteLine("Bigger than zero");
}
else
{
    Con

When the condition evaluates to true, the ‘then’ portion of the statement runs. Unlike VB, in C# you don’t need to explicitly use the Then keyword, anything directly after the condition (usually in the curly braces) is the then statement. I said usually in the curly braces, because if your then block consists of only one line of code, you can omit the braces.

// omit the curly braces {} for single lines of code
if(myVariable1 > 0)
    Console.WriteLine("Bigger than zero");

The condition can include more than one comparison using logical operators, such as && (and) or || (or)

// using the && (and) logical operator - both expressions must be true
if(myVariable1 == 0 && myVariable2 == 10)
{
    Console.WriteLine("Both are true");
}
 
// using the || (or) logical operator - either expression may be true
if (myVariable1 == 0 || myVariable2 != 10)
{
    Console.WriteLine("One value is true");
}

switch

The switch statement runs one of the number of code blocks based on the value of the switch expression. Each code block begins with a case keyword and a value to compare against the switch expression. If the case and switch expression match, the code in that section runs. Instead of each block of code being delineated by curly braces, a jump statement (usually break) is used to exit the switch. 

// compare the value of myVariable1 against 3 different case 
// the last is the catch-all or default that runs if neither
// of the previous sections offer a match
switch (myVariable1)
{
    case 0:
        Console.WriteLine("Equal to zero");
        break;
    case 1:
        Console.WriteLine("Equal to one");
        break;
    default:
        Console.WriteLine("Some other number");
        break;
}

Tags:

C#

Filter records in a DataSet/DataTable

by CameronM 10. June 2006 08:12

There are times when working with disconnected data that your DataSet can be quite large and you need some way of filtering the records you are working with, much as you would use a SELECT statement to filter the rows you want to return from a database. Thankfully the .NET framework contains a Filter mthods that can filter the records in a DataTable to achieve just such an outcome.

To illustrate this we will create a DataSet containing one DataTable and populate the DataTable with some dummy data. We won't be connecting to a database in this example, but as you know form previous posts, it is very easy to create your DataSets from SQL Server, Access or XML-based data sources.

    //create Dataset and DataTable

    DataSet ds = new DataSet();

    DataTable dt = new DataTable();

    dt.Columns.Add("FirstName", string.Empty.GetType());

    dt.Columns.Add("LastName", string.Empty.GetType());

 

    //create some dummy data

    DataRow dr = dt.NewRow();

    dr["FirstName"] = "Kim";

    dr["LastName"] = "Abercrombie";

    dt.Rows.Add(dr);

 

    dr = dt.NewRow();

    dr["FirstName"] = "Gustavo";

    dr["LastName"] = "Achlong";

    dt.Rows.Add(dr);

 

    dr = dt.NewRow();

    dr["FirstName"] = "Catherine";

    dr["LastName"] = "Abel";

    dt.Rows.Add(dr);

 

    dr = dt.NewRow();

    dr["FirstName"] = "Humberto";

    dr["LastName"] = "Acevedo";

    dt.Rows.Add(dr);

 

    dr = dt.NewRow();

    dr["FirstName"] = "Robert";

    dr["LastName"] = "O'Hara";

    dt.Rows.Add(dr);

 

    //add the DataTable to our DataSet

    ds.Tables.Add(dt);

Once we have created our dummy DataSet, we can now perform some filtering. The .Filter method of the DataTable object returns an array of DataRow objects.

    //perform some filtering

    string name = "O'Hara";

    DataRow[] filtered = ds.Tables[0].Select("LastName ='" + name.Replace("'", "''") + "'");

    if (filtered.Length > 0)

    {

        Console.WriteLine("Found " + filtered.Length + " records.");

    }

The filtering all happens with just one line of code, which is pretty straight forward. The only gotcha that you may encounter especially when dealing with names is the apostrophe. If you don't replace the single apostrophe with a double, the code will throw an error Syntax error: Missing operand after 'Hara' operator when trying to carry out the Filter method.

You can filter using a range of valid expressions, including using the LIKE operator. The following code returns two records.

    string name = "Ac";

    DataRow[] filtered = ds.Tables[0].Select("LastName Like'" + name.Replace("'", "''") + "%'");

Tags: ,

ASP.NET | C#

Read Excel 2000-2003 into Dataset

by CameronM 28. February 2006 07:22

There are times when you need to deal with data stored in an Excel spreadsheet directly, without first importing it into your database of choice (Access or SQL Server for instance).

One easy way is to use the OleDB Data Provider in NET to retrieve the data from Excel and load it into a DataSet. If you have been using Access 2000-2003 as a data source in your .NET projects then you may have already come across the OleDB Namespace. According to Microsoft, the OleDb Namespace provides a number of classes used to access OLE data sources.

The basic steps required to connect to Excel using OleDB is as follows:

  1. Create a connection
  2. Create a command (such as a SELECT) to retrieve the data you want
  3. Create a data adapter using this command
  4. Fill a dataset with the results

    public DataSet OpenExcel(string FileName)

    {

        //read excel file using OleDB and return results in a DataSet

 

        DataSet ds = new DataSet();

 

        //FileName contains the full filepath

        string connstr = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + FileName + ";Extended Properties=Excel 8.0";

        OleDbConnection conn = new OleDbConnection(connstr);

 

        //select everything from the Worksheet named Merge Data

        string strSQL = "SELECT * FROM [Merge Data$]";

 

        OleDbCommand cmd = new OleDbCommand(strSQL, conn);

 

        //use the command to fill out DataSet

        OleDbDataAdapter da = new OleDbDataAdapter(cmd);

        da.Fill(ds);

 

        return ds;

    }

In the code snippet above we are feeding in the full filename, including path into our function. The SELECT statement grabs everything from a Worksheet named Merge Data and returns the results in a DataSet.

Tags: , ,

ASP.NET | C#