Creating database connection class in c#

Question

I'm a beginner of learning C#. I've written this code to display data from database in a combobox with a connection in the same form, and then my teacher asked me to create a class to for the connection and call it from any form I'm using.

And I've already made my connection here. Is it just to remove the code to a new class and just call? Because I found many lessons on the internet creating code for the connection which is different from mine.

This code is to display data in a combobox

SqlConnection con = new SqlConnection("Data Source");
SqlCommand cmd = new SqlCommand("Select ", con);

// con.Open();
SqlDataReader DR1;

try
{
    con.Open();
    DR1 = cmd.ExecuteReader();

    while (DR1.Read())
    {
        int BayN = Convert.ToInt32(DR1["BayNumber"]);
        comboBox1.Items.Add(BayN);
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally
{
    con.Close();
}

and the other connection is to display the data 8 rows to textboxes :

SqlConnection con = new SqlConnection("Data Source");
DataSet dsa = new DataSet();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("Select ='" + comboBox1.Text.Trim() + "';", con);
da.Fill(dsa);

for (int i = 0; i <= 8; i++)
{
    for (int k = 0; k <= 8; k++)
    {
        textBox1.Text = dsa.Tables[0].Rows[i++]["PatientId"].ToString();
        label2.Text = dsa.Tables[0].Rows[k++]["Status"].ToString();
        textBox2.Text = dsa.Tables[0].Rows[i++]["PatientId"].ToString();
        label4.Text = dsa.Tables[0].Rows[k++]["Status"].ToString();
        textBox3.Text = dsa.Tables[0].Rows[i++]["PatientId"].ToString();
        label6.Text = dsa.Tables[0].Rows[k++]["Status"].ToString();
        textBox4.Text = dsa.Tables[0].Rows[i++]["PatientId"].ToString();
        label8.Text = dsa.Tables[0].Rows[k++]["Status"].ToString();
        textBox5.Text = dsa.Tables[0].Rows[i++]["PatientId"].ToString();
        label10.Text = dsa.Tables[0].Rows[k++]["Status"].ToString();
        textBox6.Text = dsa.Tables[0].Rows[i++]["PatientId"].ToString();
        label12.Text = dsa.Tables[0].Rows[k++]["Status"].ToString();
        textBox7.Text = dsa.Tables[0].Rows[i++]["PatientId"].ToString();
        label14.Text = dsa.Tables[0].Rows[k++]["Status"].ToString();
        textBox8.Text = dsa.Tables[0].Rows[i++]["PatientId"].ToString();
        label16.Text = dsa.Tables[0].Rows[k++]["Status"].ToString();
    }
}

I'm so sorry if I went too deep or in details, but to make it clear (: and I'm lost now because I'm using DataSet and DataReader is possible to call them from another class?

SqlCommand cmd = new SqlCommand("Select ", con); where is the actual Select Statement — MethodMan
– MethodMan, Commented Nov 28, 2015 at 21:52
for starters you need to understand how Classes work, you need to understand how to return DataSet, DataTable, etc... I will post something that you can put into your own SqlHelpers Class — MethodMan
– MethodMan, Commented Nov 28, 2015 at 22:04
for (int i = 0; i <= 8; i++) { for (int k = 0; k <= 8; k++) { unnecessary code you need to understand how to iterate over the DataTable that fills a DataTable or DataSet.. also it's easier to fill a DataTable than to return the DataSet unless you are returning DataSet.Tables[0] — MethodMan
– MethodMan, Commented Nov 28, 2015 at 22:15

MethodMan · Accepted Answer · 2015-11-28 22:12:38Z

public static class SqlDBHelper
{
    public static DataSet ExecuteDataSet(string sql, CommandType cmdType, params SqlParameter[] parameters)
    {
        using (DataSet ds = new DataSet())
        using (SqlConnection connStr = new SqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
        using (SqlCommand cmd = new SqlCommand(sql, connStr))
        {
            cmd.CommandType = cmdType;
            foreach (var item in parameters)
            {
                cmd.Parameters.Add(item);
            }

            try
            {
                cmd.Connection.Open();
                new SqlDataAdapter(cmd).Fill(ds);
            }
            catch (SqlException ex)
            {
                //log to a file or Throw a message ex.Message;
            }
            return ds;
        }
    }

    public static DataTable ExecuteDataSet(string sql, CommandType cmdType, params SqlParameter[] parameters)
    {
        using (DataSet ds = new DataSet())
        using (SqlConnection connStr = new SqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
        using (SqlCommand cmd = new SqlCommand(sql, connStr))
        {
            cmd.CommandType = cmdType;
            foreach (var item in parameters)
            {
                cmd.Parameters.Add(item);
            }

            try
            {
                cmd.Connection.Open();
                new SqlDataAdapter(cmd).Fill(ds);
            }
            catch (SqlException ex)
            {
                //Show a message or log a message on ex.Message
            }
            return ds.Tables[0];
        }
    }   
}

if you want to add another method in your class that returns a DataTable then do the following

Collectives™ on Stack Overflow

Creating database connection class in c#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related