How to use SQL Connection Class in Windows Forms Application

Hello Guys!

I got adopted this code from this website written by Hammad Maqbool. I found it very interesting but i just want to clarify if how would i going to use this in Windows Forms Applications like in "Try" Statement.

This is the class i modified:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;
namespace myssfasys
{
public class myconnstring1
{
string ConnString = "Server=localhost; User Id=root; Password=deityshasm911; Database=ssfasys";
MySqlConnection con;
public void OpenConection()
{
con = new MySqlConnection(ConnString);
con.Open();
}
public void CloseConnection()
{
con.Close();
}
public void ExecuteQueries(string Query_)
{
MySqlCommand cmd = new MySqlCommand(Query_, con);
cmd.ExecuteNonQuery();
}
public MySqlDataReader DataReader(string Query_)
{
MySqlCommand cmd = new MySqlCommand(Query_, con);
MySqlDataReader dr = cmd.ExecuteReader();
return dr;
}
public object ShowDataInGridView(string Query_)
{
MySqlDataAdapter dr = new MySqlDataAdapter(Query_, ConnString);
DataSet ds = new DataSet();
dr.Fill(ds);
object dataum = ds.Tables[0];
return dataum;
}
}
}

I'm just new with programming so please i need help.

I hope my response provides you inspiration and ideas for a solution.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace SandBox
{
public partial class MyMainForm : Form
{
// declare a class level object variable for the entire class
private System.Data.SqlClient.SqlConnection m_cnn = null;
public MyMainForm()
{
InitializeComponent();
// create the connection object in class constructor or form_load
// NOTE: Connection strings vary according to application and security needs.
// Please don't take my connection string as gospel. Just an example.
m_cnn = new System.Data.SqlClient.SqlConnection("Server=localhost;Database=;Trusted_Connection=True;");
}
~ MyMainForm()
{
// class deconstructor clean up
if(m_cnn.State != ConnectionState.Closed)
{
m_cnn.Close();
}
m_cnn = null;
}
private void PopulateAUIComponent()
{
DataTable dt = GetData();
// method 1
foreach(DataRow dr in dt.Rows)
{
// popualte a control and/or create an object using the data
// control.Items.Add( )
}
// method 2
// control.DataSource = dt
}
private DataTable GetData()
{
// USING THE CONNECTION TO GET DATA
// the majority (or perhaps all) data operations require a command for the connection
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
// configure the command
// give the command the connection
cmd.Connection = m_cnn;
// configure for sql server stored procedure call
cmd.CommandType = CommandType.StoredProcedure;
// provide stored procedure name
cmd.CommandText = "GetCustomerOrders";
// add any input or output parameters
cmd.Parameters.Clear();
System.Data.SqlClient.SqlParameter prm = new System.Data.SqlClient.SqlParameter();
prm.ParameterName = "@CUSTOMERID";
prm.SourceColumn = "CUSTOMER_ID";
prm.SqlDbType = SqlDbType.Int;
prm.Direction = ParameterDirection.Input;
prm.Value = 777;
// add parameter
cmd.Parameters.Add(prm);
// create a data adapter
// NOTE: the data adapter automatically opens and closes the connection in the fill method
// you don't have to open it in code
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
// create a data table
System.Data.DataTable dtOrders = new DataTable("Orders");
// fill method automatically opens and closes the connection
da.Fill(dtOrders);
return dtOrders;
}
private void UpdateCustomer()
{
// USING THE CONNECTION TO UPDATE or CHANGE DATA
// the majority (or perhaps all) data operations require a command for the connection
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
// configure the command
// give the command the connection
cmd.Connection = m_cnn;
// configure for sql server stored procedure call
cmd.CommandType = CommandType.StoredProcedure;
// provide stored procedure name
cmd.CommandText = "UpdateCustomer";
// add any input or output parameters
cmd.Parameters.Clear();
// identifies which customer (or row) is going to be updated
System.Data.SqlClient.SqlParameter prm1 = new System.Data.SqlClient.SqlParameter();
prm1.ParameterName = "@CUSTOMERID";
prm1.SourceColumn = "CUSTOMER_ID";
prm1.SqlDbType = SqlDbType.Int;
prm1.Direction = ParameterDirection.Input;
prm1.Value = 777;
// add parameter
cmd.Parameters.Add(prm1);
// identifies which column is going to be updated
System.Data.SqlClient.SqlParameter prm2 = new System.Data.SqlClient.SqlParameter();
prm2.ParameterName = "@ADDRESS";
prm2.SourceColumn = "ADDRESS";
prm2.SqlDbType = SqlDbType.VarChar;
prm2.Size = 250;
prm2.Direction = ParameterDirection.Input;
prm2.Value = 777;
// add parameter
cmd.Parameters.Add(prm2);
// when you update or change data you will need to explicitly open and close the connection
// no data adapter required here
// no data table or dataset required
if(m_cnn.State != ConnectionState.Open) { m_cnn.Open(); }
cmd.ExecuteNonQuery();
if(m_cnn.State != ConnectionState.Closed { m_cnn.Close(); }
}
}
}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

James Axsom8y ago
I hope my response provides you inspiration and ideas for a solution.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SandBox
{
    public partial class MyMainForm : Form
    {
        // declare a class level object variable for the entire class
        private System.Data.SqlClient.SqlConnection m_cnn = null;

        public MyMainForm()
        {
            InitializeComponent();
            // create the connection object in class constructor or form_load
            // NOTE: Connection strings vary according to application and security needs.
            // Please don't take my connection string as gospel. Just an example.
            m_cnn = new System.Data.SqlClient.SqlConnection("Server=localhost;Database=;Trusted_Connection=True;");
        }

        ~ MyMainForm()
        {
            // class deconstructor clean up
            if(m_cnn.State != ConnectionState.Closed)
            {
                m_cnn.Close();
            }

            m_cnn = null;
        }

        private void PopulateAUIComponent()
        {
            DataTable dt = GetData();

            // method 1
            foreach(DataRow dr in dt.Rows)
            {
                // popualte a control and/or create an object using the data
                // control.Items.Add(  )
            }

            // method 2
            // control.DataSource = dt

        }

        private DataTable GetData()
        {
            // USING THE CONNECTION TO GET DATA

            // the majority (or perhaps all) data operations require a command for the connection
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            // configure the command
            // give the command the connection
            cmd.Connection = m_cnn;
            // configure for sql server stored procedure call
            cmd.CommandType = CommandType.StoredProcedure;
            // provide stored procedure name
            cmd.CommandText = "GetCustomerOrders";

            // add any input or output parameters
            cmd.Parameters.Clear();

            System.Data.SqlClient.SqlParameter prm = new System.Data.SqlClient.SqlParameter();
            prm.ParameterName = "@CUSTOMERID";
            prm.SourceColumn = "CUSTOMER_ID";
            prm.SqlDbType = SqlDbType.Int;
            prm.Direction = ParameterDirection.Input;
            prm.Value = 777;
            // add parameter
            cmd.Parameters.Add(prm);

            // create a data adapter
            // NOTE: the data adapter automatically opens and closes the connection in the fill method
            // you don't have to open it in code
            System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
            // create a data table
            System.Data.DataTable dtOrders = new DataTable("Orders");
            // fill method automatically opens and closes the connection
            da.Fill(dtOrders);

            return dtOrders;
        }

        private void UpdateCustomer()
        {
            // USING THE CONNECTION TO UPDATE or CHANGE DATA

            // the majority (or perhaps all) data operations require a command for the connection
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            // configure the command
            // give the command the connection
            cmd.Connection = m_cnn;
            // configure for sql server stored procedure call
            cmd.CommandType = CommandType.StoredProcedure;
            // provide stored procedure name
            cmd.CommandText = "UpdateCustomer";

            // add any input or output parameters
            cmd.Parameters.Clear();

            // identifies which customer (or row) is going to be updated
            System.Data.SqlClient.SqlParameter prm1 = new System.Data.SqlClient.SqlParameter();
            prm1.ParameterName = "@CUSTOMERID";
            prm1.SourceColumn = "CUSTOMER_ID";
            prm1.SqlDbType = SqlDbType.Int;
            prm1.Direction = ParameterDirection.Input;
            prm1.Value = 777;
            // add parameter
            cmd.Parameters.Add(prm1);

            // identifies which column is going to be updated
            System.Data.SqlClient.SqlParameter prm2 = new System.Data.SqlClient.SqlParameter();
            prm2.ParameterName = "@ADDRESS";
            prm2.SourceColumn = "ADDRESS";
            prm2.SqlDbType = SqlDbType.VarChar;
            prm2.Size = 250;
            prm2.Direction = ParameterDirection.Input;
            prm2.Value = 777;
            // add parameter
            cmd.Parameters.Add(prm2);

            // when you update or change data you will need to explicitly open and close the connection
            // no data adapter required here
            // no data table or dataset required
            if(m_cnn.State != ConnectionState.Open) { m_cnn.Open(); }
            cmd.ExecuteNonQuery();
            if(m_cnn.State != ConnectionState.Closed { m_cnn.Close(); }
        }
    }
}
+1
Hemant Jindal8y ago
Hi Jansen
Please refer this link - http://csharp.net-informations.com/data-providers/csharp-sql-server-connection.htm

Thanks
Hemant
+1