C# storing data in a database

Question

I made this method to store some data into my SQL database. Does anybody know what is wrong or why it doesn't run?

public SqlConnection con = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=cdDB;Integrated Security=True;Pooling=False");
        public void GetData(string Title, string Artist, string Country, string Company, int Price, int Year)
        {
            con.Open();
            int max = 0;
            SqlDataAdapter da = new SqlDataAdapter("SELECT ID FROM CD", con);
            DataSet ds = new DataSet();
            da.Fill(ds, "CD");
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                DataRow dr = ds.Tables[0].Rows[i];
                int nr = int.Parse(dr.ItemArray[0].ToString());
                if (nr > max)
                {
                    max = nr;
                }
            }
            int id = max + 1;
            SqlCommand cmd = new SqlCommand("INSERT INTO CD VALUES(" + id + "," + Title + " ," + Artist + "," + Country + "," + Company + "," + Price + ", " + Year + ")", con);
            cmd.ExecuteNonQuery();
            con.Close();
        }

Answer 1

This part should be:

SqlCommand cmd = new SqlCommand("INSERT INTO CD VALUES(" + id + "," + Title + " ," + Artist + "," + Country + "," + Company +
"," + Price + ", " + Year + ")", con);

SqlCommand cmd = new SqlCommand(@"INSERT INTO CD(id, Title, Artist, Country, Company, Price, Year) 
      VALUES(@id, @Title, @Artist, @Country, @Company, @Price, @Year)", con);
  cmd.Parameters.Add("@id", SqlDbType.Int).Value ) = id;
  cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 40).Value ) = Artist

and so on for remaining parameters.

Important points:

• Always specify a column list with INSERT, or else the code will break if someone adds a column to the table, even if that column is nullable.
• Use parameterised statements. That is simpler than trying to concatenate a query string. It also removes the risk for SQL injection. And the code will work even if you own albums by Gilbert O'Sullivan. Finally, it utilises the query cache in SQL Server a lot better.
• When building the parameters you need to specify the data types and for the strings also specify length. I had to make a guess here. You may need to adjust.

Does anybody know what is wrong or why it doesn't run?

Next time you ask a question of this kind, please specify what happens. That is, tell us what happens when you try. Do you get unexpected results? If you get an error message include it.

Answer 2

Try this:

public frmCDVerwaltung()
{
InitializeComponent();
sqlDataAdapter1.Fill(dataSet11, "CD");
}

    private void btnRecreate_Click(object sender, EventArgs e)  
    {  
        if (openFileDialog1.ShowDialog() == DialogResult.OK)  
        {  
            dataSet11.Clear();  
            dataSet11.ReadXml(openFileDialog1.FileName);  
            clearDatabase();  
            updateDatabase();  
        }  
    }  

    private void btnUpdate_Click(object sender, EventArgs e)  
    {  
        dataSet11.Clear();  
        sqlDataAdapter1.Fill(dataSet11, "CD");  
    }  

    private void btnDelete_Click(object sender, EventArgs e)  
    {  
        if (dataGridView1.SelectedRows != null)  
        {  
            foreach (DataGridViewRow dr in dataGridView1.SelectedRows)  
            {  
                dataGridView1.Rows.Remove(dr);  
            }  
            updateDatabase();  
        }  
    }  

    private void clearDatabase()  
    {  
        SqlCommand cmd = new SqlCommand("delete from CD where 1 = 1", sqlConnection1);  
        sqlConnection1.Open();  

        cmd.ExecuteNonQuery();  

        sqlConnection1.Close();  
    }  

    private void dgvCD_CellEndEdit(object sender, DataGridViewCellEventArgs e)  
    {  
        updateDatabase();          
    }  

    private void updateDatabase()  
	{  
        sqlDataAdapter1.Update(dataSet11, "CD");  
    }  

	private void dgvCD_CellEnter(object sender, DataGridViewCellEventArgs e)  
	{  
        updateDatabase();  
    }  
}

Answer 3

You forgot the inverted comma in the line:
SqlCommand cmd = new SqlCommand("INSERT INTO CD VALUES(" + id + "," + Title + " ," + Artist + "," + Country + "," + Company + "," + Price + ", " + Year + ")", con);
It should look like this:
SqlCommand cmd = new SqlCommand("INSERT INTO CD VALUES('" + id + "','" + Title + "' ,'" + Artist + "','" + Country + "','" + Company + "','" + Price + "',' " + Year + "')", con);

Answer 4

What version of SqlClient do you use? Did you get an error message telling you: "... The certificate chain was issued by an authority that is not trusted?"

Because, starting with version 4.0 they modified the connection parameters by changing the default value of Encrypt from 'false' to 'true' and by the way broke many scenarios, esp. on Developer machines, lacking any validated certificate for encryption.

So, if you are using a newer version of SqlClient and are not using a validated certificate, your connection string could get you in trouble, because it implies that the default value of Encrypt is set to 'true'.

If you're still using an older version of SqlClient or a fully validated certificate for encryption you can happily ignore this issue, otherwise you might take a look at it.