C# .NET - Dataset

How can we store a dataset in the sql server2005 database and fetch the database during run time in the application using c#.

Are there any options to store the xml representation of the dataset into the dataset and how to get the dataset back from the xml.

Hi,

you can restore the dataset with XML using

Dataset ds = new Dataset();

ds.ReadXml("file name");

convert dataset to XML as

ds.WriteXml("file name");

 

to commite the dataset to database, use SqlBulkCopy

DataSet ds = new DataSet();
        //load ds here
        SqlBulkCopy bCopy = new SqlBulkCopy("Server=Server;Database=dbName;Trusted_Connection=True;",SqlBulkCopyOptions.TableLock);
        bCopy.DestinationTableName = "target table Name";
        bCopy.WriteToServer(ds.Tables[0]);

Ideally you wouldn't store a dataset into a table and read it from the table. That means you need to get the xml out of it and save. and reconstruct the dataset from the xml when you need it.

But if that is your requirement, then you can use the following approach.

To store a dataset in xml format, all you need to do is,

1. Create a method that accepts a dataset and returns you a xml representation of it. like,

public static XMLDocument GetXMLData(DataSet ds)

{

      //You could stop at getxml's string, or prepare a xmldoc so that it can be used by openxml

         string xmlData = ds.GetXml();

         XmlDocument xmldoc = new XmlDocument();

         xmldoc .LoadXml(xmlData);

         return xmldoc ;

}

2. Pass in as XML dbtype through your db classes.

   dbHelper.AddInParameter("@xmlData", xmlData.InnerXml, DbType.Xml);

3. In your sp, take a variable

@xmldoc NTEXT

and then say

DECLARE @xmld INT

EXEC SP_XML_PREPAREDOCUMENT @xmld OUTPUT, @xmldoc

4. Finally insert into your table.

INSERT INTO

[TableName]

SELECT

XMLValue = XMLDOC.XmlValueFROM

OPENXML(@xmld , 'Table/Column', NodePos)

WITH

( XmlValue datatype) XMLDOC

 

Hi ,

I got how to store the dataset in the table and now how can we retrive the dataset from the table during run time.

And then use DataSet.ReadXML method

Hi,

When we get the xml representation of the dataset using getxml() method the table with zero rows are ommited and so that when we recreate the dataset i am not getting the those tables with zero rows..

Any alternatives for this.
 

end of post

Hi,

for this purpose, you need to write the schema of the datasset also in XML ...

try the below one..

ds.WriteXml("file Name", XmlWriteMode.WriteSchema);

the above code store the dataset into xml along with its schema. you can restore the dataset even though you have no datarows in it...