1 #region MIT license 2 // 3 // MIT license 4 // 5 // Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne 6 // 7 // Permission is hereby granted, free of charge, to any person obtaining a copy 8 // of this software and associated documentation files (the "Software"), to deal 9 // in the Software without restriction, including without limitation the rights 10 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell 11 // copies of the Software, and to permit persons to whom the Software is 12 // furnished to do so, subject to the following conditions: 13 // 14 // The above copyright notice and this permission notice shall be included in 15 // all copies or substantial portions of the Software. 16 // 17 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR 18 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, 19 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE 20 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER 21 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, 22 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN 23 // THE SOFTWARE. 24 // 25 #endregion 26 using System; 27 using System.Data.Common; 28 using System.Linq; 29 using System.Collections.Generic; 30 using System.Text; 31 using System.Data.Linq.Mapping; 32 using System.Reflection; 33 using System.Data; 34 using DbLinq.Data.Linq; 35 using DbLinq.Data.Linq.SqlClient; 36 using DbLinq.Util; 37 using DbLinq.Vendor; 38 39 #if MONO_STRICT 40 using System.Data.Linq; 41 #else 42 using DbLinq.Data.Linq; 43 #endif 44 45 namespace DbLinq.MySql 46 { 47 [Vendor(typeof(MySqlProvider))] 48 #if !MONO_STRICT 49 public 50 #endif 51 class MySqlVendor : Vendor.Implementation.Vendor 52 { 53 public override string VendorName { get { return "MySQL"; } } 54 55 protected readonly MySqlSqlProvider sqlProvider = new MySqlSqlProvider(); 56 public override ISqlProvider SqlProvider { get { return sqlProvider; } } 57 58 /// <summary> 59 /// for large number of rows, we want to use BULK INSERT, 60 /// because it does not fill up the translation log. 61 /// This is enabled for tables where Vendor.UserBulkInsert[db.Table] is true. 62 /// </summary> BulkInsert(Table<T> table, List<T> rows, int pageSize, IDbTransaction transaction)63 public override void BulkInsert<T>(Table<T> table, List<T> rows, int pageSize, IDbTransaction transaction) 64 { 65 // name parameters we're going to insert 66 var members = new Dictionary<string, MemberInfo>(); 67 var tableName = table.Context.Mapping.GetTable(typeof(T)).TableName; 68 foreach (var dataMember in table.Context.Mapping.GetTable(typeof(T)).RowType.PersistentDataMembers) 69 { 70 members[dataMember.MappedName.Trim('"')] = dataMember.Member; 71 } 72 var columns = new List<string>(members.Keys); 73 74 //PC: this is a test: when no page size specified, we manage to use less than 100 parameters 75 if (pageSize == 0) 76 pageSize = 99 / columns.Count; 77 78 // performes INSERTs 79 int lineIndex = 1; 80 foreach (var page in Page.Paginate(rows, pageSize)) 81 { 82 var valuesLists = new List<IList<string>>(); 83 using (var command = transaction.Connection.CreateCommand()) 84 { 85 foreach (T row in page) 86 { 87 var values = new List<string>(); 88 foreach (var keyValue in members) 89 { 90 var parameter = command.CreateParameter(); 91 parameter.ParameterName = SqlProvider.GetParameterName(string.Format("{0}_{1}", keyValue.Key, lineIndex)); 92 parameter.SetValue(keyValue.Value.GetMemberValue(row)); 93 values.Add(parameter.ParameterName); 94 command.Parameters.Add(parameter); 95 } 96 lineIndex++; 97 valuesLists.Add(values); 98 } 99 command.CommandText = sqlProvider.GetBulkInsert(SqlProvider.GetTable(tableName), columns, 100 valuesLists); 101 var result = command.ExecuteNonQuery(); 102 } 103 } 104 } 105 106 #if OBSOLETE DoBulkInsert(Table<T> table, List<T> rows, IDbConnection connection)107 public override void DoBulkInsert<T>(Table<T> table, List<T> rows, IDbConnection connection) 108 { 109 int pageSize = UseBulkInsert[table]; 110 //ProjectionData projData = ProjectionData.FromReflectedType(typeof(T)); 111 ProjectionData projData = AttribHelper.GetProjectionData(typeof(T)); 112 TableAttribute tableAttrib = typeof(T).GetCustomAttributes(false).OfType<TableAttribute>().Single(); 113 114 //build "INSERT INTO products (ProductName, SupplierID, CategoryID, QuantityPerUnit)" 115 string header = "INSERT INTO " + tableAttrib.Name + " " + InsertClauseBuilder.InsertRowHeader(projData); 116 117 foreach (List<T> page in Page.Paginate(rows, pageSize)) 118 { 119 int numFieldsAdded = 0; 120 StringBuilder sbValues = new StringBuilder(" VALUES "); 121 List<IDbDataParameter> paramList = new List<IDbDataParameter>(); 122 123 IDbCommand cmd = connection.CreateCommand(); 124 125 //package up all fields in N rows: 126 string separator = ""; 127 foreach (T row in page) 128 { 129 //prepare values = "(?P1, ?P2, ?P3, ?P4)" 130 string values = 131 InsertClauseBuilder.InsertRowFields(this, cmd, row, projData, paramList, ref numFieldsAdded); 132 sbValues.Append(separator).Append(values); 133 separator = ", "; 134 } 135 136 string sql = header + sbValues; //'INSET t1 (field1) VALUES (11),(12)' 137 cmd.CommandText = sql; 138 paramList.ForEach(param => cmd.Parameters.Add(param)); 139 140 int result = cmd.ExecuteNonQuery(); 141 } 142 } 143 #endif 144 /// <summary> 145 /// call mysql stored proc or stored function, 146 /// optionally return DataSet, and collect return params. 147 /// </summary> ExecuteMethodCall(DataContext context, MethodInfo method , params object[] inputValues)148 public override System.Data.Linq.IExecuteResult ExecuteMethodCall(DataContext context, MethodInfo method 149 , params object[] inputValues) 150 { 151 if (method == null) 152 throw new ArgumentNullException("L56 Null 'method' parameter"); 153 154 //check to make sure there is exactly one [FunctionEx]? that's below. 155 //FunctionAttribute functionAttrib = GetFunctionAttribute(method); 156 var functionAttrib = context.Mapping.GetFunction(method); 157 158 ParameterInfo[] paramInfos = method.GetParameters(); 159 //int numRequiredParams = paramInfos.Count(p => p.IsIn || p.IsRetval); 160 //if (numRequiredParams != inputValues.Length) 161 // throw new ArgumentException("L161 Argument count mismatch"); 162 163 string sp_name = functionAttrib.MappedName; 164 165 // picrap: is there any way to abstract some part of this? 166 using (IDbCommand command = context.Connection.CreateCommand()) 167 { 168 command.CommandText = sp_name; 169 //MySqlCommand command = new MySqlCommand("select hello0()"); 170 int currInputIndex = 0; 171 172 List<string> paramNames = new List<string>(); 173 for (int i = 0; i < paramInfos.Length; i++) 174 { 175 ParameterInfo paramInfo = paramInfos[i]; 176 177 //TODO: check to make sure there is exactly one [Parameter]? 178 ParameterAttribute paramAttrib = paramInfo.GetCustomAttributes(false).OfType<ParameterAttribute>().Single(); 179 180 string paramName = "?" + paramAttrib.Name; //eg. '?param1' 181 paramNames.Add(paramName); 182 183 System.Data.ParameterDirection direction = GetDirection(paramInfo, paramAttrib); 184 //MySqlDbType dbType = MySqlTypeConversions.ParseType(paramAttrib.DbType); 185 IDbDataParameter cmdParam = command.CreateParameter(); 186 cmdParam.ParameterName = paramName; 187 //cmdParam.Direction = System.Data.ParameterDirection.Input; 188 if (direction == System.Data.ParameterDirection.Input || direction == System.Data.ParameterDirection.InputOutput) 189 { 190 object inputValue = inputValues[currInputIndex++]; 191 cmdParam.Value = inputValue; 192 } 193 else 194 { 195 cmdParam.Value = null; 196 } 197 cmdParam.Direction = direction; 198 command.Parameters.Add(cmdParam); 199 } 200 201 if (!functionAttrib.IsComposable) // IsCompsable is false when we have a procedure 202 { 203 //procedures: under the hood, this seems to prepend 'CALL ' 204 command.CommandType = System.Data.CommandType.StoredProcedure; 205 } 206 else 207 { 208 //functions: 'SELECT myFunction()' or 'SELECT hello(?s)' 209 string cmdText = "SELECT " + command.CommandText + "($args)"; 210 cmdText = cmdText.Replace("$args", string.Join(",", paramNames.ToArray())); 211 command.CommandText = cmdText; 212 } 213 214 if (method.ReturnType == typeof(DataSet)) 215 { 216 //unknown shape of resultset: 217 System.Data.DataSet dataSet = new DataSet(); 218 //IDataAdapter adapter = new MySqlDataAdapter((MySqlCommand)command); 219 IDbDataAdapter adapter = CreateDataAdapter(context); 220 adapter.SelectCommand = command; 221 adapter.Fill(dataSet); 222 List<object> outParamValues = CopyOutParams(paramInfos, command.Parameters); 223 return new ProcedureResult(dataSet, outParamValues.ToArray()); 224 } 225 else 226 { 227 object obj = command.ExecuteScalar(); 228 List<object> outParamValues = CopyOutParams(paramInfos, command.Parameters); 229 return new ProcedureResult(obj, outParamValues.ToArray()); 230 } 231 } 232 } 233 GetDirection(ParameterInfo paramInfo, ParameterAttribute paramAttrib)234 static System.Data.ParameterDirection GetDirection(ParameterInfo paramInfo, ParameterAttribute paramAttrib) 235 { 236 //strange hack to determine what's a ref, out parameter: 237 //http://lists.ximian.com/pipermain/mono-list/2003-March/012751.html 238 bool hasAmpersand = paramInfo.ParameterType.FullName.Contains('&'); 239 if (paramInfo.IsOut) 240 return System.Data.ParameterDirection.Output; 241 if (hasAmpersand) 242 return System.Data.ParameterDirection.InputOutput; 243 return System.Data.ParameterDirection.Input; 244 } 245 246 /// <summary> 247 /// Collect all Out or InOut param values, casting them to the correct .net type. 248 /// </summary> CopyOutParams(ParameterInfo[] paramInfos, IDataParameterCollection paramSet)249 private List<object> CopyOutParams(ParameterInfo[] paramInfos, IDataParameterCollection paramSet) 250 { 251 List<object> outParamValues = new List<object>(); 252 //Type type_t = typeof(T); 253 int i = -1; 254 foreach (IDbDataParameter param in paramSet) 255 { 256 i++; 257 if (param.Direction == System.Data.ParameterDirection.Input) 258 { 259 outParamValues.Add("unused"); 260 continue; 261 } 262 263 object val = param.Value; 264 Type desired_type = paramInfos[i].ParameterType; 265 266 if (desired_type.Name.EndsWith("&")) 267 { 268 //for ref and out parameters, we need to tweak ref types, e.g. 269 // "System.Int32&, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" 270 string fullName1 = desired_type.AssemblyQualifiedName; 271 string fullName2 = fullName1.Replace("&", ""); 272 desired_type = Type.GetType(fullName2); 273 } 274 try 275 { 276 //fi.SetValue(t, val); //fails with 'System.Decimal cannot be converted to Int32' 277 //DbLinq.util.FieldUtils.SetObjectIdField(t, fi, val); 278 //object val2 = FieldUtils.CastValue(val, desired_type); 279 object val2 = TypeConvert.To(val, desired_type); 280 outParamValues.Add(val2); 281 } 282 catch (Exception) 283 { 284 //fails with 'System.Decimal cannot be converted to Int32' 285 //Logger.Write(Level.Error, "CopyOutParams ERROR L245: failed on CastValue(): " + ex.Message); 286 } 287 } 288 return outParamValues; 289 } 290 } 291 } 292