insert into a table using DataSet

I'm trying to insert a row into a table using a DataSet, and having no luck. Here's what I have so far:

            DataSet ds = new DataSet();
            SqlDataAdapter sda;

            XmlSerializer ser = new XmlSerializer(typeof(myClass));
            StringWriter sw = new StringWriter();
            ser.Serialize(sw, newdata);
            StringReader sr = new StringReader(sw.ToString());

            sda = new SqlDataAdapter("SELECT * from mytable", connectString);
            // sda.Fill(ds, "mytable");
            ds.ReadXml(sr);
            DataTable dt = ds.Tables[0];
            sda.Update(dt);

So basically, I'm serializing a class into XML, then I ReadXML into a DataSet. I want to add that new row into mytable, but I keep getting weird errors. If I uncomment the sda.Fill line, I don't get any errors, but it doesn't add the appropriate row to the table either.

Any thoughts Is this the wrong way to do this I know I could insert using a plain old SqlCommand object, but this seemed easier since I want to do the whole serialization thing.
Thanks!

Answer this question

insert into a table using DataSet

Helena Kotas
Hrm. If I do that sda.InsertCommand stuff, don't I have to do an Add() for each column in the table I'm adding That kind've defeats the whole purpose of using the XML and sticking it in a DataSet. Or perhaps I'm misunderstanding
ddawsonb

Hi,

The Insertcommand property of the data adapter specifies on how your data would be saved in the database. And since you uploaded an xml into your datatable then I guess all of that uploaded data were automatically "added" into your datatable and hence added into the database after the update() call...

cheers,

Paul June A. Domag
Tick Tock

Hi,

I guess you forgot to specify the INSERT command of the adapter.

sda.InsertCommand = new SqlCommand("INSERT INTO mytable VALUES(@id)");
sda.InsertCommand.Parameters.Add("<parameter>", SqlDbType.Int, 4, "<source column in dataset>");

you must specify this prior to any update calls.

cheers,

Paul June A. Domag
insert into a table using DataSet

Answer this question