Dataset Manipulation

Have an XML file that i load in to a Dataset. works fine, it builds its own scheme perfectly.

I loop through that data to load a check list which also works wonderfully.

two questions based on that.

1) can i add a column after the fact? I want to basically update the the info in the dataset to store if the record was checked in the check list. if not, i can manipulate one of the already defined columns, but i would rather not.

2) what is the best way to update data with in the dataset? Never really done anything but pull data from one. how do i locate the correct row to update ("select name from tbl where name = " + checklist.items[index].tostring(); update row)

sorry for the fairly basic question. i appreciate the help.

Justin

re #1: You can add columns to dataset tables at any time.

DataColumn _dc =newDataColumn("newcolumnname");

mydataset.Tables[0].Columns.Add(_dc);

re #2: You are asking more than just updating. You are asking how to find the row to update as well.

Create a DataView object to pass in QueryStatements to find the records you want.

Then edit the field value, something such as:

mydataset.Tables[0].Rows[4][

"newcolumnname"] ="newvalue";

mydataset.AcceptChanges();

|||

great, thanks. not really shore how i missed the column.add, i was even looking for that...

I found something else i might try for the locate and update. because it reads from an XML with no schema it doesnt create a PK. I was going to set a PK (MyDataTable.PrimaryKey = PKColumn) and then use the Find method to locate the row i want (myDataTable.Rows.Find(objValue))

not sure how it will workout, going to give it a try. if not i can do as you suggested.

I appreciate the help.

Thanks

Justin

Database vs Schema

We currently have a product in which each client has their own Database. We adjust the connection when a user for that client logs into the system. This system has continued to grow and a good pace, but we have come to a point where failover is taking too long.

Refactoring the Database to handle multiple sites in a single database is not an option because of the time it would take to make the change. So, we are looking for another way in which this could be handle. One idea is to take multiple clients and place them in a single database using a schema to seperate them. (ex. Client A = server1.db1.schema1, Client B = server1.db1.schema2, etc).

Is there another option that would be better, or what kind of performance can we expect if we follow this path? Or, is there a way to decrease the failover time (it appears the problem is the startup of the database on the failover server)?

Thad

You are right that having a large number of databases will increase your fail-over time in a fail-over cluster.

Since you mentioned schemas, you must be on SQL Server 2005. For fail-over time, database mirroring is much faster than fail-over clustering. Database mirroring does require twice the storage space, and the licensing situation is not good compared to an Active/Passive cluster. You would have to mirror each database individually.

Are you failing-over for maintenance, rolling upgrades, etc.? Maintaining and adding dozens of schema in a database would be cumbersome and prone to error. It also has some performance drawbacks, since if you don't properly schema qualify a SP name (from code), or a table name (in an SP), you will have name resolution problems and cache misses.

Depending on the numbers of clients you are talking about, I would think about getting another cluster to split the load.

|||

Currently the failover system is done for disaster recovery scenarios. As for performance. All our inline sql from the client class generate sql similar to

Code Snippet

SELECT [Schema].[Table].[Field] FROM [Schema].[Table]

While a stored procedure is set is setup so it exists in each schema, but the schema name is not used in the sproc body. This is the same method we would use with Views and Functions.

Code Snippet

CREATE PROCEDURE [Schema].[ProcedureName](@.Param INT) AS

BEGIN

SELECT * FROM [Table]

END

Would setting up the schema in this way cause these performance drawbacks. I am thinking that maintaining multiple schema will be as error prone as setting up the single db for each client.

Currently we are talking about approx 1000+ clients.

|||

If your client code can pick up the schema name dynamically from a config file or something, then having a schema for each client as opposed to a database for each client will probably be not much different from a maintenance and configuration perspective.

There may be some negative effects on your buffer cache and procedure cache from this approach. Instead of having one large, normalized table that holds data for all your clients in a single database, you will have one table for each client. You will have multiple copies of the same SP (for different schemas) in your procedure cache.

|||Currently the client already selects the Database so we would just need to add a schema selector to the data selection. So all that is straight forward. And yes we are duplicating the entire database inside a schema.

As for the procedure and data cache, which I am not completely familar with, would it not be the same as multiple database?

I can that this is not the best solution, but I think it may be the best solution given the time and other hardware constraints we have. So, it appears that there are little differences in performance and maintenance that we already deal with.