I have a small utility .Net that was used to transform data from one database to another. Initially it was written using SQLHelper DAAB class. Then it was extended to work with OledbHelper , that was created based on SqlHelper. Now it was requested to work with ODBC provider as well.
I know, that I can use Enterprise library DAAB(e.g see here) , but I decided not involve additional DLLs and extra settings in config file for this simple application.
The static finction to return dataset for specified SQL and nominated provider is below:
public const string OleDBProviderInvariantName="System.Data.OleDb";
public const string OdbcProviderInvariantName="System.Data.Odbc";
public const string SQLProviderInvariantName = "System.Data.SqlClient";
//COnsider to use DAAB in Enterprize Library instead
//Provider name is case sensitive, such as "System.Data.OleDb","System.Data.Odbc","System.Data.SqlClient";
public static DataSet ExecuteDataset(string ProviderInvariantName, string sConnString, string sSQL)
{
DebugHelper.TracedLine("sConnString=" + sConnString);
DbProviderFactory fact = DbProviderFactories.GetFactory(ProviderInvariantName);
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = sConnString;
DebugHelper.TracedLine("(sSQL=" + sSQL);
DbCommand cmdSelect = conn.CreateCommand();
cmdSelect.CommandText = sSQL;
DbDataAdapter da = fact.CreateDataAdapter();
da.SelectCommand = cmdSelect;
DataSet ds = new DataSet();
try
{
da.Fill(ds);//Table
}
catch (Exception exc) //MNF 18/5/2005
{
throw new FSCSharpLib.ExceptionWithDetails(exc.Message, " sSQL = " + sSQL, exc);
}
return ds;
}