There are lots of information out there on ORM/ Microsoft Enterprise Library Framework (Data Access Application Block)/Generic Repository etc as well as lot of patterns (Active Record/DAO/TDG) on how to structure your code for large scale applications.
However for many small scale applications (especially Desktop Based Applications) you just want to use plain old ADO.net/ODP.net.With Managed ODP.net and Publishing on Visual Studio, its a breeze to deploy these apps on any client machines. (ofcourse they need to have .net framework installed).
Here is a very simple DataAccess Class that uses the Oracle.ManagedDataAccess that you can reuse across all your applications. It has two methods (and we can add more similar methods) one that establishes a connection and one that executes a select query that takes in a dictionary of string parameters. The second method returns a dataset which you can then read based on the needs of your application.If you want params of other types, you could make the dictionary<string,object>. Though there would be performance penalty on type conversion, you could live with that for smaller applications. Again, this is a very simplified version and you could extend it by using Generics thereby taking in any params (albeit all of them should be of same type !!)
public class DataAccess
{
public static OracleConnection Connect()
{
string connectionString = ConfigurationManager.ConnectionStrings["myDb"].ConnectionString;
OracleConnection connection = new OracleConnection(connectionString);
connection.Open();
return connection;
}
public string GetScalar(string selectSql,Dictionary<string,string> paramList)
{
using (var conn = DataAccess.Connect())
{
OracleCommand cmd=new OracleCommand(selectSql,conn);
cmd.BindByName = true;
cmd.Parameters.Clear();
try
{
foreach (var param in paramList)
{
cmd.Parameters.Add(param.Key, String.IsNullOrEmpty(param.Value) ? String.Empty : param.Value);
}
var colValue = cmd.ExecuteScalar();
if (colValue != null)
return colValue.ToString();
else
{
return "Empty";
}
}
catch (OracleException oex)
{
throw oex;
//return "Empty";
}
finally{
conn.Close();
conn.Dispose();
}
}
}
public DataSet DoSelect(string selectSql,Dictionary<string,string> parameters,string tableName)
{
DataSet dSet1=new DataSet();
DataTable dTable = new DataTable();
dTable.TableName = tableName;
using (var conn = DataAccess.Connect())
{
OracleCommand cmd = conn.CreateCommand();
cmd.BindByName = true;
cmd.CommandText = selectSql;
cmd.Parameters.Clear();
try
{
if (parameters != null)
{
foreach (var param in parameters)
{
cmd.Parameters.Add(param.Key, String.IsNullOrEmpty(param.Value) ? String.Empty : param.Value);
}
}
OracleDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
for (var i = 0; i <= rdr.FieldCount - 1; i++)
{
dTable.Columns.Add(new DataColumn(rdr.GetName(i), Type.GetType("System.String")));
}
while (rdr.Read())
{
var dr = dTable.NewRow();
for (var i = 0; i <= rdr.FieldCount - 1; i++)
{
dr[i] = rdr[i].ToString();
}
dTable.Rows.Add(dr);
}
rdr.Close();
dSet1.Tables.Add(dTable);
conn.Close();
conn.Dispose();
return dSet1;
}
else
{
return null;
}
}
catch (OracleException oex)
{
throw oex;
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
}
pravsdatums 