How do I create a class with SQL connection and query functions and calling it to my windows form buttons?

Question

I want to create a class that has SQL connection and functions (like insert, select, delete queries) and I want to call it to my forms (buttons and etc.)

I don't know if it's possible or not or maybe there are some ways on doing this so...

I've done some research and come up with this code on class SQL connection and I'm not sure if it's correct.

Thank you very much in advance. I'm a beginner and want to learn more on c#.

Any type of response is appreciated. Thank you

Sorry for my bad English

using System.Data.SqlClient;

   class SqlConnClass
{
    public static SqlConnection GetConnection()
    {
        string str = "Data Source=localhost;Initial Catalog=kwem;Integrated Security=True;";

        SqlConnection conn = new SqlConnection(str);
        conn.Open();
        return conn;
    }

Answer 1

You are on the right path.
What you are referring to is called a data access layer, or DAL for short.
It's a part of the n-tier architecture model (in the simple version there are 3 tiers - presentation, business logic and data access layer).

The basic concept is that you separate the presentation, logic and data into 3 different parts of the application.
As for the data access layer, usually you'll have a static or singleton class responsible to connect the business layer to the data. This class will contain methods for CRUD operations - Create, Read, Update and Delete data. You will need to create methods for each operation and for each data entity.

One approach I see all the time is this:

public static class DAL
{
    private static string _ConnectionString = null;
    static DAL() // A static constructor to initialize the connection string
    {
        _ConnectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
    }
    public static DataSet GetCategories()
    {
        var ds = new DataSet();
        var sql = "SELECT * FROM Categories";
        using (var con = new SqlConnection(_ConnectionString))
        {
            using (var cmd = new SqlCommand(sql, con))
            {
                using (var adapter = new SqlDataAdapter(cmd))
                {
                    adapter.Fill(ds);
                }
            }
        }
        return ds;
    }

    public static int DeleteCategory(int categoryId)
    {
        int rowsEffected = 0;
        var sql = "DELETE FROM Categories WHERE Id = @Id";
        using (var con = new SqlConnection(_ConnectionString))
        {
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.Add("@Id", SqlDbType.Int).Value = categoryId;
                con.Open();
                cmd.ExecuteNonQuery();
            }
        }
        return rowsEffected;
    }
}

and so on. As you can see, there is a lot of code that repeats itself.
This means longer code, herder maintenance, and if for some reason you will want to support other types of databases (like migrating to MySql, Oracle or whatever) You will have to work very hard to change all the vendor specific classes in your DAL (SqlConnection, SqlCommand etc`).

These problems are exactly the reason I wrote ADONETHelper. I've been using it for a few years (mostly in earlier, different forms) and I feel now it's matured enough to go public. It's currently under MIT licence, meaning it's completely free and you can download your copy and change it as you see fit.
Should you choose to use it, your DAL class should probably look like this:

public static class DAL
{
    private static IDBHelper _DB;
    static DAL() // A static constructor to initialize _DB
    {
         // initialize connection string from config file
        var connectionstring = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
        _DB = DBHelperFactory.GetInstance(DataBaseType.SQLServer, connectionstring);
    }

    public static DataSet GetCategories()
    {
        var sql = "SELECT * FROM Categories";
        return _DB.FillDataSet(sql, CommandType.Text);
    }

    public static int DeleteCategory(int categoryId)
    {
        var sql = "DELETE FROM Categories WHERE Id = @Id";
        var param = _DB.CreateParameter("@Id", ADONETType.Int, categoryId);
        return _DB.ExecuteNonQuery(sql, CommandType.Text, param);
    }
}

As you can see, code repetitions are down to the bare minimum, and migrating to a different database is as simple as changing the static constructor to use a different DataBaseType. Of course, if you are using vendor-specific sql you will have to change that too.

Answer 2

It's true but; if your string str do not work. Please try this:

string str = "Data Source=local host ;Initial Catalog=kwem;Integrated Security=True"

also you need to define sql table and then select your database.

Answer 3

You were close! You may want to take the `conn.Open()' out of your method as you can open it for your query. (Remember to close it or put it in a using statement!)

public static void UpdateDB(string valToUpdate)
{
    SQLConnection conn = GetConnection();
    using (conn)
    {
    SQLCommand updateCommand = new SQLCommand(GetConnection(), "Update Table 
    Set Val = @newValue");
    updateCommand.Parameters.AddWithValue("@newValue", valToUpdate);
    conn.Open();
    updateCommand.ExecuteNonQuery();
    }
}

You would then do the same for any other kind of DB functions.