SQL Server Update query for two tables

lyr7nygr  于 2023-08-02  发布在  其他
关注(0)|答案(1)|浏览(100)
private void btnupdate_Click(object sender, EventArgs e)
{
    SqlConnection connection = DBConnectivity.getConnection();

    try
    {
        string query = "UPDATE Student SET RegNo ='" + txtRegNo.Text + "',StudName='" + txtStudentName.Text + "',DateAdd='" + DateTime.Now + "',DOB='" + DateTime.Now + "',Age='" + txtAge.Text + "',Gender='" + cBGender.Text + "',PrAddress='" + txtAddress.Text + "',PeAddress='" + txtPaddress.Text + "',FName='" + txtFName.Text + "',FMobile='" + mtxtFmobile.Text + "',FOccupation='" + txtFOccupation.Text + "',MName='" + txtMName.Text + "',MOccupation='" + txtMOccupation.Text + "',Nationality= '" + ComboBox3.Text + "',Area='" + ComboBox1.Text + "',BPlace='" + TextBox12.Text + "',Religion='" + TextBox13.Text + "',AdmitedTo='" + cBClass.Text + "' ,RollNo ='" + txtRollNo.Text + "',CNIC='" + mtxtCNIC.Text + "',Mobile='" + mtxtMobileStud.Text + "' where id='" + txtid.Text + "'";

        SqlCommand command = DBConnectivity.getCommandForQuery(query, connection);

        int result1 = command.ExecuteNonQuery();

        if (pho == 1)
        {
            string query2 = " Update Photo SET PID='"+pho+"', SID='"+txtRegNo.Text+"', SName='"+txtStudentName.Text+"', StudImage='"+Photos1+"' Where id='"+txtid.Text+"' ";

            Photos1 = System.IO.File.ReadAllBytes(OpenFileDialog1.FileName);

            SqlCommand command2 = DBConnectivity.getCommandForQuery(query2, connection);
            int result2 = command2.ExecuteNonQuery();
        }
    }
    catch (Exception ex) 
    { }
}

This is the table of data, but the table with the student is updated, but the photo is not there.

gcmastyq

gcmastyq1#

Just combine them into the same SQL batch, checking to see if the photo has been passed in or is null.

  • Do not use dangerous SQL injection. Use proper parameterization instead.
  • Don't read the whole file as bytes. Open it as a stream, and pass that into the parameter. Pass DbNull.Value if you don't have a value.
  • Use using to dispose the connection and command.
  • Do not store the same data in multiple tables. Each table is a single source of truth for a fact (column).
  • Do not just swallow exceptions. Catch them at the top-level (ie directly in the UI handler and return an error message to the user.
  • Conside using async to make your UI more responsive.
private async void btnupdate_Click(object sender, EventArgs e)
{
    try
    {
        const string query = @"
UPDATE Student
SET RegNo = @RegNo,
    StudName = @StudentName,
    DateAdd = GETDATE(),
    DOB = @DOB,
    Gender = @Gender,
    -- etc
where id = @id;

IF @Photo IS NOT NULL
    UPDATE Photo
    SET StudImage = @Photo
    WHERE id = @id;
";

        using var connection = DBConnectivity.getConnection();
        using var command = new SqlCommand(query, connection);
        command.Parameters.Add("@RegNo", SqlDbType.BigInt).Value = int.Parse(txtRegNo.Text);
        command.Parameters.Add("@StudentName", SqlDbType.NVarChar, 100).Value = txtStudentName.Text;
        command.Parameters.Add("@DOB", SqlDbType.Date).Value = DateOnly.Parse(txtDOB.Text).ToDateTime();
        command.Parameters.Add("@Gender", SqlDbType.Char, 1).Value = cBGender.Text;
        // etc

        using var stream = pho == 1
            ? File.OpenRead(OpenFileDialog1.FileName)
            : null;
        command.Parameters.Add("@Photo", SqlDbType.VarBinary, -1) = stream ?? (object) DBNull.Value;
        await connection.OpenAsync();
        await command.ExecuteNonQueryAsync();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

It;s not entirely clear if the Photo actually needs to be inserted. If so, do

UPDATE Student
SET RegNo = @RegNo,
    StudName = @StudentName,
    DateAdd = GETDATE(),
    DOB = @DOB,
    Gender = @Gender,
    -- etc
where id = @id;

IF @Photo IS NOT NULL
    INSERT Photo(id, StudImage)
    VALUES (@id, @Photo);

相关问题