PageRenderTime 246ms CodeModel.GetById 141ms app.highlight 17ms RepoModel.GetById 84ms app.codeStats 0ms

/mcs/tools/sqlmetal/src/DbLinq.MySql/MySqlVendor.cs

http://github.com/mono/mono
C# | 291 lines | 258 code | 4 blank | 29 comment | 0 complexity | 0c729405bbe5a8d70ac74991fab159be MD5 | raw file
  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
 26using System;
 27using System.Data.Common;
 28using System.Linq;
 29using System.Collections.Generic;
 30using System.Text;
 31using System.Data.Linq.Mapping;
 32using System.Reflection;
 33using System.Data;
 34using DbLinq.Data.Linq;
 35using DbLinq.Data.Linq.SqlClient;
 36using DbLinq.Util;
 37using DbLinq.Vendor;
 38
 39#if MONO_STRICT
 40using System.Data.Linq;
 41#else
 42using DbLinq.Data.Linq;
 43#endif
 44
 45namespace 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>
 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
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>
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
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>
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}