Retrieving data from SQL DB based on user input

Question

I wrote this to connect to a local DB using my local login credentials. It pulls back card information based on the user's input.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace SQL_Console{
public class Info
{

//SQL Sever connection

    public static string connstring = @"Data Source=localhost\MAYDAY;Initial Catalog=NV; Integrated Security=SSPI; Connection Timeout=5";

    public static string ConnectionString
    {
        get
        {
            return ConnectionString;
        }
        set
        {
            connstring = value;
        }
    }
}

public class Lookup
{

//SQL Query

        public static string squery = "Select Cardnumber, CardHolder, ExpireDate from CreditCardTransactionHistory where clientnumber = (select clientnumber from foliolink where folionumber = @fnumber)";

}

//Execution

    class Program
{
    static void Main(string[] args)
    {
        string sqlconn = Info.connstring;


        Console.WriteLine("Enter Folio Number:");
        int fnum = Convert.ToInt32(Console.ReadLine());

        Console.Clear();


        SqlConnection sqlcon1 = new SqlConnection(sqlconn);
        sqlcon1.Open();

        SqlCommand sql1 = new SqlCommand(Lookup.squery, (sqlcon1));

        sql1.Parameters.AddWithValue("@fnumber", fnum);
        SqlDataReader reader = sql1.ExecuteReader();

        while (reader.Read())
        {
            Console.Write("Card Number: {0}\nCard Holder: {1}\nExpiration: {2}\n\n", reader[0], reader[1], reader[2]);


        }
        Console.ReadLine();
        sqlcon1.Close();
    }


}
}

Answer 1

I don't get using class with static properties to store some strings.

Use Using for class that dispose:

public static void SimpleDB()
{
    string sqlconn = @"Data Source=localhost\MAYDAY;Initial Catalog=NV; Integrated Security=SSPI; Connection Timeout=5";
    string query = "Select Cardnumber, CardHolder, ExpireDate " + Environment.NewLine +
                    "from CreditCardTransactionHistory " + Environment.NewLine + 
                    "where clientnumber = (select clientnumber from foliolink where folionumber = @fnumber)";

    Console.WriteLine("Enter Folio Number:");
    int fnum = Convert.ToInt32(Console.ReadLine());
    Console.Clear();

    using (SqlConnection sqlcon1 = new SqlConnection(sqlconn))
    {
        sqlcon1.Open();
        using (SqlCommand sql1 = new SqlCommand(query, (sqlcon1)))
        {
            sql1.Parameters.AddWithValue("@fnumber", fnum);
            using (SqlDataReader reader = sql1.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Console.Write("Card Number: {0}\nCard Holder: {1}\nExpiration: {2}\n\n", reader[0], reader[1], reader[2]);
                    }
                }
                else
                {
                    Console.WriteLine("no results");
                }
            }
        }
    }
    Console.ReadLine();
} 

Answer 2

I don't get the point of the ConnectionString class. Just about any example WRT writing db access code will use a connection string taken from a .config class; why aren't you following that recommendation?

---

Items like SqlConnection, SqlCommand, SqlDataReader should always be used in a using block in order to properly dispose of their resources. Look at examples like this one, or check this question on SO.

---

If you're going to place your SQL code in a separate class, I'd prefer it to be an embedded .sql file which you can then retrieve via code like this:

public static string GetQuery(string scriptName, string namespacePrefix)
{
    var resourceName = namespacePrefix + scriptName;
    var assembly = Assembly.GetExecutingAssembly();

    using (var stream = assembly.GetManifestResourceStream(resourceName))
    {
        if (stream == null)
        {
            throw new Exception($"Missing template: {resourceName}");
        }

        using (var streamReader = new StreamReader(stream))
        {
            return streamReader.ReadToEnd();
        }
    }
}

That way you'd at least get some IntelliSense for the SQL code.

---

Don't use AddWithValue().

---

On a more general note: avoid writing ADO.NET code by hand. Avoid using SqlDataReader etc. that require you to translate its results into your own custom classes and instead use one of the many tools that handles that for you. These days the vast majority of professional code is written using an ORM like Entity Framework. Even using a Micro ORM like Dapper would be much preferred.