Simple way to write DataSet contents to local system.

[Andy]

I have a web service that collects data.
However the service is hosted on a machine that it is difficult for me to
access and administer ...
As the database is small, I have added a web service method that returns the
entire database as a DataSet.

What I would like to do is create a local copy of the database from my
dataset so that I can back it up and perform any other admin/maintenance
tasks that I may want to do?
However I am finding this difficult.
I could write the whole lot out as an XML file but I want to view/query in
Enterprise manager just like any other database and so don't really want to
use the XML serialization methods if I can help it.

What I would like to do is just point the dataset at a newly created
database and say "save yourself there please" - metadata, relations and all.
Is there a "reasonably simple" idiomatic way to do that - or do I have to
naviagation my way all around the DataSet pulling out tables, columns, rows
and reconstructing the database bit by bit on my local machine?

Ideally I would like to be able to save the database to any of MSDE, SQL
Express, SQL2000, SQL2005 but if I could just get it working for one of
those initially, I would be happy.
Alternatively if this approach is completely flawed from start to finish and
there is a better way to achieve the same objective I would welcome
suggestions.

TIA

 

[Cor Ligthert [MVP]]

Andy,

In my idea are you giving your answer yourself, however asking that to the
wrong newsgroup.

You want to copy your database on your local SQL server.

Why not ask this in an SQL server newsgroup.

Cor

 

[mojeza]

Hi Andy,

I think that your approach will require a bit of code but it is
doable.
Another way to accomplish this would be to use Web Service to backup
the database, return BAK file to your local machine and then restore
on your local server. These links will get you some idea how this can
be done:

http://www.c-sharpcorner.com/Upload...FHFAG5235235KHDSHBFKSBGKSBGDBSDKBGSDHKGS.aspx

http://www.codeproject.com/showcase/RedGate_Backups.asp

 

[Andy]

Thanks for your response Cor.

I wonder if you missed something or did not quite understand what I was getting at?

I am using DataSet's, SlqDataAdapters, SqlCommandBuilder's etc, etc.
These are all ADO.Net classes - no?
My inexperience with these classes is at the root of my problem - so this certainly seems like the correct newsgroup from where I am standing.
Your perspective may be different and I respect that.

I had already retrieved a DataSet in my client app and all I wanted to do was write that to a local server and I thought that should be simple.

I did manage it in the end by creating a new database on a local machine, building the structure of the database from an SQL script and then loading the newly created (empty) database into a dataset.
I then copy the populated DataSet from the web server row by row into the empty I have just built and save the empty one back to the newly created database (though it is not empty any more - so the use of the variable name 'empty' below is poor).

Code as follows ...

// this line creates an empty DataSet from my newly created database shell
DataSet empty = database_loader.load(connection_string);

// this loop copies the data, row by row, from the WebService dataset into my new database ...
foreach (DataTable t in to_save.Tables)
{
// destination will be empty at this point
DataTable destination = empty.Tables[t.TableName];
if (destination != null)
{
// we fill destination row-by-row here!
foreach (DataRow row in t.Rows)
{
destination.Rows.Add(row.ItemArray);
if (progress != null) progress.position = ++progress_posn;
}
// if there is any data in it now we save that back what was originally our empty database
if (destination.Rows.Count > 0)
{
// these commands save the data for the current table back to the newly created database
SqlDataAdapter adapter = new SqlDataAdapter(string.Format("select * from \"{0}\"", t.TableName), conn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
builder.GetInsertCommand();
adapter.Update(destination);
rval = true;
}
}
}

This seems to work well but it took me a while to get there.
Primarily because the ADO.Net object model is so poorly documented.
Hence I visited this news group!

Andy

 

[Andy]

Thanks for your response Mojeza.
I managed this in the end - see code posted in my response to Cor Ligthert.
I like your idea of creating the .BAK file on the server and downloading
that.
It seems very sensible.

However I already have a client app that gets a DataSet from the server for
reporting purposes.
So being able to use my existing code and just write that DataSet to a local
machine seemed like the obvious thing to do.
(To me anyway). I just found the ADO.Net object model somewhat confusing to
find my way around! Hence the post.

Thanks again.

Andy

 

[Cor Ligthert [MVP]]

Andy,

I had not the idea that I was missing something, "but why do you use those?, that was my question when I was reading your message.

Cor

"Andy" <[email protected]> schreef in bericht Thanks for your response Cor.

I wonder if you missed something or did not quite understand what I was getting at?

I am using DataSet's, SlqDataAdapters, SqlCommandBuilder's etc, etc.
These are all ADO.Net classes - no?
My inexperience with these classes is at the root of my problem - so this certainly seems like the correct newsgroup from where I am standing.
Your perspective may be different and I respect that.

I had already retrieved a DataSet in my client app and all I wanted to do was write that to a local server and I thought that should be simple.

I did manage it in the end by creating a new database on a local machine, building the structure of the database from an SQL script and then loading the newly created (empty) database into a dataset.
I then copy the populated DataSet from the web server row by row into the empty I have just built and save the empty one back to the newly created database (though it is not empty any more - so the use of the variable name 'empty' below is poor).

Code as follows ...

// this line creates an empty DataSet from my newly created database shell
DataSet empty = database_loader.load(connection_string);

// this loop copies the data, row by row, from the WebService dataset into my new database ...
foreach (DataTable t in to_save.Tables)
{
// destination will be empty at this point
DataTable destination = empty.Tables[t.TableName];
if (destination != null)
{
// we fill destination row-by-row here!
foreach (DataRow row in t.Rows)
{
destination.Rows.Add(row.ItemArray);
if (progress != null) progress.position = ++progress_posn;
}
// if there is any data in it now we save that back what was originally our empty database
if (destination.Rows.Count > 0)
{
// these commands save the data for the current table back to the newly created database
SqlDataAdapter adapter = new SqlDataAdapter(string.Format("select * from \"{0}\"", t.TableName), conn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
builder.GetInsertCommand();
adapter.Update(destination);
rval = true;
}
}
}

This seems to work well but it took me a while to get there.
Primarily because the ADO.Net object model is so poorly documented.
Hence I visited this news group!

Andy

 

[Andy]

I guess I use them for the same reasons that everyone else who visits this newsgroup uses them.
If you can tell me about some better tools or a better approach please do.
As I said in my OP - if my approach is wrong and there is a better one then I would love to know about it.

As I explained in my resposne to Mojeza - I already had a client app that had downloaded the DataSet from a server.
So rather than start from scratch I was looking for something that I could easily add to that app that would just serialize the whole dataset to an existing local server.

Hope that answers your question.

Andy

Andy,

I had not the idea that I was missing something, "but why do you use those?, that was my question when I was reading your message.

Cor

"Andy" <[email protected]> schreef in bericht Thanks for your response Cor.

I wonder if you missed something or did not quite understand what I was getting at?

I am using DataSet's, SlqDataAdapters, SqlCommandBuilder's etc, etc.
These are all ADO.Net classes - no?
My inexperience with these classes is at the root of my problem - so this certainly seems like the correct newsgroup from where I am standing.
Your perspective may be different and I respect that.

I had already retrieved a DataSet in my client app and all I wanted to do was write that to a local server and I thought that should be simple.

I did manage it in the end by creating a new database on a local machine, building the structure of the database from an SQL script and then loading the newly created (empty) database into a dataset.
I then copy the populated DataSet from the web server row by row into the empty I have just built and save the empty one back to the newly created database (though it is not empty any more - so the use of the variable name 'empty' below is poor).

Code as follows ...

// this line creates an empty DataSet from my newly created database shell
DataSet empty = database_loader.load(connection_string);

// this loop copies the data, row by row, from the WebService dataset into my new database ...
foreach (DataTable t in to_save.Tables)
{
// destination will be empty at this point
DataTable destination = empty.Tables[t.TableName];
if (destination != null)
{
// we fill destination row-by-row here!
foreach (DataRow row in t.Rows)
{
destination.Rows.Add(row.ItemArray);
if (progress != null) progress.position = ++progress_posn;
}
// if there is any data in it now we save that back what was originally our empty database
if (destination.Rows.Count > 0)
{
// these commands save the data for the current table back to the newly created database
SqlDataAdapter adapter = new SqlDataAdapter(string.Format("select * from \"{0}\"", t.TableName), conn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
builder.GetInsertCommand();
adapter.Update(destination);
rval = true;
}
}
}

This seems to work well but it took me a while to get there.
Primarily because the ADO.Net object model is so poorly documented.
Hence I visited this news group!

Andy