Update multiple databases using the same ObjectDataSource using C #
Is there a way to update multiple databases having the same schema using a single ObjectDataSource in C #?
ie Just by providing multiple connection strings, is it possible to somehow update multiple databases? I need to update / insert the same record in multiple databases with the same schema using ObjectDataSource in C #.
source to share
Yes, you can do that, because with the ObjectDataSource you have to write the code that does the insert. Inside the Update and Delete methods, you can simply do two things on the database: one for each database you are working with. You can abstract this into an operation that can be passed using a join to make sure you don't have duplicate code sitting around.
NOTE
You CANNOT do this thought over a single connection, you have to do two completely separate actions on the database.
Example
Added a request for more detail in the comments.
Basically, inside each of your methods, just make two db calls, a crude AND NOT well-formed example, to show the concept below, for the "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 using a stored procedure as well as using the correct statements or try / catch is necessary, but that gives the idea.
source to share
Considering " Mitchel Sellers " Proposal with some modifications: -
For ObjectDataSource, create OnInserting, OnUpdating, OnDeleting Events in which the handle is Insert / Update / Delete in all Databases except those that have been bound to ObjectDataSource.
eg
If DataConnectionString1 , DataConnectionString2 and DataConnectionString3 , and DataConnectionString1 is attached to ObjectDataSource1 then consider the ObjectDataSource1_ObjectUpdating event code 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 ...
source to share
I would say no; I don't think this is possible as the SqlCommand only needs to be associated with one connection string.
Since you are using an ObjectDataSource, why not write a "middle tier" object that handles it? Typically, I will have a class called CustomerManager that provides all the methods needed for ObjectDataSource operations for CRUD.
Suppose you are updating a customer address and you need to touch a different database: your update (...) method in your CustomerManager class can then call both databases inside the method. The nice thing about this approach is that you can even wrap everything inside a TransactionScope block, and if you update more than one database, .NET will automatically "grow" the transaction into a distributed transaction.
I hope this makes sense to get you going in the right direction!
source to share