Developer forum

 There are no any way to get connection to different database with Database class.

You can use standard .NET providers to connect to another database: OleDb, SqlClient.

Best regards,
Sergey

 Ok, but if i use a standard .net sqlconnection object. I will be in trouble later in the process.

 

To make a sort explanation on what i am trying to do: We now have 12 sites wrapply growing whit 1 every month. And need to make statistics on users and orders on all sites.

 

We have created a complete report system, and it works perfectly on 1 site, but we need to create a custom module which can extract data from all 12 sites (12 x dynamicweb solutions + new onces as they come).

 

So we need to be able to use the dynamicweb api to grap users, order,orderline, products and so on from the various sites

 

E.g.

 

Dynamicweb.Database = ConnecToDB("ConnectstringtoDb1");

Dynamicweb.eCommerce.Orders.Order o = new Dynamicweb.eCommerce.Orders.Order("orderid");

 

Write.something to report from db1

 

Dynamicweb.Database = ConnecToDB(ConnectstringtoDb2");

Dynamicweb.eCommerce.Orders.Order o = new Dynamicweb.eCommerce.Orders.Order("orderid");

 

Write.something to report from db2

There are no any API function to get all information. There are two ways you can make it work:

1. Use a custom module to gather all needed information from all databases and create orders collections.
2. Gather all needed information on each solution and then your custom module will get it.

Your first result might be:

        public void SomeMethod()
        {
            SqlConnection con = new SqlConnection("server=sqlserver1;user=username;password=pass;database=db1");
            Dynamicweb.eCommerce.Orders.OrderCollection collection1 = GetOrderCollection("SELECT * FROM EcomOrders", con);

            con = new SqlConnection("server=sqlserver2;user=username;password=pass;database=db2");
            Dynamicweb.eCommerce.Orders.OrderCollection collection2 = GetOrderCollection("SELECT * FROM EcomOrders", con);

            //Todo: insert code here
        }

        public Dynamicweb.eCommerce.Orders.OrderCollection GetOrderCollection(string sql, System.Data.IDbConnection con)
        {
            Dynamicweb.eCommerce.Orders.OrderCollection col = new Dynamicweb.eCommerce.Orders.OrderCollection();
            Dynamicweb.eCommerce.Orders.Order objOrder;
            System.Data.IDataReader objDataReader = GetDataReader(sql, con);

            while (objDataReader.Read())
            {
                objOrder = new Dynamicweb.eCommerce.Orders.Order(objDataReader);
                col.Add(objOrder);
            }

            objDataReader.Close();
            objDataReader.Dispose();

            return col;
        }

        private System.Data.IDataReader GetDataReader(string sql, System.Data.IDbConnection con)
        {
            System.Data.IDbCommand objCommand = null;
            try
            {
                con.Open();
                objCommand = con.CreateCommand();
                objCommand.CommandText = sql;

                return objCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            }
            catch (System.Exception objException)
            {
                if ((objCommand != null))
                {
                    objCommand.Dispose();
                }
                if ((con != null))
                {
                    con.Close();
                    con.Dispose();
                }
                throw;
                //New Exception
            }
        }

If your're able to use the same SQL login on all databases, you can query them all by prefixing the table names in in your statment like this:

SELECT * FROM Database1.dbo.EcomOrders
SELECT * FROM Database2.dbo.EcomOrders

If you want one statement that fetches all records from all databasen at the same time, use the UNION operator:

(SELECT *FROM EcomOrders) -- current database
UNION
(SELECT * FROM Database1.dbo.EcomOrders)
UNION
(SELECT * FROM Database2.dbo.EcomOrders)

And so on.

Since all the hard work is done by the SQL server, you can easily use the DW API for this with no custom connections. You can perform a quick test in the SQL Firehose in Management Center to see how well it works on your solution.

Remember that when using UNION, the tables your're joining must have the exact same structure, so the solutions must either be on the same hotfix level, or you must specify the columns in the statement:

(SELECT OrderID, OrderCustomerName FROM EcomOrders)
UNION
(SELECT OrderID, OrderCustomerName FROM Database1.dbo.EcomOrders)
UNION
(SELECT OrderID, OrderCustomerName FROM Database2.dbo.EcomOrders)

One thing to note when joining tables across databases is collation. It the databases are set using different collations, use toe COLLATE function to streamline:

(SELECT TOP 5 OrderID FROM EcomOrders)UNION(SELECT TOP 5 OrderID COLLATE sql_latin1_general_cp1_ci_as FROM DetailWebshopStaging.dbo.Orders)

Depending on the frequenzy you need to do this, you may want to set up a data warehouse an push data to here from other sources on demand or scheduled basis in order to reduce load on production systems.

Hope this helps.

BR.
Lars