C# Data Set Method

In my C# program, I have several areas where I am connecting to an excel spreadsheet. To reduce repeat of coding, I am trying to write a method that reads in an sql statement, and then outputs a dataset.

How do I write this method?

Secondly, what should I be using to create such a module? A method, class, or something other?

This is what I have so far.

        //Connection String to read Excel File into Dataset.  
        if (Path.GetExtension(brtFile) == ".xlsx")  
        {  
            ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", brtFile);  
        }  
        else  
        {  
            ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +  
                                  "Data Source=" + brtFile + ";" +  
                                  "Extended Properties=Excel 8.0;";  
        }  

        //Read Excel file into Dataset.  
        OleDbConnection objConn = new OleDbConnection(ConnectionString);  
        try  
        {  
            objConn.Open();  
        }  
        catch (Exception e)  
        {  
            Console.WriteLine("{0} Exception caught.", e);  
            Console.ReadLine();  
        }  

        OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + xlWorksheet + "$]", objConn);  
        OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();  
        o开发者_JS百科bjAdapter1.SelectCommand = objCmdSelect;  
        DataSet objDataset1 = new DataSet();  
        objAdapter1.Fill(objDataset1);  
        objConn.Close();  

---

you can put this inside a class as either virtual or static public method, e.g

public class ExcelHelper
{
        public static DataSet GetExcelData()
        {
            //your codes here
            return objDataset1;
        }
}

you can call this like this

var xl = new ExcelHelper();
var ds = xl.GetExcelData();

or

var ds = new ExcelHelper().GetExcelData();

while if you create this as a static method like

public class ExcelHelper { 
    public static DataSet GetExcelData()
    {
        return objDataset1;
    }
}

you can call this as

var ds = ExcelHelper.GetExcelData();

---

The sample class below is the general pattern I use.

I create a "data-manager" class which handles all data access. I include static methods for "core" functionality, and I also allow the user to create an object with a specified connection string.

Points regarding the sample code:

1. Important to have using statements around every data class.
2. Add parameters before the call to Open makes for readable code.
3. I am purposely not using a data-adapter for this code in order to show that it is possible to create a DataSet and add data-tables as needed. (Note that using a data-adapter with a stored procedure that returns multiple result-sets fills a data-set with a data-table for each result-set.)
4. I am purposely not handling exception. Since this is a utility class, standard practice allows exceptions to bubble up to the caller.
5. This is sample code to show you want can be done and how I handle low-level data access. This code has not been tested, but this code-pattern I use all the time.

Sample Class

public class ExcelDataManager
{
    public string ConnectionString { get; set; }

    public ExcelDataManager(string connectionString)
    {
        this.ConnectionString = connectionString;
    }

    public DataSet LoadDataSet(string commandText, string dataSetName, string tableName)
    {
        return LoadDataSet(this.ConnectionString, commandText, dataSetName, tableName);
    }

    public static DataSet LoadDataSet(string connectionString, string commandText, string dataSetName, string tableName)
    {
        DataSet oResult = null;

        DataTable oDataTable = LoadDataTable(connectionString, commandText, tableName);

        if (oDataTable != null)
        {
            string sDataSetName = dataSetName;

            if (string.IsNullOrWhiteSpace(dataSetName))
            {
                sDataSetName = "DataSet1";
            }

            oResult = new DataSet(sDataSetName);

            oResult.Tables.Add(oDataTable);

            oResult.AcceptChanges();
        }

        return oResult;
    }

    public DataTable LoadDataTable(string commandText, string tableName)
    {
        return LoadDataTable(this.ConnectionString, commandText, tableName);
    }

    public static DataTable LoadDataTable(string connectionString, string commandText, string tableName)
    {
        DataTable oResult = null;

        using (OleDbConnection oConnection = new OleDbConnection(connectionString))
        {
            using (OleDbCommand oCommand = oConnection.CreateCommand())
            {
                oCommand.CommandType = CommandType.Text;
                oCommand.CommandText = commandText;

                oCommand.Connection.Open();

                using (OleDbDataReader oReader = oCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    if (oReader.HasRows)
                    {
                        // You need a table name if you call WriteXml.

                        string sTableName = tableName;

                        if (string.IsNullOrWhiteSpace(tableName))
                        {
                            sTableName = "Table1";
                        }

                        oResult = new DataTable(sTableName);

                        oResult.Load(oReader);

                        oResult.AcceptChanges();
                    }

                }

            }

        }

        return oResult;
    }

}