mcs/class/System.Data.Linq/src/DbLinq/Data/Linq/Sugar/Implementation/QueryRunner.cs

   1 #region MIT license\r
   2 // \r
   3 // MIT license\r
   4 //\r
   5 // Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne\r
   6 // \r
   7 // Permission is hereby granted, free of charge, to any person obtaining a copy\r
   8 // of this software and associated documentation files (the "Software"), to deal\r
   9 // in the Software without restriction, including without limitation the rights\r
  10 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell\r
  11 // copies of the Software, and to permit persons to whom the Software is\r
  12 // furnished to do so, subject to the following conditions:\r
  13 // \r
  14 // The above copyright notice and this permission notice shall be included in\r
  15 // all copies or substantial portions of the Software.\r
  16 // \r
  17 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR\r
  18 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,\r
  19 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE\r
  20 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER\r
  21 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,\r
  22 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN\r
  23 // THE SOFTWARE.\r
  24 // \r
  25 #endregion\r
  26 \r
  27 using System;\r
  28 using System.Data;\r
  29 using System.Collections;\r
  30 using System.Collections.Generic;\r
  31 using System.Reflection;\r
  32 \r
  33 #if MONO_STRICT\r
  34 using System.Data.Linq.Sql;\r
  35 using System.Data.Linq.Sugar.Expressions;\r
  36 #else\r
  37 using DbLinq.Data.Linq.Sql;\r
  38 using DbLinq.Data.Linq.Sugar.Expressions;\r
  39 #endif\r
  40 \r
  41 using DbLinq.Data.Linq.Database;\r
  42 using DbLinq.Util;\r
  43 \r
  44 #if MONO_STRICT\r
  45 namespace System.Data.Linq.Sugar.Implementation\r
  46 #else\r
  47 namespace DbLinq.Data.Linq.Sugar.Implementation\r
  48 #endif\r
  49 {\r
  50     internal class QueryRunner : IQueryRunner\r
  51     {\r
  52         /// <summary>\r
  53         /// Enumerates all records return by SQL request\r
  54         /// </summary>\r
  55         /// <typeparam name="T"></typeparam>\r
  56         /// <param name="selectQuery"></param>\r
  57         /// <returns></returns>\r
  58         public virtual IEnumerable<T> Select<T>(SelectQuery selectQuery)\r
  59         {\r
  60             var rowObjectCreator = selectQuery.GetRowObjectCreator<T>();\r
  61             Console.WriteLine("# rowObjectCreator={0}", rowObjectCreator.Method);\r
  62             Console.WriteLine("# rowObjectCreator.Target={0}", rowObjectCreator.Target.GetType().FullName);\r
  63 \r
  64             // handle the special case where the query is empty, meaning we don't need the DB\r
  65             if (string.IsNullOrEmpty(selectQuery.Sql.ToString()))\r
  66             {\r
  67                 yield return rowObjectCreator(null, null);\r
  68                 yield break;\r
  69             }\r
  70 \r
  71             using (var dbCommand = selectQuery.GetCommand())\r
  72             {\r
  73 \r
  74                 // write query to log\r
  75                 selectQuery.DataContext.WriteLog(dbCommand.Command);\r
  76 \r
  77                 using (var reader = dbCommand.Command.ExecuteReader())\r
  78                 {\r
  79                     while (reader.Read())\r
  80                     {\r
  81                         // someone told me one day this could happen (in SQLite)\r
  82                         if (reader.FieldCount == 0)\r
  83                             continue;\r
  84 \r
  85                         var row = rowObjectCreator(reader, selectQuery.DataContext._MappingContext);\r
  86                         // the conditions to register and watch an entity are:\r
  87                         // - not null (can this happen?)\r
  88                         // - registered in the model\r
  89                         if (row != null && selectQuery.DataContext.Mapping.GetTable(row.GetType()) != null)\r
  90                         {\r
  91                             row = (T)selectQuery.DataContext.Register(row);\r
  92                         }\r
  93 \r
  94                         yield return row;\r
  95                     }\r
  96                 }\r
  97             }\r
  98         }\r
  99 \r
 100         /// <summary>\r
 101         /// Returns a unique row (common reference)\r
 102         /// </summary>\r
 103         /// <param name="row"></param>\r
 104         /// <param name="t"></param>\r
 105         /// <param name="dataContext"></param>\r
 106         /// <returns></returns>\r
 107         protected virtual object GetUniqueRow(object row, Type t, DataContext dataContext)\r
 108         {\r
 109             if (row != null && dataContext.Mapping.GetTable(row.GetType()) != null)\r
 110                 row = dataContext.Register(row);\r
 111             return row;\r
 112         }\r
 113 \r
 114         /// <summary>\r
 115         /// Returns a unique row (common reference)\r
 116         /// </summary>\r
 117         /// <param name="row"></param>\r
 118         /// <param name="dataContext"></param>\r
 119         /// <returns></returns>\r
 120         protected virtual T GetUniqueRow<T>(object row, DataContext dataContext)\r
 121         {\r
 122             return (T)GetUniqueRow(row, typeof(T), dataContext);\r
 123         }\r
 124 \r
 125         public virtual S SelectScalar<S>(SelectQuery selectQuery)\r
 126         {\r
 127             switch (selectQuery.ExecuteMethodName)\r
 128             {\r
 129                 case null: // some calls, like Count() generate SQL and the resulting projection method name is null (never initialized)\r
 130                     return SelectSingle<S>(selectQuery, false); // Single() for safety, but First() should work\r
 131                 case "First":\r
 132                     return SelectFirst<S>(selectQuery, false);\r
 133                 case "FirstOrDefault":\r
 134                     return SelectFirst<S>(selectQuery, true);\r
 135                 case "Single":\r
 136                     return SelectSingle<S>(selectQuery, false);\r
 137                 case "SingleOrDefault":\r
 138                     return SelectSingle<S>(selectQuery, true);\r
 139                 case "Last":\r
 140                     return SelectLast<S>(selectQuery, false);\r
 141             }\r
 142             throw Error.BadArgument("S0077: Unhandled method '{0}'", selectQuery.ExecuteMethodName);\r
 143         }\r
 144 \r
 145         /// <summary>\r
 146         /// Returns first item in query.\r
 147         /// If no row is found then if default allowed returns default(S), throws exception otherwise\r
 148         /// </summary>\r
 149         /// <typeparam name="S"></typeparam>\r
 150         /// <param name="selectQuery"></param>\r
 151         /// <param name="allowDefault"></param>\r
 152         /// <returns></returns>\r
 153         protected virtual S SelectFirst<S>(SelectQuery selectQuery, bool allowDefault)\r
 154         {\r
 155             foreach (var row in Select<S>(selectQuery))\r
 156                 return row;\r
 157             if (!allowDefault)\r
 158                 throw new InvalidOperationException();\r
 159             return default(S);\r
 160         }\r
 161 \r
 162         /// <summary>\r
 163         /// Returns single item in query\r
 164         /// If more than one item is found, throws an exception\r
 165         /// If no row is found then if default allowed returns default(S), throws exception otherwise\r
 166         /// </summary>\r
 167         /// <typeparam name="S"></typeparam>\r
 168         /// <param name="selectQuery"></param>\r
 169         /// <param name="allowDefault"></param>\r
 170         /// <returns></returns>\r
 171         protected virtual S SelectSingle<S>(SelectQuery selectQuery, bool allowDefault)\r
 172         {\r
 173             S firstRow = default(S);\r
 174             int rowCount = 0;\r
 175             foreach (var row in Select<S>(selectQuery))\r
 176             {\r
 177                 if (rowCount > 1)\r
 178                     throw new InvalidOperationException();\r
 179                 firstRow = row;\r
 180                 rowCount++;\r
 181             }\r
 182             if (!allowDefault && rowCount == 0)\r
 183                 throw new InvalidOperationException();\r
 184             return firstRow;\r
 185         }\r
 186 \r
 187         /// <summary>\r
 188         /// Returns last item in query\r
 189         /// </summary>\r
 190         /// <typeparam name="S"></typeparam>\r
 191         /// <param name="selectQuery"></param>\r
 192         /// <param name="allowDefault"></param>\r
 193         /// <returns></returns>\r
 194         protected virtual S SelectLast<S>(SelectQuery selectQuery, bool allowDefault)\r
 195         {\r
 196             S lastRow = default(S);\r
 197             int rowCount = 0;\r
 198             foreach (var row in Select<S>(selectQuery))\r
 199             {\r
 200                 lastRow = row;\r
 201                 rowCount++;\r
 202             }\r
 203             if (!allowDefault && rowCount == 0)\r
 204                 throw new InvalidOperationException();\r
 205             return lastRow;\r
 206         }\r
 207 \r
 208         /// <summary>\r
 209         /// Runs an InsertQuery on a provided object\r
 210         /// </summary>\r
 211         /// <param name="target"></param>\r
 212         /// <param name="insertQuery"></param>\r
 213         public void Insert(object target, UpsertQuery insertQuery)\r
 214         {\r
 215             Upsert(target, insertQuery);\r
 216         }\r
 217 \r
 218         private void Upsert(object target, UpsertQuery insertQuery)\r
 219         {\r
 220             insertQuery.Target = target;\r
 221             var dataContext = insertQuery.DataContext;\r
 222             using (var dbCommand = insertQuery.GetCommand())\r
 223             {\r
 224 \r
 225                 // log first command\r
 226                 dataContext.WriteLog(dbCommand.Command);\r
 227 \r
 228                 // we may have two commands\r
 229                 int rowsCount = dbCommand.Command.ExecuteNonQuery();\r
 230                 // the second reads output parameters\r
 231                 if (!string.IsNullOrEmpty(insertQuery.IdQuerySql.ToString()))\r
 232                 {\r
 233                     var outputCommand = dbCommand.Command.Connection.CreateCommand();\r
 234 \r
 235                     // then run commands\r
 236                     outputCommand.Transaction = dbCommand.Command.Transaction;\r
 237                     outputCommand.CommandText = insertQuery.IdQuerySql.ToString();\r
 238 \r
 239                     // log second command\r
 240                     dataContext.WriteLog(outputCommand);\r
 241 \r
 242                     using (var dataReader = outputCommand.ExecuteReader())\r
 243                     {\r
 244                         // TODO: check if this is needed\r
 245                         dataReader.Read();\r
 246 \r
 247                         for (int outputParameterIndex = 0;\r
 248                              outputParameterIndex < insertQuery.OutputParameters.Count;\r
 249                              outputParameterIndex++)\r
 250                         {\r
 251                             var outputParameter = insertQuery.OutputParameters[outputParameterIndex];\r
 252                             var outputDbParameter = dataReader.GetValue(outputParameterIndex);\r
 253                             SetOutputParameterValue(target, outputParameter, outputDbParameter);\r
 254                         }\r
 255                     }\r
 256                 }\r
 257                 dbCommand.Commit();\r
 258             }\r
 259         }\r
 260 \r
 261         protected virtual void SetOutputParameterValue(object target, ObjectOutputParameterExpression outputParameter, object value)\r
 262         {\r
 263             // depending on vendor, we can have DBNull or null\r
 264             // so we handle both\r
 265             if (value is DBNull || value == null)\r
 266                 outputParameter.SetValue(target, null);\r
 267             else\r
 268                 outputParameter.SetValue(target, TypeConvert.To(value, outputParameter.ValueType));\r
 269         }\r
 270 \r
 271         /// <summary>\r
 272         /// Performs an update\r
 273         /// </summary>\r
 274         /// <param name="target">Entity to be flushed</param>\r
 275         /// <param name="updateQuery">SQL update query</param>\r
 276         /// <param name="modifiedMembers">List of modified members, or null to update all members</param>\r
 277         public void Update(object target, UpsertQuery updateQuery, IList<MemberInfo> modifiedMembers)\r
 278         {\r
 279             Upsert(target, updateQuery);\r
 280         }\r
 281 \r
 282         /// <summary>\r
 283         /// Performs a delete\r
 284         /// </summary>\r
 285         /// <param name="target">Entity to be deleted</param>\r
 286         /// <param name="deleteQuery">SQL delete query</param>\r
 287         public void Delete(object target, DeleteQuery deleteQuery)\r
 288         {\r
 289             deleteQuery.Target = target;\r
 290             using (var dbCommand = deleteQuery.GetCommand())\r
 291             {\r
 292 \r
 293                 // log command\r
 294                 deleteQuery.DataContext.WriteLog(dbCommand.Command);\r
 295 \r
 296                 int rowsCount = dbCommand.Command.ExecuteNonQuery();\r
 297                 dbCommand.Commit();\r
 298             }\r
 299         }\r
 300 \r
 301         /// <summary>\r
 302         /// Fills dbCommand parameters, given names and values\r
 303         /// </summary>\r
 304         /// <param name="dbCommand"></param>\r
 305         /// <param name="parameterNames"></param>\r
 306         /// <param name="parameterValues"></param>\r
 307         private void FeedParameters(IDbCommand dbCommand, IList<string> parameterNames, IList<object> parameterValues)\r
 308         {\r
 309             for (int parameterIndex = 0; parameterIndex < parameterNames.Count; parameterIndex++)\r
 310             {\r
 311                 var dbParameter = dbCommand.CreateParameter();\r
 312                 dbParameter.ParameterName = parameterNames[parameterIndex];\r
 313                 dbParameter.SetValue(parameterValues[parameterIndex]);\r
 314                 dbCommand.Parameters.Add(dbParameter);\r
 315             }\r
 316         }\r
 317 \r
 318         /// <summary>\r
 319         /// Runs a direct scalar command\r
 320         /// </summary>\r
 321         /// <param name="directQuery"></param>\r
 322         /// <param name="parameters"></param>\r
 323         /// <returns></returns>\r
 324         public int Execute(DirectQuery directQuery, params object[] parameters)\r
 325         {\r
 326             directQuery.parameterValues = parameters;\r
 327             using (var dbCommand = directQuery.GetCommand())\r
 328             {\r
 329 \r
 330                 // log command\r
 331                 directQuery.DataContext.WriteLog(dbCommand.Command);\r
 332 \r
 333                 var result = dbCommand.Command.ExecuteScalar();\r
 334                 if (result == null || result is DBNull)\r
 335                     return 0;\r
 336                 var intResult = TypeConvert.ToNumber<int>(result);\r
 337                 return intResult;\r
 338             }\r
 339         }\r
 340 \r
 341         // TODO: move method?\r
 342         protected virtual Delegate GetTableBuilder(Type elementType, IDataReader dataReader, DataContext dataContext)\r
 343         {\r
 344             var fields = new List<string>();\r
 345             for (int fieldIndex = 0; fieldIndex < dataReader.FieldCount; fieldIndex++)\r
 346                 fields.Add(dataReader.GetName(fieldIndex));\r
 347             return dataContext.QueryBuilder.GetTableReader(elementType, fields, new QueryContext(dataContext));\r
 348         }\r
 349 \r
 350         /// <summary>\r
 351         /// Runs a query with a direct statement\r
 352         /// </summary>\r
 353         /// <param name="tableType"></param>\r
 354         /// <param name="directQuery"></param>\r
 355         /// <param name="parameters"></param>\r
 356         /// <returns></returns>\r
 357         public IEnumerable ExecuteSelect(Type tableType, DirectQuery directQuery, params object[] parameters)\r
 358         {\r
 359             directQuery.parameterValues = parameters;\r
 360             using (var dbCommand = directQuery.GetCommand())\r
 361             {\r
 362 \r
 363                 // log query\r
 364                 directQuery.DataContext.WriteLog(dbCommand.Command);\r
 365 \r
 366                 using (var dataReader = dbCommand.Command.ExecuteReader())\r
 367                 {\r
 368                     // Did you know? "return EnumerateResult(tableType, dataReader, dataContext);" disposes resources first\r
 369                     // before the enumerator is used\r
 370                     foreach (var result in EnumerateResult(tableType, dataReader, directQuery.DataContext))\r
 371                         yield return result;\r
 372                 }\r
 373             }\r
 374         }\r
 375 \r
 376         /// <summary>\r
 377         /// Enumerates results from a request.\r
 378         /// The result shape can change dynamically\r
 379         /// </summary>\r
 380         /// <param name="tableType"></param>\r
 381         /// <param name="dataReader"></param>\r
 382         /// <param name="dataContext"></param>\r
 383         /// <returns></returns>\r
 384         public IEnumerable EnumerateResult(Type tableType, IDataReader dataReader, DataContext dataContext)\r
 385         {\r
 386             return EnumerateResult(tableType, true, dataReader, dataContext);\r
 387         }\r
 388 \r
 389         /// <summary>\r
 390         /// Enumerates results from a request.\r
 391         /// The result shape can change dynamically\r
 392         /// </summary>\r
 393         /// <param name="tableType"></param>\r
 394         /// <param name="dynamicallyReadShape">Set True to change reader shape dynamically</param>\r
 395         /// <param name="dataReader"></param>\r
 396         /// <param name="dataContext"></param>\r
 397         /// <returns></returns>\r
 398         protected virtual IEnumerable EnumerateResult(Type tableType, bool dynamicallyReadShape, IDataReader dataReader, DataContext dataContext)\r
 399         {\r
 400             Delegate tableBuilder = null;\r
 401             while (dataReader.Read())\r
 402             {\r
 403                 if (tableBuilder == null || dynamicallyReadShape)\r
 404                     tableBuilder = GetTableBuilder(tableType, dataReader, dataContext);\r
 405                 var row = tableBuilder.DynamicInvoke(dataReader, dataContext._MappingContext);\r
 406                 row = GetUniqueRow(row, tableType, dataContext);\r
 407                 yield return row;\r
 408             }\r
 409         }\r
 410     }\r
 411 }\r