How to insert data to the database.mdf by using SqlConnection in WPF

Question

private void Save_Button_Click(object sender, RoutedEventArgs e)
        {
            string student_name = stunamebox.Text;
            string student_nrc = stunrcbox.Text;
            DateTime dob = studobbox.SelectedDate.Value;
            string date_of_birth = dob.Month + "/" + dob.Day + "/" + dob.Year;
            string grade = gradecombo.Text;
            string father_name = fatnamebox.Text;
            string mother_name = motnamebox.Text;
            string father_nrc = fatnrcbox.Text;
            string mother_nrc = motnrcbox.Text;
            string contact_no = contactbox.Text;
            string address = addbox.Text;
            string gender = "";
            if (Rmale.IsChecked == true)
            {
                gender = "Male";
            }
            else if (Rfemale.IsChecked == true)
            {
                gender = "Female";
            }
            string study_year = DateTime.Now.Year + "-" + (DateTime.Now.Year + 1);

            int age=(DateTime.Now.Year)-(dob.Year);
            SqlConnection myConnection = new SqlConnection(@"Data Source=(LocalDB)\v11.0;Database=" + path + ";Integrated Security=True");

            try
            {
                myConnection.Open();

                string comm = "INSERT Stuident_Info (Name,Grade,Date_Of_Birth,Contact_number,Age,Contact_Address,Father_name,Mother_name,Father_NRC,Mother_NRC,Student_NRC,Study_year)" + "VALUES('" + student_name + "','" + grade + "','" + date_of_birth +
                    "','" + contact_no + "'," + age.ToString() + ",'"+address+
                    "','"+father_name+"','"+mother_name+"','"+father_nrc+"','"+mother_nrc+"','"+student_nrc+"','"+study_year+"')";

                SqlCommand sqlcommand = new SqlCommand(comm, myConnection);
                sqlcommand.ExecuteNonQuery();
                MessageBox.Show("Saving complete", "ok message", MessageBoxButton.OK, MessageBoxImage.Information);
                myConnection.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }


        }

[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection — marc_s, Jul 24 '15 at 04:59

score 0 · Answer 1 · answered Jul 24 '15 at 05:05

You should definitely use parametrized queries instead of concatenating together your SQL statements!

Try something like this:

private void Save_Button_Click(object sender, RoutedEventArgs e)
{
    // get the values from the GUI
    string student_name = stunamebox.Text;
    string student_nrc = stunrcbox.Text;

    DateTime dob = studobbox.SelectedDate.Value;

    string grade = gradecombo.Text;
    string father_name = fatnamebox.Text;
    string mother_name = motnamebox.Text;
    string father_nrc = fatnrcbox.Text;
    string mother_nrc = motnrcbox.Text;
    string contact_no = contactbox.Text;
    string address = addbox.Text;
    string gender = "";

    if (Rmale.IsChecked == true)
    {
        gender = "Male";
    }
    else if (Rfemale.IsChecked == true)
    {
        gender = "Female";
    }

    string study_year = DateTime.Now.Year + "-" + (DateTime.Now.Year + 1);

    int age = (DateTime.Now.Year) - (dob.Year);

    // define your connection string (usually from a config file)
    // and your INSERT query as strings
    string connectionString = @"Data Source=(LocalDB)\v11.0;Database=" + path + ";Integrated Security=True";

    string insertQuery = "INSERT INTO dbo.Student_Info (Name, Grade, Date_Of_Birth, Contact_number, Age, Contact_Address, Father_name, Mother_name, Father_NRC, Mother_NRC, Student_NRC, Study_year) " + 
                         "VALUES(@Name, @Grade, @Date_Of_Birth, @Contact_number, @Age, @Contact_Address, @Father_name, @Mother_name, @Father_NRC, @Mother_NRC, @Student_NRC, @Study_year)";

    // set up your connection and command inside "using" blocks
    using (SqlConnection myConnection = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand(insertQuery, myConnection))
    {
        // define your parameters and set values
        cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100).Value = Name;
        cmd.Parameters.Add("@Grade", SqlDbType.VarChar, 20).Value = Grade;
        cmd.Parameters.Add("@Date_of_Birth", SqlDbType.DateTime).Value = dob; // use the DateTime value directly!
        // ..... and so on, until you've defined all parameters

        try
        {
            // open connection, execute query, close connection
            myConnection.Open();

            cmd.ExecuteNonQuery();
            myConnection.Close();

            MessageBox.Show("Saving complete", "ok message", MessageBoxButton.OK, MessageBoxImage.Information);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
}

How to insert data to the database.mdf by using SqlConnection in WPF

1 Answers1