CODEWITHSUNDEEP
c#
GurdeepS
GurdeepS

Reputation: 67213

How to load SQL Server DB into dataset?

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

Upvotes: 2

Views: 2229

Answers (4)

Richard Reddy
Richard Reddy

Reputation: 2170

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.

Upvotes: 0

Hogan
Hogan

Reputation: 70513

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);
        }
    }

Upvotes: 1

Joel Mansford
Joel Mansford

Reputation: 1316

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.

Upvotes: 1

Dani
Dani

Reputation: 15069

  1. Read some article about in memory Database.

  2. @ 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.

  3. 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.

Upvotes: 0

Related Questions