StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The sample class below is the general pattern I use.</p> <p>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.</p> <p>Points regarding the sample code:</p> <ol> <li>Important to have <code>using</code> statements around every data class.</li> <li>Add parameters before the call to <code>Open</code> makes for readable code.</li> <li>I am purposely not using a data-adapter for this code in order to show that it is possible to create a <code>DataSet</code> 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.)</li> <li>I am purposely not handling exception. Since this is a utility class, standard practice allows exceptions to bubble up to the caller.</li> <li>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.</li> </ol> <h3>Sample Class</h3> <pre><code>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; } } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload