Inserting to a SQL database in C#

Question

I am trying to insert to a SQL database in my C# application.

I've read a bit of documentation, and came up with what I thought would work. In reality, what happens is that when the user has entered thier data and presses the submit button, the app freezes for a moment, then gives me a "SqlException", and mentions something about not being able to connect.

I am not sure I used the connection string correctly so I am asking for help.

These are the methods I used to build the query and make the connection:

private void btn_Submit_Click(object sender, EventArgs e)
{
    if (isValidData())
    {
        //MessageBox.Show("Valid", "All Entries Were Valid!");

        //CONVERT FORM VALUES AND STORE IN VARIABLES TO SEND TO MYSQL QUERY
        DateTime saleTime = saleDatePicker.Value;
        Decimal price = Convert.ToDecimal(txt_Price.Text);
        string customerName = txt_CustomerName.Text;
        string customerPhone = txt_CustomerPhone.Text;
        string description = rTxt_Description.Text;

        //Build Query string
        string query = "INSERT into SALES VALUES ('" + saleTime + "','" + 
            price + "','" + customerName  + "','" + customerPhone  + "','" + 
            description + "');";

        insertValues(query);

    }
}
private void insertValues(string q)
{
    SqlConnection sqlConnection1 = new SqlConnection("Server=host;Database=dbname;User Id=username;Password=password;");
    SqlCommand cmd = new SqlCommand();
    SqlDataReader reader;

    cmd.CommandText = q;
    cmd.CommandType = CommandType.Text;
    cmd.Connection = sqlConnection1;

    sqlConnection1.Open();

    reader = cmd.ExecuteReader();
    // Data is accessible through the DataReader object here.

    sqlConnection1.Close();
}

Answer 1

I am not sure about your connection string, but seeing that your question is tagged with MySql then you need to use different classes to 'talk' to MySql. The ones you are using now serve the purpose of working with Microsoft Sql Server.

You need to change that SqlConnection, SqlCommand and SqlDataReader to the MySql counterpart named MySqlConnection, MySqlCommand, MySqlDataReader. These classes are available after you download, and install the MySql NET/Connector, then set up a reference to the MySql.Data.Dll and add the using MySql.Data.MySqlClient; to your project

About the connectionstring for MySql you need also to follow the rules and use the keywords as explained in this site

These are the basic steps to give to your program the possibility to work, but you have a big problem here. It is called string concatenation in sql commands and this habit leads straight to the Sql Injection vulnerability.

You need to change your code to something like this:

private void btn_Submit_Click(object sender, EventArgs e)
{
    if (isValidData())
    {

        //CONVERT FORM VALUES AND STORE IN VARIABLES TO SEND TO MYSQL QUERY
        DateTime saleTime = saleDatePicker.Value;
        Decimal price = Convert.ToDecimal(txt_Price.Text);
        string customerName = txt_CustomerName.Text;
        string customerPhone = txt_CustomerPhone.Text;
        string description = rTxt_Description.Text;

        // Create the query using parameter placeholders, not the actual stringized values....
        string query = "INSERT into SALES VALUES (@stime, @price, @cname,@cphone,@cdesc)";

        // Create a list of parameters with the actual values with the placeholders names
        // Pay attention to the Size value for string parameters, you need to change it 
        // accordingly to your fields size on the database table.
        List<MySqlParameter> prms = new List<MySqlParameter>()
        {
            new MySqlParameter {ParameterName="@stime", MySqlDbType=MySqlDbType.DateTime, Value =  saleTime },
            new MySqlParameter {ParameterName="@price", MySqlDbType=MySqlDbType.Decimal, Value =  price },
            new MySqlParameter {ParameterName="@cname", MySqlDbType=MySqlDbType.VarChar, Value =  customerName, Size = 150 },
            new MySqlParameter {ParameterName="@cphone", MySqlDbType=MySqlDbType.VarChar, Value = customerPhone , Size = 150 },
            new MySqlParameter {ParameterName="@desc", MySqlDbType=MySqlDbType.VarChar, Value = description , Size = 150 }
        };

        // Pass query and parameters to the insertion method.
        // get the return value. if it is more than zero you are ok..
        int result = insertValues(query, prms);
        // if(result > 0)
        //    .... insertion ok ....
    }
}

private int insertValues(string q, List<MySqlParameter> parameters)
{
    using(MySqlConnection con = new MySqlConnection(....))
    using(MySqlCommand cmd = new MySqlCommand(q, con))
    {
        con.Open();
        cmd.Parameters.AddRange(parameters.ToArray());
        int rowsInserted = cmd.ExecuteNonQuery();
        return rowsInserted;
    }
}