CODEWITHSUNDEEP
c#sql
3D-kreativ
3D-kreativ

Reputation: 9319

Save data into table not working?

In my code below I call a method along with a reference to a list to save all data like name, age, gender and info from each object, but after I have tested my application, the table is empty! Have I missed something or done wrong? I get no error.

public void SaveDataToDB(List<Animal> animals)
    {
        connection = new SqlConnection(connectionString);
        dataset = new DataSet();
        sql = "SELECT * From Guests";

        try
        {
            connection.Open();
            adapter = new SqlDataAdapter(sql, connection);
            adapter.Fill(dataset, "Guests");

            foreach (Animal animal in animals)
            {
                DataRow row = dataset.Tables["Guests"].NewRow();
                row["Name"] = animal.Name;
                row["Age"] = animal.Age;
                row["Gender"] = animal.Gender;
                row["Info"] = animal.ImportantInfo;

                dataset.Tables["Guests"].Rows.Add(row);
            }
            new SqlCommandBuilder(adapter);
            adapter.Update(dataset.Tables["Guests"]);
            connection.Close();
        }
        catch
        {
            throw;
        }
    }

Upvotes: 1

Views: 164

Answers (1)

Andrei
Andrei

Reputation: 56716

For your inserts to work correctly you need to define an InsertCommand for the adapter. Here is the sample:

public void SaveDataToDB(List<Animal> animals)
{
    SqlConnection connection = new SqlConnection(connectionString);
    DataSet dataset = new DataSet();
    string sql = "SELECT * From Guests";

    try
    {
        connection.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
        adapter.Fill(dataset, "Guests");

        // Create the InsertCommand.
        SqlCommand command = new SqlCommand(
            "INSERT INTO Guests (Name, Age, Gender, ImportantInfo) " +
            "VALUES (@Name, @Age, @Gender, @ImportantInfo)", connection);

        // Add the parameters for the InsertCommand.
        command.Parameters.Add("@Name", SqlDbType.NVarChar, 50, "Name");
        command.Parameters.Add("@Age", SqlDbType.Int, 4, "Age");
        command.Parameters.Add("@Gender", SqlDbType.NVarChar, 6, "Gender");
        command.Parameters.Add("@ImportantInfo", SqlDbType.NVarChar, 100, "ImportantInfo");

        foreach (Animal animal in animals)
        {
            DataRow row = dataset.Tables["Guests"].NewRow();
            row["Name"] = animal.Name;
            row["Age"] = animal.Age;
            row["Gender"] = animal.Gender;
            row["Info"] = animal.ImportantInfo;

            dataset.Tables["Guests"].Rows.Add(row);
        }
        new SqlCommandBuilder(adapter);
        adapter.Update(dataset.Tables["Guests"]);
        connection.Close();
    }
    catch
    {
        throw;
    }
}

Make sure to specify actual db types and sizes for db parameters.

Upvotes: 1

Related Questions