Update more than one database using same ObjectDataSource using C#

Question

Is there a way to update more than one Database having same schema using single ObjectDataSource in C#???

i.e Just by providing more than one connection string is it some how possible to update more than one Database? I need to update/insert same record in multiple Database with same schema using ObjectDataSource in C#.

Answer 1

I would say no; I don't think that's possible as a SqlCommand must be associated with exactly one connection string.

Since you're using an ObjectDataSource, why not write a "midde-tier" object that handles it? Typically, I'll have a class called something like CustomerManager that exposes all the methods required by the ObjectDataSource for CRUD operations.

Suppose you're updating a Customer's address, and need to touch on another database: your Update(...) method in your CustomerManager class then can call both databases inside of the method. This nice thing about this approach is that you can even wrap everything inside of a TransactionScope block and if you are updating more than one database, .NET will automatically "upsize" the transaction to a distributed transaction.

I hope that makes enough sense to get you going in the right direction!

Answer 2

Yes you can do it, since with an ObjectDataSource YOU ae the one writing the code that does the insert. Inside your "Update" and "Delete" methods you can simply perform two database actions, one for each database that you are working with. You can abstract this out to an operation that could be passed a connection to ensure that you don't have duplicate code sitting everywhere.

NOTE You CANNOT do this thought via a single connection, you must do two fully separate database actions.

Example

Per the comment there was a request for more detail.

Basically inside each of your methods, simply do two db calls, a crude AND NOT properly formed example to show the concept is below, for a "delete" method.

public void DeleteMyObject(int objectId)
{
    SqlConnection connectionOne = new SqlConnection("MyFirstDbConnection");
    SqlConnection connedtionTwo = new SqlConnection("MySecondDbCOnnection");
    SqlCommand myCommand = new SqlCommand(connectionOne);
    myCommand.CommandText = "DELETE FROM myTable where myid = " + objectId.ToString();
    connectionOne.Open();
    myCommand.ExecuteNonQuery();
    connectionOne.Close();
    myCommand.Connection = connectionTwo;
    connectionTwo.Open();
    myCommand.ExecuteNonQuery();
    connectionTwo.Close();
}

Obviously the usage of a stored procedure, as well as proper using statements or try/catch is needed, but this gets the idea across.

Answer 3

Not sure about what you intend to do, but if your goal is to avoid dataloss if a crash occurs on one of your servers, a lot of database servers can automatically replicate all the data to a backup server. If you want more info on this, google for "sql cluster"

Answer 4

Considering "Mitchel Sellers" Suggestion with some changes:-

For ObjectDataSource create OnInserting,OnUpdating,OnDeleting Events in which handle Insert/Update/Delete on all the Databases except the one attached to he ObjectDataSource.

e.g

If DataConnectionString1,DataConnectionString2 and DataConnectionString3 are present and DataConnectionString1 is attached to ObjectDataSource1 then consider the ObjectDataSource1_ObjectUpdating Event code given below....

protected void ObjectDataSource1_ObjectUpdating(object sender, ObjectDataSourceMethodEventArgs e)

{

    string name = (string)e.InputParameters["name"];

    int id = (int)e.InputParameters["id"];

    if (string.IsNullOrEmpty(name))
        e.Cancel = true;

    else
    {
        // Here insert in all Databases currently present 
        // Except DB with StorageIndex = 1 as will b updated by ObjectDataSource1

        for (int index = 2; index <= 3; index++)
        {
            string DataConnectionString = ConfigurationManager.AppSettings["DataConnectionString " + index]);
            SqlConnection con = new SqlConnection(DataConnectionString);
            string query = "UPDATE student SET [name] = @name WHERE (([id] = @id))";
            int cnt = Utils.executeQuery(query, con, new object[] { "@name", "@id" }, new object[] { name, id });
        }
    }
}

This is what I did!!! Hope it Helps others too....