From DataTable Insert into Sql Server Database

Question

I'm loading data from a XML file and fill a DataTable. Then I insert that data into SQL Server using a stored procedure.

I'm using this code, but it is not working when saving to database:

private void button1_Click(object sender, EventArgs e)
{
        XmlDocument xmldoc = new XmlDocument();
        xmldoc.Load(@"D:\DBXML.xml");

        XmlElement root = xmldoc.DocumentElement;
        XmlNodeList nodes = root.SelectNodes("/students/student");

        DataTable dt = new DataTable();
        dt.Columns.Add("Nama", typeof(string));
        dt.Columns.Add("Alamat", typeof(string));

        foreach (XmlNode item in nodes)
        {
            DataRow dr = dt.NewRow();
            dr[0] = item["name"].InnerText;
            dr[1] = item["address"].InnerText;
            dt.Rows.Add(dr);

            dataGridView1.DataSource = dt;

            using (var cmd = new SqlCommand("InsertSiswa") { CommandType = CommandType.StoredProcedure })
            {
                //DataTable dt = new DataTable();
                cmd.Parameters.Add(new SqlParameter("@MyDataTable", dt));
                cmd.ExecuteNonQuery();
                MessageBox.Show("Sukses");
            }

You are looping the data and trying to insert every row. Also if you are using Table Value Parameter in SP, then you must set its SqlDbType as System.Data.SqlDbType.Structured. See more about table value parameter http://stackoverflow.com/questions/10409576/pass-table-valued-parameter-using-ado-net — SelvaS, Feb 20 '15 at 06:57
You're doing the call to the stored procedure **for each XML node** - is that really what you want?? Didn't you intend to **first** load the entire XML (iterating over all nodes), and then save the resulting data table **once** when it's completed?? — marc_s, Feb 20 '15 at 06:58

score 1 · Accepted Answer · answered Feb 20 '15 at 07:03

1

you can use uder-defined-table-type in SQL server to insert the DataTable to SQL Server in a smoother way.

create User-defined-table-type Example

use user-defined-Table-type in Procedure Example

answered Feb 20 '15 at 07:03

Venki

535
2
8
21

score 0 · Answer 2 · answered Feb 20 '15 at 08:07

yes now has been solved, this code work :

 SqlConnection koneksi = null;
        koneksi = new SqlConnection(conn);
        koneksi.Open();

        XmlDocument xmldoc = new XmlDocument();
        xmldoc.Load(@"D:\DBXML.xml");
        XmlElement root = xmldoc.DocumentElement;
        XmlNodeList nodes = root.SelectNodes("/students/student");
        DataTable dt = new DataTable();
        dt.Columns.Add("Nama", typeof(string));
        dt.Columns.Add("Alamat", typeof(string));
        foreach (XmlNode item in nodes)
        {
            DataRow dr = dt.NewRow();
            dr[0] = item["name"].InnerText;
            dr[1] = item["address"].InnerText;
            dt.Rows.Add(dr);
            dataGridView1.DataSource = dt;



        }
        //senddtata();
        string dtout = dt.Rows[2][1].ToString();

        SqlCommand cmd = new SqlCommand("InsertSiswa",koneksi);
        cmd.CommandType = CommandType.StoredProcedure;
        {
            //DataTable dt = new DataTable();
            cmd.Parameters.Add(new SqlParameter("@mytable", dt));
            cmd.ExecuteNonQuery();
            MessageBox.Show("Sukses");

        }
    }

From DataTable Insert into Sql Server Database

2 Answers2