I want to load the entire database (SQL Server) I have into a dataset so that I can work with several tables and their relationships. I know this might be frowned upon, but how can I do this (I will be using DataRelation and Table objects)?
Thanks
2
-
2Why would you want to do this? :PSergio Tapia– Sergio Tapia2010-01-10 16:02:34 +00:00Commented Jan 10, 2010 at 16:02
-
1Not sure why you'd want to do this, but I'd iterate through the list of tables and populate a DataTable with the contents. I'd also expect you to run out of memory first.user114600– user1146002010-01-10 16:09:10 +00:00Commented Jan 10, 2010 at 16:09
Add a comment
|
4 Answers
Unless I'm missing something this should just be a simple case of generating a dataset and then altering the Fill methods to remove the WHERE portion. Then ensure you call the fills in the right order (master, then detail) to ensure you maintain the referential integrity.
1 Comment
GurdeepS
Any example of how to do this?
You can run this... but don't expect to have a db or app server after.
using (SqlConnection conn = new SqlConnection("YourConnectionString"))
{
using (SqlCommand command = new SqlCommand("exec sp_msforeachtable 'select * FROM ?'", conn))
{
conn.Open();
DataSet ds = new DataSet();
command.Fill(ds);
}
}
Comments
Read some article about in memory Database.
@ Randolph Potter idea is an option - you can get the list of tables from the server, and then iterate on the list and load all the tables. I guess you can do that same about FK and relations.
you can probably do it automatically using the designer - using drag and drop from the server explorer to a dataset (VS2008), and with a little code load the entire thing into memory.
2 Comments
GurdeepS
I asked as the question came about from looking at this: knowdotnet.com/articles/datarelation.html I guess I can iterate the tables and then load them/use them. DataTable with a name same as the table in the db once I've connected should be a live instance of that table.
GurdeepS
Also, if I am going to work with 2 tables or more, I can do this: eggheadcafe.com/community/aspnet/2/10110718/… but run the method n times for n tables (in a loop or whatever).
I suppose you could do multiple selects within a single stored procedure and then fill a dataset.
using (SqlConnection conn = new SqlConnection("YourConnectionString"))
{
using (SqlDataAdapter command = new SqlDataAdapter("usp_YourStoredProcedure", conn))
{
command.CommandType = CommandType.StoredProcedure;
conn.Open();
DataSet ds = new DataSet();
command.Fill(ds);
}
}
I would agree with the other comments here that unless your database is tiny this is a really bad idea.
