Esempio n. 1
0
        void ProcessOneDataElem(DataTable table, ExpandoObject data)
        {
            var row   = table.NewRow();
            var dataD = data as IDictionary <string, object>;

            for (int c = 0; c < table.Columns.Count; c++)
            {
                Object rowVal;
                var    col = table.Columns[c];
                if (col.ColumnName.Contains("."))
                {
                    // complex value
                    if (GetComplexValue(data, col.ColumnName, out rowVal))
                    {
                        var dbVal = SqlExtensions.ConvertTo(rowVal, col.DataType);
                        row[col.ColumnName] = dbVal;
                    }
                }
                else if (dataD.TryGetValue(col.ColumnName, out rowVal))
                {
                    var dbVal = SqlExtensions.ConvertTo(rowVal, col.DataType);
                    row[col.ColumnName] = dbVal;
                }
            }
            table.Rows.Add(row);
        }
Esempio n. 2
0
        void SetParametersWithList <T>(SqlCommand cmd, Object prms, IEnumerable <T> list) where T : class
        {
            Type listType = typeof(T);
            Type prmsType = prms != null?prms.GetType() : null;

            var       props  = listType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
            var       propsD = new Dictionary <String, PropertyInfo>();
            DataTable dt     = new DataTable();

            foreach (var p in props)
            {
                var column = new DataColumn(p.Name, p.PropertyType);
                if (p.PropertyType == typeof(String))
                {
                    column.MaxLength = 32767;
                }
                dt.Columns.Add(column);
                propsD.Add(p.Name, p);
            }
            for (int i = 0; i < cmd.Parameters.Count; i++)
            {
                SqlParameter prm             = cmd.Parameters[i];
                var          simpleParamName = prm.ParameterName.Substring(1); // skip @
                if (prm.SqlDbType == SqlDbType.Structured)
                {
                    foreach (var itm in list)
                    {
                        var row = dt.NewRow();
                        for (int c = 0; c < dt.Columns.Count; c++)
                        {
                            var col    = dt.Columns[c];
                            var rowVal = propsD[col.ColumnName].GetValue(itm);
                            var dbVal  = SqlExtensions.ConvertTo(rowVal, col.DataType);
                            row[col.ColumnName] = dbVal;
                        }
                        dt.Rows.Add(row);
                    }
                    prm.Value = dt;
                    prm.RemoveDbName(); // remove first segment (database name)
                }
                else if (prmsType != null)
                {
                    // scalar parameter
                    var pi = prmsType.GetProperty(simpleParamName);
                    if (pi != null)
                    {
                        prm.Value = pi.GetValue(prms);
                    }
                }
            }
        }
Esempio n. 3
0
 public async Task <IList <T> > LoadListAsync <T>(String source, String command, Object prms) where T : class
 {
     using (var token = _host.Profiler.Start(ProfileAction.Sql, command))
     {
         Type retType = typeof(T);
         var  props   = retType.GetProperties();
         var  result  = new List <T>();
         using (var cnn = await GetConnectionAsync(source))
         {
             using (var cmd = cnn.CreateCommandSP(command))
             {
                 SqlExtensions.SetFromDynamic(cmd.Parameters, prms);
                 using (var rdr = await cmd.ExecuteReaderAsync())
                 {
                     var keyMap = new Dictionary <String, Int32>();
                     for (int c = 0; c < rdr.FieldCount; c++)
                     {
                         keyMap.Add(rdr.GetName(c), c);
                     }
                     while (await rdr.ReadAsync())
                     {
                         T     item = System.Activator.CreateInstance(retType) as T;
                         Int32 fieldIndex;
                         foreach (var p in props)
                         {
                             if (keyMap.TryGetValue(p.Name, out fieldIndex))
                             {
                                 var dbVal = rdr.GetValue(fieldIndex);
                                 if (dbVal == DBNull.Value)
                                 {
                                     dbVal = null;
                                 }
                                 p.SetValue(item, dbVal);
                             }
                         }
                         result.Add(item);
                     }
                 }
             }
         }
         return(result);
     }
 }
Esempio n. 4
0
        SqlParameter SetParametersFrom <T>(SqlCommand cmd, T element)
        {
            Type retType = typeof(T);
            var  props   = retType.GetProperties(BindingFlags.Public | BindingFlags.Instance);

            SqlCommandBuilder.DeriveParameters(cmd);
            var          sqlParams = cmd.Parameters;
            SqlParameter retParam  = null;

            if (cmd.Parameters.Contains(RET_PARAM_NAME))
            {
                retParam       = cmd.Parameters[RET_PARAM_NAME];
                retParam.Value = DBNull.Value;
            }
            foreach (var p in props)
            {
                var paramName = "@" + p.Name;
                if (sqlParams.Contains(paramName))
                {
                    var sqlParam = sqlParams[paramName];
                    var sqlVal   = p.GetValue(element);
                    if (sqlParam.SqlDbType == SqlDbType.VarBinary)
                    {
                        var stream = sqlVal as Stream;
                        if (stream == null)
                        {
                            throw new IndexOutOfRangeException("Stream expected");
                        }
                        sqlParam.Value = new SqlBytes(stream);
                    }
                    else
                    {
                        sqlParam.Value = SqlExtensions.ConvertTo(sqlVal, sqlParam.SqlDbType.ToType());
                    }
                }
            }
            return(retParam);
        }
Esempio n. 5
0
        internal void SetTableParameters(SqlCommand cmd, Object data, Object prms)
        {
            IDictionary <String, Object> scalarParams = SqlExtensions.GetParametersDictionary(prms);

            for (int i = 0; i < cmd.Parameters.Count; i++)
            {
                SqlParameter prm             = cmd.Parameters[i];
                var          simpleParamName = prm.ParameterName.Substring(1); /*skip @*/
                if (prm.SqlDbType == SqlDbType.Structured)
                {
                    Tuple <DataTable, String> table;
                    if (_tables.TryGetValue(prm.ParameterName, out table))
                    {
                        // table parameters (binging by object name)
                        FillDataTable(table.Item1, GetDataForSave(data as ExpandoObject, table.Item2 /*path*/));
                        prm.Value = table.Item1;
                        prm.RemoveDbName(); // remove first segment (database name)
                    }
                    else
                    {
                        throw new DataWriterException($"Parameter {simpleParamName} not found");
                    }
                }
                else if (prm.SqlDbType == SqlDbType.VarBinary)
                {
                    throw new NotImplementedException();
                }
                else
                {
                    Object paramVal;
                    // simple parameter
                    if (scalarParams != null && scalarParams.TryGetValue(simpleParamName, out paramVal))
                    {
                        prm.Value = SqlExtensions.Value2SqlValue(paramVal);
                    }
                }
            }
        }
Esempio n. 6
0
 public async Task <T> LoadAsync <T>(String source, String command, Object prms = null) where T : class
 {
     using (var p = _host.Profiler.Start(ProfileAction.Sql, command))
     {
         var helper = new LoadHelper <T>();
         using (var cnn = await GetConnectionAsync(source))
         {
             using (var cmd = cnn.CreateCommandSP(command))
             {
                 SqlExtensions.SetFromDynamic(cmd.Parameters, prms);
                 using (var rdr = await cmd.ExecuteReaderAsync())
                 {
                     helper.ProcessRecord(rdr);
                     if (await rdr.ReadAsync())
                     {
                         T result = helper.ProcessFields(rdr);
                         return(result);
                     }
                 }
             }
         }
     }
     return(null);
 }