SqlCommand的执行查询更新不及时删除和插入(sqlcommand execute query

你好，我一直使用的SqlCommand非查询，但现在事情错了，我不知道我有业务更新插入3个按钮和删除，但我创造了独特的方法对所有3个操作，问题是，它不会插入删除或更新：

private void operacao(String operacao) {
        String comando = "";
        con = new SqlConnection();
        WorksDataSet  dataset = new WorksDataSet();
        con.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Works.mdf;Integrated Security=True;User Instance=True;Asynchronous Processing=true";
        try
        {
            con.Open();

        }
        catch (SqlException cox) {
            MessageBox.Show(cox.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        switch (operacao) { 
            case "inserir":

                try
                {
                    comando = "Insert Into Estudante (Codigo,Nome,Apelido) values(" + txtID.Text + ",'" + txtNome.Text + "','" + txtapelido.Text + "')";
                    SqlCommand command = new SqlCommand(comando, con);
                    SqlDataAdapter sda=new SqlDataAdapter(command);
                    command.CommandType = CommandType.Text;
                    sda.Fill(dataset);
                    command.ExecuteNonQuery();
                    command.Dispose();
                    MessageBox.Show("Adicionado com Sucesso", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (SqlException sex) {
                    MessageBox.Show(sex.Message , this.Text,MessageBoxButtons.OK,MessageBoxIcon.Error );
                }

                break;

            case "apagar":
                comando = "delete from Estudante where Codigo=" + txtID;
                try
                {

                    SqlCommand command = new SqlCommand(comando, con);
                    command.BeginExecuteNonQuery();
                    MessageBox.Show("Removido com Sucesso", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (SqlException sex)
                {
                    MessageBox.Show(sex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                break;
            case "atualizar":
                comando = "update table Estudante set nome='" + txtNome + "'^ apelido='" + txtapelido + "'";
                try
                {

                    SqlCommand command = new SqlCommand(comando, con);
                    command.BeginExecuteNonQuery();
                    MessageBox.Show("Actualizado com Sucesso", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (SqlException sex)
                {
                    MessageBox.Show(sex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                break;
            default:
                break
                ;
        }
        con.Close();
    }

Answer 1:

您应该使用参数化查询。总是.....

本作的OP插入。

comando = "Insert Into Estudante (Codigo,Nome,Apelido) values(@id, @nome, @apelido");
SqlCommand command = new SqlCommand(comando, con);                     
command.Parameters.AddWithValue("@id", txtID.Text);
command.Parameters.AddWithValue("@nome", txtNome.Text);
command.Parameters.AddWithValue("@apelido", txtapelido.Text);
command.CommandType = CommandType.Text;                     
command.ExecuteNonQuery();

无需使用数据集或这里一个DataAdapter。刚刚为ExecuteNonQuery

这对于删除接线员。

comando = "delete from Estudante where Codigo=@id";
SqlCommand command = new SqlCommand(comando, con);
command.Parameters.AddWithValue("@id", txtID.Text);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();

请注意，你应该通过Text属性，而不是整个文本框

本作的OP更新

comando = "update table Estudante set nome=@nome, apelido=@apelido where codigo=@id";
SqlCommand command = new SqlCommand(comando, con);
command.Parameters.AddWithValue("@id", txtID.Text);
command.Parameters.AddWithValue("@nome", txtNome.Text);
command.Parameters.AddWithValue("@apelido", txtapelido.Text);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();

同样，此处使用Text属性不是文本框对象

通过这种方式，你不必担心你的字串PARAMS报价和你关门
SQL注入攻击

Answer 2:

要执行insert/delete/update语句，你只需要创建SqlCommand和SqlConnection对象。 DataSet和DataAdapter是无用的。

要插入一行：

string cnstr=@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Works.mdf;Integrated Security=True;User Instance=True;Asynchronous Processing=true";

using(SqlConnection con = new SqlConnection(cnstr))
 {
  string sql = "Insert Into Estudante (Codigo,Nome,Apelido) values(@Codigo,@Nome,@Apelido)";
  using(SqlCommand command= new SqlCommand(sql,con))
   {
     command.Parameters.Add("@Codigo",SqlDbType.Int).Value=txtID.Text;
     command.Parameters.Add("@Nome",SqlDbType.VarChar,30).Value=txtNome.Text;
     command.Parameters.Add("@Apelido",SqlDbType.VarChar,30).Value=txtapelido.Text;
     con.Open();
     cmd.ExecuteNonQuery();
     con.Close();
    }
 }