C# sending select command from one class to another and return result?

Question

I am beginner in C#, so this question may be a bit stupid but...

I would like to send some SQL command as string from one class to another, execute and then return result to the first class.

    class MainWindow
private void btnLogin_Click(object sender, RoutedEventArgs e)
        {

            TestConnection checkLogin = new TestConnection();
            checkLogin.SimpleQuery("SELECT Name FROM Names WHERE ID='1'", "");

            //MessageBox .Show(checkLogin.SimpleQuery(response:ToString));
        }

And class TestConnection

  public string SimpleQuery(string request, string response)
    {


        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = "Server=SQLOLEDB.1;User ID=" +
                Constants.DATABASE_USERNAME + ";Password=" +
                Constants.DATABASE_PASSWORD + ";Initial Catalog=" +
                Constants.DATABASE_CATALOG + ";Data Source=" +
                Constants.SERVER_ADRESS;

            SqlCommand command = new SqlCommand(request, conn);

            {
                conn.Open();
                response = Convert.ToString (command.ExecuteScalar());
                conn.Close();
                return response; 
            }

        }

Is something like this even an OK idea? I am learning and I am testing ideas..

Thank you!

Answer 1

The thing is, usually you want your classes to encapsulate functionality.

In your case, it makes more sense if you keep the query in the object and expose a method which name correspond with the actual functionality, e.g.:

//your class, also: the name must describe it's reason for existence
public class UserRepository { //previously known as TestConnection

//the method name explains its function.
//the query is stored within the function
//therefore the functionality is encapsulated
public string CheckLogin(int id)
{
    //note: tricky: almost SQL injection here: that must be fixed.
    //I just left it here so you can see the basic idea
    var request = "SELECT Name FROM Names WHERE ID ="  + id.ToString();

    //another note: response doesn't have to be passed as parameter.
    var response = string.Empty;
    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = "Server=SQLOLEDB.1;User ID=" +
            Constants.DATABASE_USERNAME + ";Password=" +
            Constants.DATABASE_PASSWORD + ";Initial Catalog=" +
            Constants.DATABASE_CATALOG + ";Data Source=" +
            Constants.SERVER_ADRESS;

        SqlCommand command = new SqlCommand(request, conn);
        {
            conn.Open();
            response = Convert.ToString (command.ExecuteScalar());
            conn.Close();
            return response; 
        }
    }

There are some further enhancements which can be made, but for now I think this will give you enough to think about.

---

SQL injection issue (see comment)

To prevent SQL injection, a good approach is to use parameters. There is an article about it here.

Basically it come down on using parameterized inputs:

disclaimer: copied from link:

 using (SqlCommand command = 
              new SqlCommand("SELECT * FROM Dogs1 WHERE Name LIKE @Name", connection))
 {
     // Add new SqlParameter to the command.
     command.Parameters.Add(new SqlParameter("Name", dogName));

For more information about how and why, see this link.

Answer 2

Here is a working example with fixes for the non parameterized sql and suggestions on how to better store the connection string.

MainWindow.cs

class MainWindow
{
    private void btnLogin_Click(object sender, RoutedEventArgs e)
    {
        SomeRepository repo = new SomeRepository();
        var userName = repo.GetUserName(1);
        MessageBox.Show(userName ?? "User not found!");
    }
}

SomeRepository.cs

public sealed class SomeRepository
{
    private readonly string connectionString;
    public SomeRepository()
    {
      // the ideal location for a connection string is in the application's app.config (or web.confic)
      connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;

      // Or uncomment this
      // connectionString = "Server=SQLOLEDB.1;User ID=" +
        //      Constants.DATABASE_USERNAME + ";Password=" +
        //      Constants.DATABASE_PASSWORD + ";Initial Catalog=" +
        //      Constants.DATABASE_CATALOG + ";Data Source=" +
        //      Constants.SERVER_ADRESS;
    }

    public string GetUserName(int id)
    {
        const string sqlRequest = "SELECT Name FROM Names WHERE ID = @id";

        using (SqlConnection conn = new SqlConnection(this.connectionString))
        using (SqlCommand command = new SqlCommand(sqlRequest, conn))
        {
            // if this is an integer in the schema, which it looks like it should be, then you need to pass it as an int and not a string
            command.Parameters.Add("@id", SqlDbType.Int).Value = id;

            // if it is a string then specify the type as varchar and specify the varchar length in the schema and pass a string
            // command.Parameters.Add("@id", SqlDbType.VarChar, 20).Value = id.ToString();

            conn.Open();
            return command.ExecuteScalar()?.ToString();
        }
    }
}

app.config

<?xml version="1.0"?>
<configuration>

  <connectionStrings>
    <add name="MyConnection" connectionString="YOUR CONNECTION STRING HERE" providerName="System.Data.SqlClient"/>
  </connectionStrings>

</configuration>

Answer 3

You have to store your return result from SimpleQuery() to finally show this in a MessageBox.

    private void btnLogin_Click(object sender, RoutedEventArgs e)
    {
        TestConnection checkLogin = new TestConnection();
        string result = checkLogin.SimpleQuery("SELECT Name FROM Names WHERE ID='1'", "");

        MessageBox.Show(result);
    }

Alter your method to return the result:

public string SimpleQuery(string request, string response)
{
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = "Server=SQLOLEDB.1;User ID=" +
                Constants.DATABASE_USERNAME + ";Password=" +
                Constants.DATABASE_PASSWORD + ";Initial Catalog=" +
                Constants.DATABASE_CATALOG + ";Data Source=" +
                Constants.SERVER_ADRESS;

            SqlCommand command = new SqlCommand(request, conn);

            conn.Open();
            string response = Convert.ToString(command.ExecuteScalar());
            conn.Close();

            return response; 
        }
}