コード例 #1
0
ファイル: Db.cs プロジェクト: eugene-tt/Code-Samples
        private static async Task runSprocWithTran <T>(
            string sprocName,
            dynamic parameters,
            NpgsqlTransaction tran,
            List <T> results,
            int max_rows = -1) where T : BaseModel, new()
        {
            T    resObj     = new T();
            Type resObjType = resObj.GetType();

            // Run the requested sproc
            using (var cmd = new NpgsqlCommand(sprocName, tran.Connection, tran))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                addParamsBinding(cmd, parameters);

                using (var reader = await CallSprocWithCursor(cmd))
                {
                    if (reader != null)
                    {
                        while (await reader.ReadAsync())
                        {
                            T pItem = new T();

                            for (int iField = 0; iField < reader.FieldCount; iField++)
                            {
                                var fieldName = reader.GetName(iField);
                                var dbValue   = reader[iField];
                                var property  = resObjType.GetProperty(fieldName);

                                if (dbValue == DBNull.Value)
                                {
                                    continue;
                                }

                                if (property != null)
                                {
                                    var value = dbValue;
                                    if (property.PropertyType == typeof(HtmlString))
                                    {
                                        value = (HtmlString)dbValue.ToString();
                                    }
                                    else
                                    if (property.PropertyType == typeof(JsonString))
                                    {
                                        value = (JsonString)dbValue.ToString();
                                    }
                                    else
                                    if (property.PropertyType == typeof(HashIdString))
                                    {
                                        value = new HashIdString((long)dbValue);
                                    }

                                    property.SetValue(pItem, value);
                                }
                                else
                                {
                                    // Place all the unknown fields' values into the aux of BaseModel
                                    pItem.aux.Add(new FieldValue {
                                        name = fieldName, value = dbValue.ToString()
                                    });
                                }
                            }

                            results.Add(pItem);

                            if (max_rows >= 0 && results.Count > max_rows)
                            {
                                break;
                            }
                        }
                    }
                }
            }

            if (ContextHelper.GetContext().HasTransaction)
            {
                try
                {
                    using (var cmd = new NpgsqlCommand("CLOSE ALL;", tran.Connection, tran))
                    {
                        cmd.CommandType = CommandType.Text;
                        await cmd.ExecuteNonQueryAsync();
                    }
                }
                catch
                {
                    ; // Ignore
                }
            }
        }
コード例 #2
0
ファイル: Db.cs プロジェクト: eugene-tt/Code-Samples
        private static async Task runSQL <T>(
            string sql,
            dynamic parameters,
            NpgsqlConnection conn,
            List <T> results,
            int max_rows = -1) where T : new()
        {
            T    resObj     = new T();
            Type resObjType = resObj.GetType();

            using (var tran = conn.BeginTransaction())
            {
                try
                {
                    // Add current user id and company
                    var ctx = ContextHelper.GetContext();

                    using (var cmd = new NpgsqlCommand(sql, conn, tran))
                    {
                        addParamsBinding(cmd, parameters);

                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            if (reader != null)
                            {
                                while (await reader.ReadAsync())
                                {
                                    T pItem = new T();

                                    for (int iField = 0; iField < reader.FieldCount; iField++)
                                    {
                                        var fieldName = reader.GetName(iField);
                                        var dbValue   = reader[iField];
                                        var property  = resObjType.GetProperty(fieldName);

                                        if (dbValue == DBNull.Value)
                                        {
                                            continue;
                                        }

                                        if (property != null)
                                        {
                                            var value = dbValue;
                                            if (property.PropertyType == typeof(HtmlString))
                                            {
                                                value = (HtmlString)dbValue.ToString();
                                            }
                                            else
                                            if (property.PropertyType == typeof(JsonString))
                                            {
                                                value = (JsonString)dbValue.ToString();
                                            }
                                            else
                                            if (property.PropertyType == typeof(HashIdString))
                                            {
                                                value = new HashIdString((long)dbValue);
                                            }

                                            property.SetValue(pItem, value);
                                        }
                                    }

                                    results.Add(pItem);

                                    if (max_rows >= 0 && results.Count > max_rows)
                                    {
                                        break;
                                    }
                                }
                            }
                        }
                        await tran.CommitAsync();
                    }
                }
                catch
                {
                    await tran.RollbackAsync();

                    throw;
                }
            }
        }