I will have to run back through this post later, but let's see if I get the
basic gist of what is going on.
The web site is using data disconnected in the form of a DataSet. You are
loading from the Database into this "cache" and you want to update on a
regular schedule.
The issue you are going to have is syncing up the data in memory with the
database. This means either a) locking the app out, updating and refreshing,
which could lock people out permanently or b) taking a chance of losing data
during the sync. Neither is particularly appealing to me, unless this is
extremely fast (with only 65k records, it is probably not a big deal.
Depending on traffic, another option is using SQL 2005 Express and having it
updated. SQL has a transaction log, which means you can update while
activity is in progress without danger of sync issues. SQL Express is free
and if you are using ASP.NET 2.0 you can create the file base version on the
ASP.NET site without having to deploy the server (note that server software
will have to exist on the ASP.NET server). This may not be an option for
you, but it is safer.
You then move from a custom "cache" solution to a normal replication type of
scenario. You can update through replication, for MS SQL, or use Service
Broker if the connection will be broken from time to time. You will have to
customize the transfer to Sybase or MySQL, but there are plenty of ways to
do that from Database to Database.
Not sure if these are options for you, but the "cache" version means you
have to most likely lock and/or use a rather chatty syncing to ensure no
data is lost. It can be wrapped in a transaction type mechanism, but
rollback will be a pain. What you have to determine is how fast the database
refresh is and whether reloading a DataSet is an option based on traffic.
If you must go to cache, I would wrap cache with your own object and pull
the new data in first, then attempt to refresh the database and then make
sure the new dataset, now serving requests is in sync. The danger point is
the second and third step. If this is really fast, you can lock the data and
run the cycle, which is the safest. The safest way I know of to do this is
to wrap the cache and use multiple threads with a mutex (mutually exclusive)
so two ops cannot act on the data at one time. The danger here is how the
system recovers if the update fails.
If SQL Express is an option, use it, as the hard part is already solved for
you. Working with an in memory database means you have to write all of the
infrastructure. The longer the update takes, the more complex your code will
likely get.
Hope this helps.
--
Gregory A. Beamer
*************************************************
Think Outside the Box!
*************************************************
Newkid said:
Hello Gregory!
Thanks a ton for wonderful explainations!
First let me allow to brief you about the work. The work which is
assigned to me is something like client has tool that pushes the
records (products which client want to sell online from his 65k
products list) in a dataset onyo a webserver cache. What I needs to do
is take the records from the datasets and update three different
database(Sysbase / MY SQL / MS SQL) at a particular time frame, say
after every half an hour. The current e-commerce website is in PHP /
MYSQL.
Now, please find my answer below your queries:
a) Default ASp.NET caching? Not really a viable option in a web service
scenario, as you are talking a stateless environment. You could cache
commonly used items, if you can identify them, but in most cases the DB
engine is better at caching than you. Am I missing somethign?
Yes, you're correct. I'm refering to ASP.NET Caching.Well, I'm baised
to use the ASP.NET caching and read records from the cache. The person
who has coded the service at client end is forcing us to consume their
service in the same way despite of our recommendations.
I would like to know a basic question here; What my understading is a
dataset can contains complete inventory, if it's the case how one can
retrieve records from a given table. In other words, Is it possible to
retrive the information of one particular field of a table from a
dataset? if yes, how?. Hope, I'm making sense.
c) > This is a "it depends" answer, as you never know when garbage
collection clears things out. From the standpoint of access, unless you
cache them or stick them somewhere, they are gone after they are passed
on.
Okay. Any default time frame?. I'm asking this because the person is
sitting at client end is not responding et all. He always said that no
one has faced the issue so far (approx 1500 installations) except us.
And I'm not able to read the records from cache by all way
serialization / deserializations / System.Web.Caching namespace and
what not. I don't know how to proceed and how to check whether he is
correct or not.?
If you use the standard ADO.NET methods, you link up the SELECT, INSERT,
UPDATE and DELETE queries to your DataAdapter and call Update(). As long
as
you either have a query or a stored procedure (I think MySQL has this in
its
latest incarnation).
Would you please kind enough to explain it with example or redirect me
to a page where I can read more about them?
Once again, thank you so much for your time and support.
Shyam
Answers inline with queries.
Hi there!
I'm very new to dot net envirnoment and have little bit understanding.
Due to some reason, I need to work straight forward on datasets and
webservices and xml. I don't have much hold on the same but would
appreciate if someone put lights on the given questions:
a) How datasets which has passed by value can be retreived from cache?
Default ASp.NET caching? Not really a viable option in a web service
scenario, as you are talking a stateless environment. You could cache
commonly used items, if you can identify them, but in most cases the DB
engine is better at caching than you. Am I missing somethign?
b) How would one know whether datasets is containing any records or
not? Is there any way to test the same? Please let me know via coding.
Single table dataset:
if(dataSet.Tables[0].Rows.Count != 0)
{
//Do something, as there are records
}
c) How long datasets will store in server memory?
This is a "it depends" answer, as you never know when garbage collection
clears things out. From the standpoint of access, unless you cache them
or
stick them somewhere, they are gone after they are passed on.
d) How to create a dataset and publish it in cache?
For a web service, you can cache with
Cache["nameOfDataSet"] = ds;
I am still not sure what you are caching, or where ... much less why.
Beside this, I do have other questions which will not fall into this
group like :
1) How to map datasets records to update a MYSQL Server?
If you use the standard ADO.NET methods, you link up the SELECT, INSERT,
UPDATE and DELETE queries to your DataAdapter and call Update(). As long
as
you either have a query or a stored procedure (I think MySQL has this in
its
latest incarnation).
--
Gregory A. Beamer
*************************************************
Think Outside the Box!
*************************************************
