datatable constraintsexception occurs when dt.load(reader)

Question

I have a project with c# and mysql. For querying, i prepared stored procedures and used

public Sorgulama sp_call(string sp_query, Dictionary<string, object> parameters)
    {
        Sorgulama sorgulama = new Sorgulama();
        sorgulama.hatasiz = true;
        DataTable dt = new DataTable();

        using (var con = new MySqlConnection(myDBstring))
        {

            try
            {
                con.Open();

                using (MySqlCommand cmd = new MySqlCommand(sp_query, con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (parameters != null) { 
                        foreach (KeyValuePair<string, object> item in parameters)
                        {
                            object value = new object();
                            cmd.Parameters.AddWithValue(item.Key, item.Value);
                        }
                    }

                    var rdr = cmd.ExecuteReader();

                    dt.Load(rdr);

                    rdr.Close();
                }
            }
            catch (Exception Ex)
            {
                sorgulama.hatasiz = false;
                ///
                string hatalirowlar = "";
                foreach (DataRow dr in dt.GetErrors())
                {
                    hatalirowlar =  hatalirowlar + "\n" + dr.RowError.ToString();
                }
                XtraMessageBox.Show(hatalirowlar);
                ////
                XtraMessageBox.Show("Bağlantı problemi." + Ex.ToString());
            }

            finally
            {
                if (con.State == System.Data.ConnectionState.Open) con.Close();
            }
        }
        sorgulama.obje = dt;
        return sorgulama;
    }

and this is my stored procedure example:

SELECT h.id AS hesapid   FROM `evrak_bordrolar` AS b
LEFT JOIN `hesaplar` AS h ON b.hesapid=h.id;

this procedures have no errors on phpmyadmin or sql applications(like navicat) when some rows have the same values. But it throws constraintexception in c# application.

And this is the dr.rowerror() result: "Column 'hesapid' is constrained to be unique. Value '8' is already present." — fuatkaraca, Jan 20 '16 at 14:06
Well if the relationship between evrak_bordrolar and hesaplar is one-to-many that would explain duplicate hesapid values, but I don't see where the constraint is coming from. — juharr, Jan 20 '16 at 14:16
When i use query string in my application instead of procedure, there is no problem. DataTable dt = new DataTable(); using (var con = new MySqlConnection(myDBstring)) { try { con.Open(); using (MySqlDataAdapter adap = new MySqlDataAdapter(sorgu, con)) { adap.Fill(dt); } } — fuatkaraca, Jan 20 '16 at 14:23

score 0 · Answer 1 · answered Jan 20 '16 at 18:22

Found a solution: When using dataadapter.fill(datatable); there is no constraints for table. But like in my app, if datatable is loaded by reader dt.Load(rdr); the constraints is TRUE defaultly. So a dataset with a FALSE constraints instance must be created.

var rdr = cmd.ExecuteReader();
using (DataSet ds = new DataSet() { EnforceConstraints = false })
     {
      ds.Tables.Add(dt);
      dt.Load(rdr);
     }

datatable constraintsexception occurs when dt.load(reader)

1 Answers1