Inserting multiple Values into Microsoft SQL Server via C#

Question

I have a database made up of 2 (more, actually, but only 2 im working with) tables.

The Material table consists solely of the material-number and the description

DPMatNr
DPBezeichnung

The Eigenschaften table is there to hold the properties of the materials.

It uses the columns:

EigenschaftenBezeichnerID
Wert (value)

My problem is: each entry in the Material table needs to have multiple entries in the Eigenschaften table.

For example:

"Material": 
      DPMatNr = 001, 
      DPBezeichnung = "Description"

"Eigenschaften": 
      EigenschaftenBezeichnerID = 1, 
      Wert = "A4"

      EigenschaftenBezeichnerID = 3, 
      Wert = "80" and so on.

My code currently looks like this:

public static void InsertData(string connectionstring, string matnummer, string bezeichnung, string format, string grammatur, string gewicht, string eform, string kuvertierung, string altkuvert)
{
    string query = @"Insert INTO dbo.Material (DPMatNr, DPBezeichnung)
                     VALUES (@matnummer, @bezeichnung)";
    string query2 = @"Insert INTO dbo.Eigenschaften 
                      (EigenschaftenBezeichnerID, Wert)
                      VALUES (@1, @format, @2, @grammatur, @3, @gewicht, 
                              @4, @eform, @5, @kuvertierung, 
                              @6, @altkuvert)";

    using (SqlConnection cn = new SqlConnection(connectionstring))
    using (SqlCommand cmd = new SqlCommand(query, cn))
    {
        cmd.Parameters.Add("@matnummer", SqlDbType.VarChar, 50).Value = matnummer;
        cmd.Parameters.Add("@bezeichnung", SqlDbType.VarChar, 50).Value = bezeichnung;

        cn.Open();
        cmd.ExecuteNonQuery();

        using (SqlCommand cmd2 = new SqlCommand(query2, cn))
        {
            cmd2.Parameters.Add("@1", SqlDbType.Int).Value = 1;
            cmd2.Parameters.Add("@format", SqlDbType.VarChar, 50).Value = format;
            cmd2.Parameters.Add("@2", SqlDbType.Int).Value = 2;
            cmd2.Parameters.Add("@grammatur", SqlDbType.VarChar, 50).Value = grammatur;
            cmd2.Parameters.Add("@3", SqlDbType.Int).Value = 3;
            cmd2.Parameters.Add("@gewicht", SqlDbType.VarChar, 50).Value = gewicht;
            cmd2.Parameters.Add("@4", SqlDbType.Int).Value = 4;
            cmd2.Parameters.Add("@eform", SqlDbType.VarChar, 50).Value = eform;
            cmd2.Parameters.Add("@5", SqlDbType.Int).Value = 5;
            cmd2.Parameters.Add("@kuvertierung", SqlDbType.VarChar, 50).Value = kuvertierung;
            cmd2.Parameters.Add("@6", SqlDbType.Int).Value = 6;
            cmd2.Parameters.Add("@altkuvert", SqlDbType.VarChar, 50).Value = altkuvert;
            cmd2.ExecuteNonQuery();

        }
        cn.Close();
    }
}

Now I currently get an error that says:

System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object 'dbo.Material' with unique index 'IX_MatNrUnique'

What am I doing wrong?

@TomTom thats why i am here, the error tells me, I can not do the thing the way I do it at the moment, and as I am a newcomer to coding, I am in need of help from people who know what to do in this situation. Probably should have added, that i don't know what VS means with IX_MatNrUnique, as its nothing i've written — Olaf Klausson, Jun 02 '17 at 08:12
The table "Material" has a constraint on the column "DPMatNr" that doesn't allow two records with the same "DPMatNr" value. If you get this error then you are trying to insert a second record with the same "DPMatNr" value. — Steve, Jun 02 '17 at 08:14
Your "Eigenschaften" table needs to reference somehow the "Material" table. In order to do that, you should add a Foreign Key to the "Eigenschaften" table referencing 'Material.DPMatNR'. Now you are trying to insert a row in "Material" with an ID which exists already — D Ie, Jun 02 '17 at 08:16
Another observation is that in `query2` you specify 2 columns in `INSERT` statement, while number of values being inserted is 12. They should match. — the_joric, Jun 02 '17 at 08:18
Have you thought about creating a linking table? https://stackoverflow.com/questions/14978244/sql-two-tables-and-creating-a-link-table — Flexicoder, Jun 02 '17 at 08:22
@the_joric it is just a wrong syntax. With Sql Server 2008 you can write multiple inserts with ... VALUES(@p1, @p2), (@p3, @p4), ..... — Steve, Jun 02 '17 at 08:22
@Steve i did not know that for example! though now: VALUES(@1, @format),(@2, @grammatur),(@3, @gewicht),(@4, @eform),(@5 ,@kuvertierung), (@6,@altkuvert)" throws an "incorrect Syntax near ','" error. — Olaf Klausson, Jun 02 '17 at 08:40
No idea, you can refer to this QA https://stackoverflow.com/questions/2624713/how-do-i-insert-multiple-rows-without-repeating-the-insert-into-dbo-blah-part — Steve, Jun 02 '17 at 08:47

Romano Zumbé · Answer 1 · 2017-06-02T08:21:47.813

The Problem here is, that for every "Eigenschaft" you insert into the table you also try to create an entry in the "Material" table. But since every material should only be inserted once (therefore the primary key) you get the error.

Edit: You could adjust your method like the following:

public static void InsertData(string connectionstring, string matnummer, string bezeichnung, string format, string grammatur, string gewicht, string eform, string kuvertierung, string altkuvert)
{
    string check = "Select COUNT(*) FROM dbo.Material where DPMatNr = @matnummer";
    string query = "Insert INTO dbo.Material (DPMatNr, DPBezeichnung)" + "VALUES (@matnummer, @bezeichnung)";
    string query2 = "Insert INTO dbo.Eigenschaften (EigenschaftenBezeichnerID, Wert)" + "VALUES (@1, @format, @2, @grammatur, @3, @gewicht, @4, @eform, @5, @kuvertierung, @6, @altkuvert)";


    using (SqlConnection cn = new SqlConnection(connectionstring))
    using (SqlCommand chkCom = new SqlCommand(check, cn))
    {
        cn.Open();
        chkCom.Parameters.Add("@matnummer", SqlDbType.VarChar, 50).Value = matnummer;
        int? matCnt = chkCom.ExecuteScalar() as int?;

        if (matCnt == 0 || matCnt == null)
        {
             using (SqlCommand cmd = new SqlCommand(query, cn))
             {
                  cmd.Parameters.Add("@matnummer", SqlDbType.VarChar, 50).Value = matnummer;
                  cmd.Parameters.Add("@bezeichnung", SqlDbType.VarChar, 50).Value = bezeichnung;

                  cmd.ExecuteNonQuery();
             }
        }

        using (SqlCommand cmd2 = new SqlCommand(query2, cn))
        {
             cmd2.Parameters.Add("@1", SqlDbType.Int).Value = 1;
             cmd2.Parameters.Add("@format", SqlDbType.VarChar, 50).Value = format;
             cmd2.Parameters.Add("@2", SqlDbType.Int).Value = 2;
             cmd2.Parameters.Add("@grammatur", SqlDbType.VarChar, 50).Value = grammatur;
             cmd2.Parameters.Add("@3", SqlDbType.Int).Value = 3;
             cmd2.Parameters.Add("@gewicht", SqlDbType.VarChar, 50).Value = gewicht;
             cmd2.Parameters.Add("@4", SqlDbType.Int).Value = 4;
             cmd2.Parameters.Add("@eform", SqlDbType.VarChar, 50).Value = eform;
             cmd2.Parameters.Add("@5", SqlDbType.Int).Value = 5;
             cmd2.Parameters.Add("@kuvertierung", SqlDbType.VarChar, 50).Value = kuvertierung;
             cmd2.Parameters.Add("@6", SqlDbType.Int).Value = 6;
             cmd2.Parameters.Add("@altkuvert", SqlDbType.VarChar, 50).Value = altkuvert;
             cmd2.ExecuteNonQuery();

         }
         cn.Close();
     }
}

Inserting multiple Values into Microsoft SQL Server via C#

1 Answers1