I got many questions about connecting to SQL server from C# code in the simpliest way. Now I show you some very simple static classes that you can use later in your project if you create them as a Library (and build them as DLL files).

First of all I created a new Winform project and added some folders to the solution:

• Classes
• Exceptions
• SQL

The Classes folder could contain all my custom classes that would be used all over the application.

The Exceptions folder could contain all my custom exceptions.

The SQL folder could contain all my static SQL classes and methods.

I have created my new database on the SQL server, called ‘inventory’ and created some tables.

I will need a class named TempItem. This will have my items read from SQL. It’s now very simple:

using System;
using Using_SQL.Exceptions;

namespace Using_SQL.Classes
{
    public class TempItem
    {
        DateTime incomingDate;
        string receiver;
        string deliveryCompany;

        public DateTime IncomingDate
        {
            get { return incomingDate; }
            set
            {
                if (value <= DateTime.Now) incomingDate = value; else throw new DateInTheFutureException("Date cannot be greater than today!"); } } public string Receiver { get => receiver;
            set => receiver = value;
        }

        public string DeliveryCompany
        {
            get => deliveryCompany;
            set => deliveryCompany = value;
        }

        public TempItem(DateTime _incomingDate, string _receiver, string _deliveryCompany)
        {
            IncomingDate = _incomingDate;
            Receiver = _receiver;
            DeliveryCompany = _deliveryCompany;
        }
    }
}

(The LINQ-like public properties are the new features of Visual Studio 2017! The first one (IncomingDate) is the ‘classic’ version)

For now all the custom exception classes are (inherited from Exception and) empty, so I only show you this DateInTheFutureException:

using System;
using System.Runtime.Serialization;

namespace Using_SQL.Exceptions
{
    [Serializable]
    internal class DateInTheFutureException : Exception
    {
        public DateInTheFutureException()
        {
        }

        public DateInTheFutureException(string message) : base(message)
        {
        }

        public DateInTheFutureException(string message, Exception innerException) : base(message, innerException)
        {
        }

        protected DateInTheFutureException(SerializationInfo info, StreamingContext context) : base(info, context)
        {
        }
    }
}

Now we’d like to create a connection to the SQL server before applying any SQL command. Let’s create a new class named SqlServer. This will help you to maintain and update easily your code with a new SQL server. Here I used some validations.

using System;
using System.Net;
using Using_SQL.Exceptions;

namespace Using_SQL.SQL
{
    public class SqlServer
    {
        string ip;
        int port;
        string db;
        string instance;
        bool windowsAuthentication;
        string username;
        string password;

        public string Ip
        {
            get
            {
                if (this.instance != "") return this.ip + "\\" + this.instance;
                else return this.ip;
            }
            set
            {
                if (value.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries).Length == 4)
                {
                    IPAddress ipAddr;
                    if (IPAddress.TryParse(value, out ipAddr))
                    {
                        ip = value;
                    }
                    else throw new InvalidIpAddressException("Invalid IP address!");
                }
                else throw new InvalidIpAddressException("Invalid IP address!");
            }
        }

        public int Port
        {
            get => port;
            set
            {
                if (value > 0 && value < 65536) port = value; else throw new InvalidPortException("Invalid port number!"); } } public string Db { get => db;
            set => db = value;
        }

        public string Instance
        {
            private get => instance;
            set => instance = value;
        }

        public bool WindowsAuthentication
        {
            get => windowsAuthentication;
            set
            {
                this.windowsAuthentication = value;
                if (this.windowsAuthentication)
                {
                    this.username = "";
                    this.password = "";
                }
            }
        }

        public string Username
        {
            get => username;
            set
            {
                if (!WindowsAuthentication)
                {
                    this.username = value;
                }
                else this.username = "";
            }
        }

        public string Password
        {
            get => password;
            set
            {
                if (!WindowsAuthentication)
                {
                    this.password = value;
                }
                else this.password = "";
            }
        }

        public SqlServer(string _ip, int _port, string _db, string _instance = "", bool _windowsAuthentication = true, string _username = "", string _password = "")
        {
            Ip = _ip;
            Port = _port;
            Db = _db;
            Instance = _instance;
            WindowsAuthentication = _windowsAuthentication;
            Username = _username;
            Password = _password;
        }
    }
}

As you can see, the public IP property will validate if a real IP address is given. Otherwise the new instance of this class won’t be created. The same is true for the port number. If you use your SQL server with WindowsAuthentication then no username and password is needed in the connection string, because you current Windows username/password pair will be used. Depending on your SQL server installation, instance name is also not a mandatory field.

Now you can create a new SqlServer instance this way (maybe on your main form):

SqlServer sqlServer = new SqlServer("10.250.6.143", 1433, "inventory");

Now you can see, that neither instance name, nor username/password pair were used.

To be able to connect to this server, we need the Connection class with a new custom exception, called SqlConnectionException:

using System;
using System.Data.SqlClient;
using Using_SQL.Exceptions;

namespace Using_SQL.SQL
{
    public static class Connection
    {
        public static SqlConnection ServerConnection(SqlServer _sqlServer)
        {
            string connUri;

            if (_sqlServer.WindowsAuthentication)
            {
                connUri = "Data Source=tcp:" + _sqlServer.Ip + "," + _sqlServer.Port + ";Integrated Security=True;Encrypt=True;TrustServerCertificate=True;PersistSecurityInfo=True;database=" + _sqlServer.Db + ";connection timeout=30;Pooling=False";
            }
            else
            {
                connUri = "Data Source=tcp:" + _sqlServer.Ip + "," + _sqlServer.Port + ";User ID=" + _sqlServer.Username + ";Password=" + _sqlServer.Password + ";Encrypt=True;TrustServerCertificate=True;PersistSecurityInfo=True;database=" + _sqlServer.Db + ";connection timeout=30;Pooling=False";
            }

            try
            {
                return new SqlConnection(connUri);
            }
            catch (Exception ex)
            {
                throw new SqlConnectionException("Couldn't create new connection!", ex);
            }
        }
        
        public static SqlConnection LocalDbConnection(string _attachDbFilename, string _instanceName = "MSSQLLocalDB")
        {
            string connUri;
            string Path = Environment.CurrentDirectory;
            string[] appPath = Path.Split(new string[] { "bin" }, StringSplitOptions.None);
            AppDomain.CurrentDomain.SetData("DataDirectory", appPath[0]);
            connUri = "Data Source=(LocalDB)\\" + _instanceName + ";AttachDbFilename= |DataDirectory|\\" + _attachDbFilename + ";Integrated Security=True";//attachDbFilename could be MyDatabase.mdf for example

            try
            {
                return new SqlConnection(connUri);
            }
            catch (Exception ex)
            {
                throw new SqlConnectionException("Couldn't create new connection!", ex);
            }
        }
    }
}

Now we use the first method and not the LocalDb one to create the connection. After the previously created SqlServer instance add this:

SqlConnection conn = Connection.ServerConnection(sqlServer);

Don’t forget to add the using of System.Data.SqlClient to this main form!
My main form looks like this (I place the SqlServer variable and the SqlConnection variable to class level to be able to access them anywhere inside or outside this form class):

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Windows.Forms;
using Using_SQL.Classes;
using Using_SQL.SQL;

namespace Using_SQL
{
    public partial class Form1 : Form
    {
        static SqlServer sqlServer;
        public static SqlConnection conn;

        public Form1()
        {
            InitializeComponent();

            sqlServer = new SqlServer("10.250.6.143", 1433, "inventory");
            conn = Connection.ServerConnection(sqlServer);
        }
    }
}

Now let’s create our new static class that will contain all the SQL commands. This static class will have some other nested static classes to have a well-understood and clear structure:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Using_SQL.Classes;
using Using_SQL.Exceptions;

namespace Using_SQL.SQL
{
    public static class Commands
    {
        static SqlCommand cmd;
        static SqlConnection conn = Form1.conn;//because it is public static on form1 class

        public static class WithTransaction
        {
            public static class Select
            {

            }

            public static class Insert
            {

            }

            public static class Update
            {

            }

            public static class Delete
            {
                public static void PurchaseCancel(int _id)
                {
                    SqlTransaction trans;
                    if (conn.State != ConnectionState.Open) conn.Open();
                    trans = conn.BeginTransaction();

                    cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM IncomingTable WHERE Id=@id";
                    cmd.Parameters.Add("@id", SqlDbType.Int).Value = _id;
                    cmd.Transaction = trans;

                    SqlCommand cmd2 = new SqlCommand();
                    cmd2.Connection = conn;
                    cmd2.CommandText = "DELETE FROM ItemTable WHERE IncomingId=@incid";
                    cmd2.Parameters.Add("@incid", SqlDbType.Int).Value = _id;
                    cmd2.Transaction = trans;
                    
                    try
                    {
                        if (cmd.ExecuteNonQuery() > 0 && cmd2.ExecuteNonQuery() > 0)
                        {
                            trans.Commit();
                        }
                        else throw new SqlTransactionException("Error in Sql transaction! Rolling back...");
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        throw ex;
                    }
                }
            }
        }

        public static class WithoutTransaction
        {
            public static class Select
            {
                public static DataTable FullInventory()
                {
                    DataTable result = new DataTable();
                    using (cmd = new SqlCommand())
                    {
                        if (conn.State != ConnectionState.Open) conn.Open();
                        cmd.Connection = conn;
                        cmd.CommandText = "SELECT * FROM FullInventoryTable";
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            da.Fill(result);
                        }
                    }
                    return result;
                }

                public static List DeliveryCompanies()
                {
                    List result = new List();
                    using (cmd = new SqlCommand())
                    {
                        if (conn.State != ConnectionState.Open) conn.Open();
                        cmd.Connection = conn;
                        cmd.CommandText = "SELECT * FROM Companies ORDER BY Name";
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                result.Add(dr["Name"].ToString());
                            }
                            dr.Close();//this is useless because of 'using'
                        }
                    }
                    return result;
                }
            }

            public static class Insert
            {
                public static int NewIncome(TempItem _item)
                {
                    using (cmd = new SqlCommand())
                    {
                        if (conn.State != ConnectionState.Open) conn.Open();
                        cmd.Connection = conn;
                        cmd.CommandText = "INSERT INTO IncomingTable (Date,Receiver,Company) OUTPUT INSERTED.Id VALUES(@date,@receiver,@company)";//OUTPUT only works if the table has a column with the name of 'Id' and it's set to auto increment!
                        cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = _item.IncomingDate;
                        cmd.Parameters.Add("@receiver", SqlDbType.NVarChar).Value = _item.Receiver;
                        cmd.Parameters.Add("@company", SqlDbType.NVarChar).Value = _item.DeliveryCompany;

                        return Convert.ToInt32(cmd.ExecuteScalar());
                    }
                }
            }

            public static class Update
            {

            }

            public static class Delete
            {

            }
        }
    }
}

Create a new instace of TempItem on your main form to be able to test these new SQL functions.

TempItem item = new TempItem(DateTime.Now.AddDays(-1), "Attila Török", "DHL");

Now on the main form if you start typing Connection. you’ll get the 2 static class under it: WithTransaction and WithoutTransaction. Continue typing WithoutTransaction. and you’ll get the 4 main commands of SQL: Select, Insert, Update, Delete. Typing Insert you’ll get the NewIncome() method that expects 1 parameter that is an instance of TempItem:

Commands.WithoutTransaction.Insert.NewIncome(item);

Of course you can get the list of the companies:

List<string> companies = Commands.WithoutTransaction.Select.DeliveryCompanies();

Some basic things about these new keywords: SqlCommand, Parameters, ExecuteNonQuery, ExecuteScalar, SqlTransaction

When you create a new SQL command (let’s say a simple SELECT * FROM Table command) you’ll have to create a new SqlCommand instance containing the query and the connection you want to use.
I could run out of the world, when I see somebody using concatenations with SQL commands (for example: string Query = “SELECT * FROM Table WHERE Username='” + username + “‘ AND Password='” + password + “‘”; This is the most vulnerable area of the SQL server: the SQL Injection! What if I type this username:

'';DROP TABLE Table;--

🙂 Don’t try it… The query will look like this:
SELECT * FROM Table WHERE Username=”;DROP TABLE Table;–AND Password=’something’
The semicolon tells the SQL server that new command is coming.
The double – tells the SQL server to skip the remaining commands… So you do a table listing command then you delete the whole table.
To protect your SQL server against SQL injection ALWAYS USE PARAMETRIZED SQL QUERIES!

string Query = "SELECT * FROM Table WHERE Username=@user AND Password=@pass";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = Query;
cmd.Parameters.Add("@user", SqlDbType.NVarChar).Value = username;
cmd.Parameters.Add("@pass", SqlDbType.NVarChar).Value = password;

Isn’t it much more beautiful?

The ExecuteNonQuery() command returns with the rows of the result. If you’d like to delete one row from a table, this will return with 1 if it’s successful.
The ExecuteScalar() command returns with the first column of the first row. Every other rows and columns will be ignored. This is useful if you SELECT COUNT(*) on a table for example.
The SqlTransaction is used when you want to have multiply commands to be executed in the same time. If any of the commands fails, you’d like to ignore all other commands. In this case, we ‘prepare’ all commands, check if all of the would be successful. If so, then we Commit() the transaction. If any of them would fail, we Rollback() the transaction. This SqlTransaction will also lock the table until Commit() or Rollback() command is executed!