3
\$\begingroup\$

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();
    }


}
}
\$\endgroup\$
0

2 Answers 2

2
\$\begingroup\$

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();
} 
\$\endgroup\$
1
  • \$\begingroup\$ Thank you for the notes. This was my first C# program after dabbling in VB.net. I will definitely use in future SLQ connected applications. \$\endgroup\$
    – Das Nuk
    Commented Feb 19, 2018 at 18:54
5
\$\begingroup\$

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.

\$\endgroup\$
1
  • \$\begingroup\$ This was pretty much my very first C# program after getting my feet wet with VB.net. Thanks for the heads up on using ORM, I'll look into it. \$\endgroup\$
    – Das Nuk
    Commented Feb 19, 2018 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.