wvdotnet/wvdbi.cs

   1 using System;
   2 using System.Data;
   3 using System.Data.Common;
   4 using System.Data.Odbc;
   5 using System.Data.SqlTypes;
   6 using System.Data.SqlClient;
   7 using System.Collections.Generic;
   8 using System.Linq;
   9 using System.IO;
  10 using Wv;
  11 using Wv.Extensions;
  12
  13 namespace Wv
  14 {
  15     public abstract class WvDbi: IDisposable
  16     {
  17         static WvIni settings = new WvIni("wvodbc.ini");
  18         protected static WvLog log = new WvLog("WvDbi", WvLog.L.Debug1);
  19
  20         // MSSQL freaks out if there are more than 100 connections open at a
  21         // time.  Give ourselves a safety margin.
  22         static int num_active = 0;
  23         static int max_active = 50;
  24
  25         public static WvDbi create(string moniker)
  26         {
  27             log.print("Creating '{0}'\n", moniker);
  28
  29             if (!moniker.Contains(":") && settings[moniker].Count > 0)
  30             {
  31                 var sect = settings[moniker];
  32
  33                 if (sect["driver"] == "SqlClient")
  34                     return create(wv.fmt("mssql:"
  35                                          + "server={0};database={1};"
  36                                          + "User ID={2};Password={3};",
  37                                          sect["server"],
  38                                          sect["database"],
  39                                          sect["user"], sect["password"]));
  40                 else
  41                     return create(wv.fmt("ado:"
  42                                          + "driver={0};server={1};database={2};"
  43                                          + "uid={3};pwd={4};",
  44                                          sect["driver"], sect["server"],
  45                                          sect["database"],
  46                                          sect["user"], sect["password"]));
  47             }
  48
  49             if (moniker.StartsWith("dsn=") || moniker.StartsWith("driver="))
  50                 return create("ado:" + moniker);
  51
  52             WvDbi dbi = WvMoniker<WvDbi>.create(moniker);
  53             if (dbi == null)
  54                 throw new Exception
  55                            (wv.fmt("No moniker found for '{0}'", moniker));
  56             return dbi;
  57         }
  58
  59         protected WvDbi()
  60         {
  61             wv.assert(num_active < max_active,
  62                       "BUG: Too many open WvDbi connections");
  63             num_active++;
  64         }
  65
  66         ~WvDbi()
  67         {
  68             wv.assert(false, "A WvDbi object was not Dispose()d");
  69         }
  70
  71         // Implement IDisposable.
  72         public virtual void Dispose()
  73         {
  74             num_active--;
  75             GC.SuppressFinalize(this);
  76         }
  77
  78         public abstract WvSqlRows select(string sql, params object[] args);
  79         public abstract int execute(string sql, params object[] args);
  80
  81         public WvSqlRow select_onerow(string sql, params object[] args)
  82         {
  83             // only iterates a single row, if it exists
  84             foreach (WvSqlRow r in select(sql, args))
  85                 return r; // only return the first one
  86             return null;
  87         }
  88
  89         public WvAutoCast select_one(string sql, params object[] args)
  90         {
  91             var a = select_onerow(sql, args);
  92             if (a != null && a.Length > 0)
  93                 return a[0];
  94             else
  95                 return WvAutoCast._null;
  96         }
  97
  98         public int exec(string sql, params object[] args)
  99         {
 100             return execute(sql, args);
 101         }
 102
 103         public int try_execute(string sql, params object[] args)
 104         {
 105             try
 106             {
 107                 return execute(sql, args);
 108             }
 109             catch (DbException)
 110             {
 111                 // well, I guess no rows were affected...
 112                 return 0;
 113             }
 114         }
 115
 116         public int try_exec(string sql, params object[] args)
 117         {
 118             return try_execute(sql, args);
 119         }
 120     }
 121
 122     public class WvDbi_IDbConnection : WvDbi
 123     {
 124         IDbConnection _db;
 125         protected IDbConnection db
 126             { get { return _db; } }
 127
 128         public WvDbi_IDbConnection()
 129         {
 130         }
 131
 132         public override void Dispose()
 133         {
 134             if (db != null)
 135                 db.Dispose();
 136             _db = null;
 137             base.Dispose();
 138         }
 139
 140         protected void opendb(IDbConnection db)
 141         {
 142             this._db = db;
 143             if ((db.State & System.Data.ConnectionState.Open) == 0)
 144                 db.Open();
 145         }
 146
 147         public override WvSqlRows select(string sql, params object[] args)
 148         {
 149             return new WvSqlRows_IDataReader(prepare(sql, args));
 150         }
 151
 152         public override int execute(string sql, params object[] args)
 153         {
 154             return prepare(sql, args).ExecuteNonQuery();
 155         }
 156
 157         protected virtual IDbCommand prepare(string sql, params object[] args)
 158         {
 159             IDbCommand cmd = db.CreateCommand();
 160             cmd.CommandText = sql;
 161
 162             for (int i = 0; i < args.Length; i++)
 163             {
 164                 object a = args[i];
 165                 IDbDataParameter param;
 166                 if (cmd.Parameters.Count <= i)
 167                     cmd.Parameters.Add(param = cmd.CreateParameter());
 168                 else
 169                     param = (IDbDataParameter)cmd.Parameters[i];
 170                 if (a is DateTime)
 171                 {
 172                     param.DbType = DbType.DateTime;
 173                     param.Value = a;
 174                 }
 175                 else if (a is int)
 176                 {
 177                     param.DbType = DbType.Int32;
 178                     param.Value = a;
 179                 }
 180                 else
 181                 {
 182                     string s = a.ToString();
 183                     param.DbType = DbType.String; // I sure hope so...
 184                     param.Value = s;
 185                     param.Size = s.Length;
 186                 }
 187             }
 188
 189             return cmd;
 190         }
 191     }
 192
 193     [WvMoniker]
 194     public class WvDbi_ODBC : WvDbi_IDbConnection
 195     {
 196         public static void wvmoniker_register()
 197         {
 198             WvMoniker<WvDbi>.register("ado",
 199                  (string m, object o) => new WvDbi_ODBC(m));
 200             WvMoniker<WvDbi>.register("odbc",
 201                  (string m, object o) => new WvDbi_ODBC(m));
 202             WvMoniker<WvDbi>.register("mysql",
 203                  (string m, object o) => new WvDbi_ODBC("MySQL:" + m));
 204         }
 205
 206         public WvDbi_ODBC(string moniker)
 207         {
 208             string real;
 209             if (moniker.StartsWith("dsn=") || moniker.StartsWith("driver="))
 210                 real = moniker;
 211             else
 212             {
 213                 // try to parse it as an URL
 214                 WvUrl url = new WvUrl(moniker);
 215                 if (url.path.StartsWith("/"))
 216                     url.path = url.path.Substring(1);
 217                 if (url.method == "file") // method not provided
 218                     real = wv.fmt("dsn={0};database={1};"
 219                                   + "User ID={2};uid={2};Password={3};pwd={3}",
 220                                   url.host, url.path, url.user, url.password);
 221                 else
 222                     real = wv.fmt("driver={0};server={1};database={2};"
 223                                   + "User ID={3};uid={3};Password={4};pwd={4}",
 224                                   url.method, url.host, url.path,
 225                                   url.user, url.password);
 226             }
 227
 228             log.print("ODBC create: '{0}'\n", real);
 229             opendb(new OdbcConnection(real));
 230         }
 231
 232     }
 233
 234     [WvMoniker]
 235     public class WvDbi_MSSQL : WvDbi_IDbConnection
 236     {
 237         public static void wvmoniker_register()
 238         {
 239             WvMoniker<WvDbi>.register("mssql",
 240                  (string m, object o) => new WvDbi_MSSQL(m));
 241         }
 242
 243         public WvDbi_MSSQL(string moniker)
 244         {
 245             string real;
 246             if (!moniker.StartsWith("//"))
 247                 real = moniker;
 248             else
 249             {
 250                 // try to parse it as an URL
 251                 WvUrl url = new WvUrl(moniker);
 252                 if (url.path.StartsWith("/"))
 253                     url.path = url.path.Substring(1);
 254                 real = wv.fmt("server={0};database={1};"
 255                               + "User ID={2};Password={3};",
 256                               url.host, url.path, url.user, url.password);
 257             }
 258
 259             log.print("MSSQL create: '{0}'\n", real);
 260             opendb(new SqlConnection(real));
 261         }
 262
 263         protected override IDbCommand prepare(string sql, params object[] args)
 264         {
 265             SqlCommand cmd = (SqlCommand)db.CreateCommand();
 266             cmd.CommandText = sql;
 267
 268             for (int i = 0; i < args.Length; i++)
 269             {
 270                 object a = args[i];
 271                 if (cmd.Parameters.Count <= i)
 272                 {
 273                     string name = wv.fmt("@col{0}", i);
 274                     cmd.Parameters.Add(new SqlParameter(name, a));
 275                 }
 276                 cmd.Parameters[i].Value = a;
 277             }
 278
 279             return cmd;
 280         }
 281     }
 282 }
 283