public static void Fill(Domain domain)
    {
      con.Open();
      SqlTransaction transaction = con.BeginTransaction();
      SqlCommand cmd = con.CreateCommand();
      cmd.Transaction = transaction;
      cmd.CommandText = "Select * from [dbo].[Categories]";
      var reader = cmd.ExecuteReader();

      using (var session = domain.OpenSession())
      using (var tr = session.OpenTransaction())
      {
        #region  Categories

        var categories = new Dictionary<object, Category>();
        while (reader.Read())
        {
          var category = new Category(session);
          for (int i = 1; i < reader.FieldCount; i++)
            category[reader.GetName(i)] = !reader.IsDBNull(i) ? reader.GetValue(i) : null;
          categories.Add(reader.GetValue(0), category);
        }
        reader.Close();

        #endregion

        #region Customers

        var customers = new Dictionary<object, Customer>();
        cmd.CommandText = "Select * from [dbo].[Customers]";
        reader = cmd.ExecuteReader();
        while (reader.Read())
        {
          var customer = new Customer(session, reader.GetString(0));
          for (int i = 1; i < reader.FieldCount; i++)
            customer[reader.GetName(i)] = !reader.IsDBNull(i) ? reader.GetValue(i) : null;
          customers.Add(reader.GetValue(0), customer);
        }
        reader.Close();

        #endregion

        #region Regions

        var regions = new Dictionary<object, Region>();
        cmd.CommandText = "Select * from [dbo].[Region]";
        reader = cmd.ExecuteReader();
        while (reader.Read())
        {
          var region = new Region(session);
          for (int i = 1; i < reader.FieldCount; i++)
            region[reader.GetName(i)] = !reader.IsDBNull(i) ? reader.GetValue(i) : null;
          regions.Add(reader.GetValue(0), region);
        }
        reader.Close();

        #endregion

        #region Suppliers

        var suppliers = new Dictionary<object, Supplier>();
        cmd.CommandText = "Select * from [dbo].[Suppliers]";
        reader = cmd.ExecuteReader();
        while (reader.Read()) {
          var supplier = new Supplier(session);
          for (int i = 1; i < reader.FieldCount; i++)
            supplier[reader.GetName(i)] = !reader.IsDBNull(i) ? reader.GetValue(i) : null;
          suppliers.Add(reader.GetValue(0), supplier);
        }
        reader.Close();

        #endregion

        #region Shippers

        var shippers = new Dictionary<object, Shipper>();
        cmd.CommandText = "Select * from [dbo].[Shippers]";
        reader = cmd.ExecuteReader();
        while (reader.Read())
        {
          var shipper = new Shipper(session);
          for (int i = 1; i < reader.FieldCount; i++)
            shipper[reader.GetName(i)] = !reader.IsDBNull(i) ? reader.GetValue(i) : null;
          shippers.Add(reader.GetValue(0), shipper);
        }
        reader.Close();

        #endregion

        #region Products

        var products = new Dictionary<object, Product>();
        cmd.CommandText = "Select * from [dbo].[Products]";
        reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
        while (reader.Read())
        {
          var discontinuedColumnIndex = reader.GetOrdinal("Discontinued");
          Product product = reader.GetBoolean(discontinuedColumnIndex)
                              ? (Product) new DiscontinuedProduct(session)
                              : new ActiveProduct(session);
          for (int i = 1; i < reader.FieldCount; i++)
            switch (i)
            {
              case 2:
                product.Supplier = !reader.IsDBNull(i) ? suppliers[reader.GetValue(i)] : null;
                break;
              case 3:
                product.Category = !reader.IsDBNull(i) ? categories[reader.GetValue(i)] : null;
                break;
              default:
                if (i != discontinuedColumnIndex)
                  product[reader.GetName(i)] = !reader.IsDBNull(i) ? reader.GetValue(i) : null;
                break;
            }
          products.Add(reader.GetValue(0), product);
        }
        reader.Close();

        #endregion

        #region Employees

        var employees = new Dictionary<object, Employee>();
        cmd.CommandText = "Select * from [dbo].[Employees]";
        reader = cmd.ExecuteReader();
        while (reader.Read())
        {
          var employee = new Employee(session);
          for (int i = 1; i < reader.FieldCount; i++)
            if (i != 16)
              employee[reader.GetName(i)] = !reader.IsDBNull(i) ? reader.GetValue(i) : null;
          employees.Add(reader.GetValue(0), employee);
        }
        reader.Close();

        reader = cmd.ExecuteReader();
        while (reader.Read())
        {
          var employee = employees[reader.GetValue(0)];
          bool isNull = reader.IsDBNull(16);
          if (!isNull)
          {
            int employeeId = reader.GetInt32(16);
            var reportsTo = employees[employeeId];
            if (reportsTo == null)
              throw new NullReferenceException("Employee is null.");
            employee.ReportsTo = reportsTo;
          }
        }
        reader.Close();

        #endregion

        #region Territories

        var territories = new Dictionary<object, Territory>();
        cmd.CommandText = "Select * from [dbo].[Territories]";
        reader = cmd.ExecuteReader();
        while (reader.Read())
        {
          var territory = new Territory(session, reader.GetString(0));
          territory.TerritoryDescription = reader.GetString(1);
          territory.Region = regions[reader.GetValue(2)];
          territories.Add(reader.GetValue(0), territory);
        }
        reader.Close();

        #endregion

        #region EmployeeTerritories

        cmd.CommandText = "Select * from [dbo].[EmployeeTerritories]";
        reader = cmd.ExecuteReader();
        while (reader.Read())
        {
          var territory = territories[reader.GetString(1)];
          var employee = employees[reader.GetInt32(0)];
          if (employee == null)
            throw new NullReferenceException("Employee is null.");
          territory.Employees.Add(employee);
        }
        reader.Close();

        #endregion

        #region Orders

        var orders = new Dictionary<object, Order>();
        cmd.CommandText = "Select * from [dbo].[Orders]";
        reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
        while (reader.Read())
        {
          var order = new Order(session);
          for (int i = 1; i < reader.FieldCount; i++)
            switch (i)
            {
              case 1:
                order.Customer = !reader.IsDBNull(i) ? customers[reader.GetValue(i)] : null;
                break;
              case 2:
                order.Employee = !reader.IsDBNull(i) ? employees[reader.GetValue(i)] : null;
                break;
              case 6:
                order.ShipVia = !reader.IsDBNull(i) ? shippers[reader.GetValue(i)] : null;
                break;
              default:
                order[reader.GetName(i)] = !reader.IsDBNull(i) ? reader.GetValue(i) : null;
                break;
            }
          orders.Add(reader.GetValue(0), order);
        }
        reader.Close();

        #endregion

        #region OrderDetail

        cmd.CommandText = "Select * from [dbo].[Order Details]";
        reader = cmd.ExecuteReader();
        while (reader.Read()) {
          var order = orders[reader.GetValue(0)];
          var product = products[reader.GetValue(1)];
          var orderDetails = new OrderDetail(order, product);

          for (int i = 2; i < reader.FieldCount; i++)
            orderDetails[reader.GetName(i)] = !reader.IsDBNull(i) ? reader.GetValue(i) : null;
        }
        reader.Close();

        #endregion

        session.SaveChanges();
        tr.Complete();
      }
      transaction.Commit();
      con.Close();
    }
Beispiel #2
0
        public static void Fill(Domain domain)
        {
            con.Open();
            SqlTransaction transaction = con.BeginTransaction();
            SqlCommand     cmd         = con.CreateCommand();

            cmd.Transaction = transaction;
            cmd.CommandText = "Select * from [dbo].[Categories]";
            var reader = cmd.ExecuteReader();

            using (var session = domain.OpenSession())
                using (var tr = session.OpenTransaction())
                {
                    #region  Categories

                    var categories = new Dictionary <object, Category>();
                    while (reader.Read())
                    {
                        var category = new Category(session);
                        for (int i = 1; i < reader.FieldCount; i++)
                        {
                            category[reader.GetName(i)] = !reader.IsDBNull(i) ? reader.GetValue(i) : null;
                        }
                        categories.Add(reader.GetValue(0), category);
                    }
                    reader.Close();

                    #endregion

                    #region Customers

                    var customers = new Dictionary <object, Customer>();
                    cmd.CommandText = "Select * from [dbo].[Customers]";
                    reader          = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        var customer = new Customer(session, reader.GetString(0));
                        for (int i = 1; i < reader.FieldCount; i++)
                        {
                            customer[reader.GetName(i)] = !reader.IsDBNull(i) ? reader.GetValue(i) : null;
                        }
                        customers.Add(reader.GetValue(0), customer);
                    }
                    reader.Close();

                    #endregion

                    #region Regions

                    var regions = new Dictionary <object, Region>();
                    cmd.CommandText = "Select * from [dbo].[Region]";
                    reader          = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        var region = new Region(session);
                        for (int i = 1; i < reader.FieldCount; i++)
                        {
                            region[reader.GetName(i)] = !reader.IsDBNull(i) ? reader.GetValue(i) : null;
                        }
                        regions.Add(reader.GetValue(0), region);
                    }
                    reader.Close();

                    #endregion

                    #region Suppliers

                    var suppliers = new Dictionary <object, Supplier>();
                    cmd.CommandText = "Select * from [dbo].[Suppliers]";
                    reader          = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        var supplier = new Supplier(session);
                        for (int i = 1; i < reader.FieldCount; i++)
                        {
                            supplier[reader.GetName(i)] = !reader.IsDBNull(i) ? reader.GetValue(i) : null;
                        }
                        suppliers.Add(reader.GetValue(0), supplier);
                    }
                    reader.Close();

                    #endregion

                    #region Shippers

                    var shippers = new Dictionary <object, Shipper>();
                    cmd.CommandText = "Select * from [dbo].[Shippers]";
                    reader          = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        var shipper = new Shipper(session);
                        for (int i = 1; i < reader.FieldCount; i++)
                        {
                            shipper[reader.GetName(i)] = !reader.IsDBNull(i) ? reader.GetValue(i) : null;
                        }
                        shippers.Add(reader.GetValue(0), shipper);
                    }
                    reader.Close();

                    #endregion

                    #region Products

                    var products = new Dictionary <object, Product>();
                    cmd.CommandText = "Select * from [dbo].[Products]";
                    reader          = cmd.ExecuteReader(CommandBehavior.KeyInfo);
                    while (reader.Read())
                    {
                        var     discontinuedColumnIndex = reader.GetOrdinal("Discontinued");
                        Product product = reader.GetBoolean(discontinuedColumnIndex)
                              ? (Product) new DiscontinuedProduct(session)
                              : new ActiveProduct(session);
                        for (int i = 1; i < reader.FieldCount; i++)
                        {
                            switch (i)
                            {
                            case 2:
                                product.Supplier = !reader.IsDBNull(i) ? suppliers[reader.GetValue(i)] : null;
                                break;

                            case 3:
                                product.Category = !reader.IsDBNull(i) ? categories[reader.GetValue(i)] : null;
                                break;

                            default:
                                if (i != discontinuedColumnIndex)
                                {
                                    product[reader.GetName(i)] = !reader.IsDBNull(i) ? reader.GetValue(i) : null;
                                }
                                break;
                            }
                        }
                        products.Add(reader.GetValue(0), product);
                    }
                    reader.Close();

                    #endregion

                    #region Employees

                    var employees = new Dictionary <object, Employee>();
                    cmd.CommandText = "Select * from [dbo].[Employees]";
                    reader          = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        var employee = new Employee(session);
                        for (int i = 1; i < reader.FieldCount; i++)
                        {
                            if (i != 16)
                            {
                                employee[reader.GetName(i)] = !reader.IsDBNull(i) ? reader.GetValue(i) : null;
                            }
                        }
                        employees.Add(reader.GetValue(0), employee);
                    }
                    reader.Close();

                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        var  employee = employees[reader.GetValue(0)];
                        bool isNull   = reader.IsDBNull(16);
                        if (!isNull)
                        {
                            int employeeId = reader.GetInt32(16);
                            var reportsTo  = employees[employeeId];
                            if (reportsTo == null)
                            {
                                throw new NullReferenceException("Employee is null.");
                            }
                            employee.ReportsTo = reportsTo;
                        }
                    }
                    reader.Close();

                    #endregion

                    #region Territories

                    var territories = new Dictionary <object, Territory>();
                    cmd.CommandText = "Select * from [dbo].[Territories]";
                    reader          = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        var territory = new Territory(session, reader.GetString(0));
                        territory.TerritoryDescription = reader.GetString(1);
                        territory.Region = regions[reader.GetValue(2)];
                        territories.Add(reader.GetValue(0), territory);
                    }
                    reader.Close();

                    #endregion

                    #region EmployeeTerritories

                    cmd.CommandText = "Select * from [dbo].[EmployeeTerritories]";
                    reader          = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        var territory = territories[reader.GetString(1)];
                        var employee  = employees[reader.GetInt32(0)];
                        if (employee == null)
                        {
                            throw new NullReferenceException("Employee is null.");
                        }
                        territory.Employees.Add(employee);
                    }
                    reader.Close();

                    #endregion

                    #region Orders

                    var orders = new Dictionary <object, Order>();
                    cmd.CommandText = "Select * from [dbo].[Orders]";
                    reader          = cmd.ExecuteReader(CommandBehavior.KeyInfo);
                    while (reader.Read())
                    {
                        var order = new Order(session);
                        for (int i = 1; i < reader.FieldCount; i++)
                        {
                            switch (i)
                            {
                            case 1:
                                order.Customer = !reader.IsDBNull(i) ? customers[reader.GetValue(i)] : null;
                                break;

                            case 2:
                                order.Employee = !reader.IsDBNull(i) ? employees[reader.GetValue(i)] : null;
                                break;

                            case 6:
                                order.ShipVia = !reader.IsDBNull(i) ? shippers[reader.GetValue(i)] : null;
                                break;

                            default:
                                order[reader.GetName(i)] = !reader.IsDBNull(i) ? reader.GetValue(i) : null;
                                break;
                            }
                        }
                        orders.Add(reader.GetValue(0), order);
                    }
                    reader.Close();

                    #endregion

                    #region OrderDetail

                    cmd.CommandText = "Select * from [dbo].[Order Details]";
                    reader          = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        var order        = orders[reader.GetValue(0)];
                        var product      = products[reader.GetValue(1)];
                        var orderDetails = new OrderDetail(order, product);

                        for (int i = 2; i < reader.FieldCount; i++)
                        {
                            orderDetails[reader.GetName(i)] = !reader.IsDBNull(i) ? reader.GetValue(i) : null;
                        }
                    }
                    reader.Close();

                    #endregion

                    session.SaveChanges();
                    tr.Complete();
                }
            transaction.Commit();
            con.Close();
        }