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();
}

enter image description here

Is 'host' the name of your server (the PC name where the Sql Server is installed) ? — Steve
– Steve, Commented Dec 5, 2014 at 8:32
no, that was just for me to censor where it is. The host is a godaddy server, in the form something.db.11420661.hostedresource.com — user2962806
– user2962806, Commented Dec 5, 2014 at 8:35
we need the actual exception, please post this and i'll check it out. — krystan honour
– krystan honour, Commented Dec 5, 2014 at 8:37
@user2962806 "mentions something about not being able to connect" What is the stacktrace of exception? Consider also that you're using classes of .NET framework explicitly designed only for SQL Server database engine. To connect to a MySQL engine, you have to consider other classes and libraries. — Alberto Solano
– Alberto Solano, Commented Dec 5, 2014 at 8:39

Community · Accepted Answer · 2017-05-23 12:06:23Z

6

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;
    }
}

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Dec 5, 2014 at 8:36

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2962806 Over a year ago

Ok. Now this seems the right direction. I downloaded and installed the connector. Where do I refer to the DLL?

krystan honour Over a year ago

can be installed with nuget nuget.org/packages?q=Tags%3A%22Connector%2FNET%22

user2962806 Over a year ago

Thanks very much. This was certainly the problem, and now my rows insert correctly. Thanks again.

Steve Over a year ago

Please take a look at the revised code, there are numerous thing that you can use to make your code safer and better.

krystan honour Over a year ago

great answer removed mine as this is the best by far

Collectives™ on Stack Overflow

Inserting to a SQL database in C#

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related