C# windows form button to execute SQL Query

Question

I'm new to programming so please bear with me as I learn. I've been looking for days for a way to make this work, trying different solutions that I have found on here and other sites. I am using user input to create my connection string, and button 1 works great to verify that a connection has been established, button 2 not so much. I am trying to create a button that once pushed will execute a SQL command and provide the results from the command.

This is what I have so far, its button 2 that I have not been able to get to work yet.

using Microsoft.AspNet.SignalR.Infrastructure;
using Microsoft.SqlServer.Server;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace WindowsFormsApp4
{
   public partial class Form1 : Form
   {
       public Form1()
       {
           InitializeComponent();
       }

       private void Form1_Load(object sender, EventArgs e)
       {

       }

       private void textBox1_TextChanged(object sender, EventArgs e)
       {

       }

       private void textBox2_TextChanged(object sender, EventArgs e)
       {

       }

       private void textBox3_TextChanged(object sender, EventArgs e)
       {

       }
       private void textBox4_TextChanged(object sender, EventArgs e)
       {

       }
       private void textBox5_TextChanged(object sender, EventArgs e)
       {

       }

       private void button1_Click(object sender, EventArgs e)
       {
           {
               string ServerName = textBox1.Text;
               string Database = textBox2.Text;
               string Username = textBox3.Text;
               string Pass = textBox4.Text;

               string connetionString;
               SqlConnection cnn;
               connetionString = @"Data Source= " + ServerName + ";Initial Catalog= " + Database + ";User ID=" + Username + ";Password= " + Pass + ";";
               cnn = new SqlConnection(connetionString);
               try
               {
                   cnn.Open();
                   MessageBox.Show("Connection Open  !");
                   cnn.Close();
               }
               catch (Exception) { MessageBox.Show("Login Failed, Information is Incorrect"); }
           }
       }

       private void button2_Click(object sender, EventArgs e)
       {
           string ServerName = textBox1.Text;
           string Database = textBox2.Text;
           string Username = textBox3.Text;
           string Pass = textBox4.Text;
           string results = textBox5.Text;

           string connetionString;
           SqlConnection cnn;
           connetionString = @"Data Source= " + ServerName + ";Initial Catalog= " + Database + ";User ID=" + Username + ";Password= " + Pass + ";";
           string userInput = "";
           var process = new Process();
           var startInfo = new ProcessStartInfo();
           startInfo.WindowStyle = ProcessWindowStyle.Hidden;
           startInfo.FileName = "cmd.exe";
           startInfo.Arguments = string.Format(@"Data Source= " + ServerName + ";Initial Catalog= " + Database + ";User ID=" + Username + ";Password= " + Pass + "; "" SELECT count(*) from participanthistory, SELECT count(*) from postransaction where communicated = 0, userInput);

           process.StartInfo = startInfo;
           process.Start();
       }

       }


   }

I am trying to get the button to run this sql:

select count(*) from history
select count(*) from results where communicated = 0

I can run the SQL Query in SSMS no problem its just getting it to launch from the GUI I'm creating.

Any help is greatly appreciated.

Answer 1

I wanted to say that I appreciate all the help and patience from everyone on here, I was able to get this resolved on another board and wanted to share the results encase someone else needs assistance with it.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace WindowsFormsApp4
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {

        }

        private void textBox3_TextChanged(object sender, EventArgs e)
        {

        }
        private void textBox4_TextChanged(object sender, EventArgs e)
        {

        }
        private void textBox5_TextChanged(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            {
                string ServerName = textBox1.Text;
                string Database = textBox2.Text;
                string Username = textBox3.Text;
                string Pass = textBox4.Text;

                string connetionString;
                SqlConnection cnn;
                connetionString = @"Data Source= " + ServerName + ";Initial Catalog= " + Database + ";User ID=" + Username + ";Password= " + Pass + ";";
                cnn = new SqlConnection(connetionString);
                try
                {
                    cnn.Open();
                    MessageBox.Show("Connection Open  !");
                    cnn.Close();
                }
                catch (Exception) { MessageBox.Show("Login Failed, Information is Incorrect"); }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string ServerName = textBox1.Text;
            string Database = textBox2.Text;
            string Username = textBox3.Text;
            string Pass = textBox4.Text;
            SqlConnection connection = new SqlConnection();

            connection.ConnectionString = @"Data Source= " + ServerName + ";Initial Catalog= " + Database + ";User ID=" + Username + ";Password= " + Pass + ";";

            SqlCommand command = new SqlCommand();

            command.Connection = connection;
            command.CommandText = "--your SQL query --";
            command.CommandType = CommandType.Text;

            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    int CommunicatedRecordsCount = (int)reader[0];
                    textBox5.Text = CommunicatedRecordsCount.ToString();
                }

                reader.Close();
            }
            catch
            {
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }
    }
}

Where I was looking for a numerical output we changed the output slightly, but again thanks everyone for all the assistance.

Answer 2

Sanitize that input!

Your click event code might look something like this:

    private void button2_Click(object sender, EventArgs e)
    {
        string ServerName = textBox1.Text;
        string Database = textBox2.Text;
        string Username = textBox3.Text;
        string Pass = textBox4.Text;
        //string results = textBox5.Text;

        using (var cnn = new SqlConnection($"Data Source= \"{ServerName}\";Initial Catalog= \"{Database}\";User ID=\"{Username}\";Password= \"{Pass}\";"))
        using (var cmd = cnn.CreateCommand())
        {
            cmd.CommandText = "-- Your query here --"; 

            try
            {
                cnn.Open();
            }
            catch (Exception ex)
            {
                // Error connecting to db.
            }

            if (cnn.State == System.Data.ConnectionState.Open)
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        // Read your data, row by row, here. ...or do something with textBox5
                    }
                }
            }
        }
    }

And because you are just getting counts, you may also skip the reader and do this instead...

if (cnn.State == System.Data.ConnectionState.Open)
{
    var count = cmd.ExecuteScalar();
    //count is an object, cast as needed.
}

Answer 3

Something like this, open the connection with the connection string the user imputed, then read the returned rows and their fields

SqlDataReader reader = null;
     using (SqlConnection connection = new SqlConnection(_sqlConnectionStringFromUserImput))
                    {
                        connection.Open();
                        if (connection.State == ConnectionState.Open)
                        {
                            SqlCommand sqlCommand =
                                new SqlCommand(
                                    "select count(*) from history",
                                    connection)
                                {
                                    CommandType = CommandType.Text,
                                    CommandTimeout = 20
                                };
                            reader = sqlCommand.ExecuteReader();
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    DateTime datetimefield = reader.GetFieldValue<DateTime>(0);
                                    string stringField = reader.GetFieldValue<string>(1);
                                }
                            }
                            reader.Close();
        }
                        connection.Close();
                    }