C#: Execute a query from Windows Forms using TextBoxes and a Button

Question

How I can change my code, so I can search for specific columns of my table?

I want to write the request's column values in TextBoxes, and then execute the request when I press a Button.

private async void button26_Click(object sender, EventArgs e)
{
    if (label77.Visible) label77.Visible = false;
    if (!string.IsNullOrEmpty(textBox62.Text) && !string.IsNullOrEmpty(textBox62.Text))
    {
        SqlCommand command = new SqlCommand("INSERT INTO [Policlinic] ('id_Policlinic', 'Name', 'Address', 'Phone') VALUES (" + textBox62 + ", '" + textBox62 + "', '" + textBox62 + "','" + textBox62 + "')", sqlConnection);
        command.Parameters.Add("@id_Policlinic", SqlDbType.Int); command.Parameters["@id_Policlinic"].Value = Convert.ToInt32(textBox62.Text, 4);
        command.Parameters.AddWithValue("Name", textBox62.Text);
        command.Parameters.AddWithValue("Address", textBox62.Text);
        command.Parameters.AddWithValue("Phone", textBox62.Text);
        await command.ExecuteNonQueryAsync();
    }
    else
    {
        label77.Visible = true;
        label77.Text = "Поля должны быть заполнены!";
    }
}

Any thoughts?

Answer 1

Try this:

private async void button26_Click(object sender, EventArgs e)
{
    //why check the SAME textbox twice?
    // You should give MEANINGFUL NAMES to your controls, rather than leaving them at the default
    if (string.IsNullOrEmpty(textBox62.Text) || string.IsNullOrEmpty(textBox62.Text)) 
    {
        label77.Visible = true;
        label77.Text = "Поля должны быть заполнены!";
        return;
    }
    label77.Visible = false;

    string sql = "INSERT INTO [Policlinic] (Name, Address, Phone) VALUES ( @Name, @Address, @Phone);";

    using (var con = new SqlConnection("connection string here"))
    using (var cmd = new SqlCommand(sql, con))
    {  
        //Use exact database column types and lengths here
        // DON'T trust ADO.Net to guess these types correctly.
        cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 20).Value = textBox62.Text;
        cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 80).Value =  textBox62.Text;
        cmd.Parameters.Add("@Phone", SqlDbType.NVarChar, 14).Value =  textBox62.Text;

        con.Open()
        await cmd.ExecuteNonQueryAsync();
    }
}

There are a number of important changes here:

1. Do NOT try to re-use the same SqlConnection object throughout your application. This interferes with the built-in connection pooling.
2. Do NOT trust ADO.Net to guess your parameter types and lengths.
3. DO rely on using blocks to the make sure your connection object is disposed, even if an exception is thrown.

If you're still learning how to do this stuff, I also recommend removing async/await for the time being, and just calling ExecuteNonQuery(). Add that stuff back after you have it working in the "normal" way.

Answer 2

I tried to summarize what you should do in the code below to run this query.

Just remember to replace that textBox62 by something else more suitable.

Make sure you have the right sever address and the right credentials before creating the connection.

After executing the query, you should not keep the connection open for a long time (read this).

private void button26_Click(object sender, EventArgs e)
{
    if (label77.Visible) label77.Visible = false;

    if (string.IsNullOrEmpty(textBox62.Text))
    {
        label77.Visible = true;
        label77.Text = "Поля должны быть заполнены!";
        return;
    }

    SqlConnection connection = null;
    SqlCommand command = null;
    try
    {
        // First create a SQL Connection:
        string connectionString = $"Data Source=127.0.0.1;Initial Catalog=MyDatabaseName";
        Credential credential = new SqlCredential("myusername", "mypassword");
        connection = new SqlConnection(connectionString, credential);
        connection.Open();

        // Then create the SQL command:
        command = connection.CreateCommand();
        command.CommandType = CommandType.Text;
        command.CommandText = "INSERT INTO dbo.[Policlinic] (Name, Address, Phone) VALUES (@Name, @Address, @Phone)";

        // Add '@' in front of all column names:
        command.Parameters.Add(new SqlParameter("@Name", textBox62.Text, 60)
        {
            SqlDbType = SqlDbType.NVarChar,
            Direction = ParameterDirection.Input
        });
        command.Parameters.Add(new SqlParameter("@Address", textBox62.Text, 120)
        {
            SqlDbType = SqlDbType.NVarChar,
            Direction = ParameterDirection.Input
        });
        command.Parameters.Add(new SqlParameter("@Phone", textBox62.Text, 20)
        {
            SqlDbType = SqlDbType.NVarChar,
            Direction = ParameterDirection.Input
        });

        // Execute command:
        Task.Run(() => command.ExecuteNonQuery()).Wait();

        // Parse any results here...
    }
    catch (Exception ex)
    {
        if (ex.InnerException != null) ex = ex.InnerException;
        MessageBox.Show(this, ex.ToString(), "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        // Now cleanup this mess:
        command?.Dispose();
        connection?.Close();
        connection?.Dispose();
    }
}

Let me know the error message in case you're still in trouble executing that.