Creating a SqlDbConnection class in C#

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

I'm trying to create a SqlDbConnection class file for my application. And I want use this connection class to connect to the database whenever it requires. Till now what I have for the class code is:

C#

using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;

namespace SqlTesting
{
    public class SqlDbConnection
    {
        private SqlConnection con;
        public SqlCommand cmd;
        private SqlDataAdapter sda;
        private DataTable dt;

        public SqlDbConnection()
        {
            con = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=DB_SQL;Integrated Security=SSPI;");
            con.Open();
        }

        public void SqlQuery(string querytext)
        {
            cmd = new SqlCommand(querytext, con);
        }

        public DataTable QueryEx()
        {
            sda = new SqlDataAdapter(cmd);
            dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }

        public void NonQueryEx()
        {
            cmd.ExecuteNonQuery();
        }
    }
}

I guess that I need to call the DbConnection.Dispose Method, so it will close and dispose of the SqlConnection. Now I'am bit confused with how exactly I should use the Dispose() method on my SqlDbConnection class file. So any related guideline would be much appreciated.

Posted 10-Jun-14 22:51pm

User 10744971

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2014-06-10T23:06:00

Solution 1

Add the Interface IDisposable to your class definition:

C#

public class SqlDbConnection : IDisposable

Then add the Dispose method:

C#

public void Dispose()
    {
    if (cmd != null)
        {
        cmd.Dispose();
        cmd = null;
        }
    if (sda != null)
        {
        sda.Dispose();
        sda = null;
        }
    if (dt != null)
        {
        dt.Dispose();
        dt = null;
        }
    if (con != null)
        {
        if (con.State == ConnectionState.Open) con.Close();
        con.Dispose();
        con = null;
        }
    }

There is a good discussion on this here: Implementing IDisposable and the Dispose Pattern Properly[^] which is well worth a read.

Posted 10-Jun-14 23:06pm

OriginalGriff

Updated 10-Jun-14 23:07pm

v2

Comments

[no name] 11-Jun-14 5:48am

Thanks for your contribution. Now I need to know little bit more.... I'm providing my Update query code.. you better have a look and check that it's in correct format or not...

//This Section was used to Update the existing records on the Database for Users.
private void btnUpdate_Click(object sender, EventArgs e)
{
//Connecting Database form SqlDbConnect.cs Class
con = new SqlDbConnection();
con.SqlQuery("update LookDB.dbo.Employee set FirstName='" + this.firstname_txt.Text + "', LastName='" + this.lastname_txt.Text + "', Gender='" + this.cmb_gender.Text + "', DOB='" + this.DOB_Picker.Text + "', FathersName='" + this.FathersName_txt.Text + "', PerAddress='" + this.pAdd_txt.Text + "', TempAddress='" + this.tAdd_txt.Text + "', Phone='" + this.ph_txt.Text + "', Mobile='" + this.mobile_txt.Text + "', Department='" + this.cmb_Dept.Text + "', JoiningDate='" + this.Joining_Picker.Text + "', Designation='" + this.cmb_Designation.Text + "', Email='" + this.email_txt.Text + "', Salary='" + this.salary_txt.Text + "' where EmpID='" + this.EmpID_txt.Text + "';");
SqlDataReader Reader;
try
{
Reader = con.cmd.ExecuteReader();
MessageBox.Show("Record successfully updated.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (con != null)
{
con.Dispose();
}
}

OriginalGriff 11-Jun-14 6:44am

No. Not even close!

The first thing is "why are you using ExecuteReader on an Update?" Use ExecuteNonQuery instead.

Second, and much, much, much more important: Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

The third is that this is not related to the original question, and should be a new question instead.