Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>To roll one of your own isn't that big a deal. Here is a basic structure of how I would implement it for bare minimum needs (you can of course expand it):</p> <p>1) First create an interface specifying the basic functionalities.</p> <pre><code>interface IDb { IEnumerable&lt;T&gt; Get&lt;T&gt;(string query, Action&lt;IDbCommand&gt; parameterizer, Func&lt;IDataRecord, T&gt; selector); int Add(string query, Action&lt;IDbCommand&gt; parameterizer); int Save(string query, Action&lt;IDbCommand&gt; parameterizer); int SaveSafely(string query, Action&lt;IDbCommand&gt; parameterizer); } </code></pre> <p>2) Create the generic helper class which should not only implements the interface but also should be specified by the type <code>IDbConnection</code>. The class should be better (not necessarily) instantiable (not static) so that you can pass the required connection string to instantiate it. </p> <p>Here is a fully lazy implementation:</p> <pre><code>using System; using System.Data; using System.Collections.Generic; using System.Linq; public class Db&lt;T&gt; : IDb where T : IDbConnection, new() { string connectionString; public Db(string connectionString) { this.connectionString = connectionString; } IEnumerable&lt;S&gt; Do&lt;R, S&gt;(string query, Action&lt;IDbCommand&gt; parameterizer, Func&lt;IDbCommand, IEnumerable&lt;R&gt;&gt; actor, Func&lt;R, S&gt; selector) { using (var conn = new T()) { using (var cmd = conn.CreateCommand()) { if (parameterizer != null) parameterizer(cmd); cmd.CommandText = query; cmd.Connection.ConnectionString = connectionString; cmd.Connection.Open(); foreach (var item in actor(cmd)) yield return selector(item); } } } public IEnumerable&lt;S&gt; Get&lt;S&gt;(string query, Action&lt;IDbCommand&gt; parameterizer, Func&lt;IDataRecord, S&gt; selector) { return Do(query, parameterizer, ExecuteReader, selector); } static IEnumerable&lt;IDataRecord&gt; ExecuteReader(IDbCommand cmd) { using (var r = cmd.ExecuteReader(CommandBehavior.CloseConnection)) while (r.Read()) yield return r; } public int Add(string query, Action&lt;IDbCommand&gt; parameterizer) { return Do(query, parameterizer, ExecuteReader, r =&gt; Convert.ToInt32(r[0])).First(); } public int Save(string query, Action&lt;IDbCommand&gt; parameterizer) { return Do(query, parameterizer, ExecuteNonQuery, noAffected =&gt; noAffected).First(); } static IEnumerable&lt;int&gt; ExecuteNonQuery(IDbCommand cmd) { yield return cmd.ExecuteNonQuery(); } public int SaveSafely(string query, Action&lt;IDbCommand&gt; parameterizer) { // 'using' clause ensures rollback is called, so no need to explicitly rollback return Do(query, parameterizer, cmd =&gt; { using (cmd.Transaction = cmd.Connection.BeginTransaction()) { var noAffected = ExecuteNonQuery(cmd); cmd.Transaction.Commit(); return noAffected; } }, noAffected =&gt; noAffected).First(); } } </code></pre> <p>This only does the basic <code>ExecuteNonQuery</code> and <code>ExecuteReader</code> like operations, and simple <code>Transaction</code>s. No stored procedures. The <code>Add</code> function works for inserting and retrieving the last inserted id and likes. It was crazy of me to have made things lazy and to have used just one core execution function <code>Do</code> (which is called for various db actions), and that is why <code>Do</code> looks complicated, but its very DRY. Ideally its better to be separated. You can rid of <code>Linq</code> too.</p> <p>3) Lastly provide static wrapper <code>Db</code> with no generic constraints around the instantiable <code>Db</code> class so that you don't have to keep passing the <code>T</code> parameter every time to do a db query. For instance like this:</p> <pre><code>public static class Db { static IDb db = GetDbInstance(); static IDb GetDbInstance() { // get these two from config file or somewhere var connectionString = GetConnectionString(); var driver = GetDbType(); // your logic to decide which db is being used // some sort of estimation of your db if (driver == SQLite) return new Db&lt;SQLiteConnection&gt;(connectionString); else if (driver == MySQL) return new Db&lt;MySqlConnection&gt;(connectionString); else if (driver == JET) return new Db&lt;OleDbConnection&gt;(connectionString); //etc return null; } public static void Parameterize(this IDbCommand command, string name, object value) { var parameter = command.CreateParameter(); parameter.ParameterName = name; parameter.Value = value; command.Parameters.Add(parameter); } public static IEnumerable&lt;T&gt; Get&lt;T&gt;(string query, Action&lt;IDbCommand&gt; parameterizer, Func&lt;IDataRecord, T&gt; selector) { return db.Get(query, parameterizer, selector); } public static int Add(string query, Action&lt;IDbCommand&gt; parameterizer) { return db.Add(query, parameterizer); } public static int Save(string query, Action&lt;IDbCommand&gt; parameterizer) { return db.Save(query, parameterizer); } public static int SaveSafely(string query, Action&lt;IDbCommand&gt; parameterizer) { return db.SaveSafely(query, parameterizer); } } </code></pre> <p>4) Now I would create an additional static function <code>GetDbInstance</code> somewhere so that it infers the right database parameters like connection string, provider type etc. Also have an extension method to ease parameterization of queries. I put both of them in the above static <code>Db</code> class but that's your choice (some people write it in the Db class itself but I prefer it outside because the functionality should be your application's).</p> <p>5) Take care to have neutral queries that work on the databases you prefer.</p> <p><em>Or</em></p> <p>You can utilize <a href="http://msdn.microsoft.com/en-us/library/system.data.common.dbproviderfactory.aspx" rel="nofollow">DbProviderFactory</a> under <code>System.Data.Common</code> to detect the type of <code>DbConnection</code>/provider you have. You can have just one non-generic <code>Db</code> class and do:</p> <pre><code>public class Db { string connectionString; DbProviderFactory factory; public Db(string driver, string connectionString) { this.factory = DbProviderFactories.GetFactory(driver); this.connectionString = connectionString; } //and your core function would look like IEnumerable&lt;S&gt; Do&lt;R, S&gt;(string query, Action&lt;IDbCommand&gt; parameterizer, Func&lt;IDbCommand, IEnumerable&lt;R&gt;&gt; actor, Func&lt;R, S&gt; selector) { using (var conn = factory.CreateConnection()) { // and all the remaining code.. } } } </code></pre> <p>Your <code>GetDbInstance</code> method would look like: </p> <pre><code>static IDb GetDbInstance() { string connectionString = GetConnectionString(); string driver = GetDriver(); return Db(driver, connectionString); } </code></pre> <p>Pro: You get rid of the <code>if-else</code> style of programming and the right version of <code>Db</code> class will be instantiated depending on the provider and connection string in the config file.</p> <p>Con: You need to specify the right provider/driver in the configuration file.</p> <hr> <p>A sample query from your C# code would look like:</p> <pre><code>string query = "SELECT * FROM User WHERE id=@id AND savedStatus=@savedStatus"; var users = Db.Get(sql, cmd =&gt; { cmd.Parameterize("id", 1); cmd.Parameterize("savedStatus", true); }, selector).ToArray(); </code></pre> <p>All you have to do is call <code>Db.Get</code>, <code>Db.Save</code> etc. The function <code>GetDbInstance</code> is the key here which finds the functions in the right dlls to be called, and the helper class manages the resources well while additionally doing its task of various db operations. Such a class would avoid the hassle of opening and closing connections, freeing resources, having to include database dll namespace etc every time. This is what is called <a href="http://en.wikipedia.org/wiki/Database_abstraction_layer" rel="nofollow">DbAL</a>. You can have an <a href="http://en.wikipedia.org/wiki/Data_access_layer" rel="nofollow">additional layer</a> to help DbAL communicate between various strongly typed model classes as well. I simply love the power of polymorphism via interfaces and constraints which is very very OOP! :)</p>
    singulars
    1. This table or related slice is empty.
    1. POWriting driver class generic for any database support
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USmarc_s
    1. USnawfal
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUndeletion
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COya thanks will check and update u
      singulars
      1. PO
      1. USGowthamanSS
    2. COthanks for your update
      singulars
      1. PO
      1. USGowthamanSS
    3. CO@GowthamanSS I am curious to know if that helped you or not. I just wrote this out from wild imagination :) I must say I often have not found good helper classes that worked across databases..
      singulars
      1. PO
      1. USnawfal
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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