Problem when saving row in DataSet using ADO.net

Question

Problem when saving row in DataSet using ADO.net

Navigation

#1 by (1 votes)

0

I bring you another little problem that came up during my project (which is almost finished already). What this code is supposed to do is create a new "Artist" row, add it to the dataset (in its datatable) and update the database. Well, it's clear that something is missing. But I do not know what. I'm sorry for being so noob, I'm really not capable and it's been a good few hours already giving me face to face with this. I would appreciate any help.

The fields in the table are: Name (Varchar), Genre (Varchar), Labels (Varchar) and RealName (Varchar) and in the Artist class are the same, with String.

private void addToDataTable(DataTable d,Object objeto)
    {
        DataRow newRow;

        if (d.TableName.Equals("artists"))
        {
            Artist dbArtist = (Artist)objeto;

            newRow = ds.Tables["Artists"].NewRow();
            newRow[0] = dbArtist.Name.ToString();
            newRow[1] = dbArtist.Genre.ToString();
            newRow[2]=dbArtist.Labels.ToString();
            daArtists.Update(ds,"Artists");

        }
        else if (d.TableName.Equals("albums"))
        {
            newRow = ds.Tables["Albums"].NewRow();
        }
        else
        {
            //newRow = ds.Tables["Songs"].NewRow();
        }
    }

mysql c# visual-studio-2017 ado.net innodb

asked by Denis Siks 16.11.2018 в 17:53

source

1 answer

Group records by column without the null value (Sql Server) Doubt in laravel driver with php

score 1 · Answer 1

To add a new row you only have to access the table rows by .Rows and then use the .Add() method to add a new row, leaving something similar to this:

ds.Tables["Artists"].Rows.Add(newRow);

Then your code should look like this:

private void addToDataTable(DataTable d,Object objeto)
{
    DataRow newRow;

    if (d.TableName.Equals("artists"))
    {
        Artist dbArtist = (Artist)objeto;

        newRow = ds.Tables["Artists"].NewRow();
        newRow[0] = dbArtist.Name.ToString();
        newRow[1] = dbArtist.Genre.ToString();
        newRow[2]=dbArtist.Labels.ToString();
        ds.Tables["Artists"].Rows.Add(newRow);
        daArtists.Update(ds, "Artists");
    }
    else if (d.TableName.Equals("albums"))
    {
        newRow = ds.Tables["Albums"].NewRow();
    }
    else
    {
        //newRow = ds.Tables["Songs"].NewRow();
    }
}

To insert in your database you can go through the rows of your DataTable and insert like this:

using (SqlConnection cnx = new SqlConnection("CONNECTION_STRING"))
{
    using (SqlCommand cmd = new SqlCommand("INSERT INTO tabla VALUES (@Name, @Genre, @Labels)", cnx))
    {
        cmd.Parameters.Add(new SqlParameter("@Name", 0));
        cmd.Parameters.Add(new SqlParameter("@Genre", 0));
        cmd.Parameters.Add(new SqlParameter("@Labels", 0));
        foreach (DataRow row in ds.Tables["Artists"].Rows)
        {
            cmd.Parameters["@Name"].Value = row[0];
            cmd.Parameters["@Genre"].Value = row[1];
            cmd.Parameters["@Labels"].Value = row[2];
            cmd.ExecuteNonQuery();
        }
    }
}

Another way is by sending the table in a single query .

If you want to save the last record you can select the last row using Linq in this way:

using (SqlConnection cnx = new SqlConnection("CONNECTION_STRING"))
{
    using (SqlCommand cmd = new SqlCommand("INSERT INTO tabla VALUES (@Name, @Genre, @Labels)", cnx))
    {
        cmd.Parameters.Add(new SqlParameter("@Name", 0));
        cmd.Parameters.Add(new SqlParameter("@Genre", 0));
        cmd.Parameters.Add(new SqlParameter("@Labels", 0));
        DataRow row = ds.Tables["Artists"].AsEnumerable().LastOrDefault();
        cmd.Parameters["@Name"].Value = row[0];
        cmd.Parameters["@Genre"].Value = row[1];
        cmd.Parameters["@Labels"].Value = row[2];
        cmd.ExecuteNonQuery();
    }
}