usage of classes for my connectionstring that reads from app.config

Question

0.00/5 (No votes)

See more:

I have tried to create a ConnectionString within my app.config and i'm trying to initialize it via a class i have created.

App.config:

C#

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <connectionStrings>
    <add name="EQCas"
         connectionString="Data Source=solutionworx-pc\sqlexpress;Initial Catalog=eqcas;Integrated Security=True;"/>
  </connectionStrings>
</configuration>

Application:

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            new SQLConnect();
            SqlDataReader myReader = null;
            SqlCommand myCommand = new SqlCommand("select x_id, fullname, email FROM cas_user_ext WHERE fullname = @fullname");
            myCommand.Parameters.AddWithValue("@username", "Ivan Lubbe");                                          
            myReader = myCommand.ExecuteReader();
            while (myReader.Read())
            {
                Console.WriteLine(myReader["x_id"]);
                Console.WriteLine(myReader["fullname"]);
                Console.WriteLine(myReader["email"]);
            } 
        }
    }
}

public class SQLConnect
{
    public string myConnection;

    public SQLConnect()
    {
        myConnection = ConfigurationManager.ConnectionStrings["EQCas"].ConnectionString;
        SqlConnection connectSQL = new SqlConnection(myConnection);
        connectSQL.Open();
    }
}

I assume i am using the class i created wrong. Most tutorials are not very clear on how to use
classes as i have tried to use it. I am new to C# been using it for around 4 weeks now. Would appreciate any assistance. Also once i have got my connection string working i will attempt to create a class to get/set values from SQL unlike the code above (just for testing purposes)

This is what i have done with the advice i have gotten.

v2

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            SQLConnect sqlcon = new SQLConnect();
            SqlDataReader myReader = null;
            SqlCommand myCommand = new SqlCommand("select x_id, fullname, email FROM cas_user_ext WHERE fullname = @fullname", 
                                                                                    sqlcon.Connection);
            myCommand.Parameters.AddWithValue("@fullname", "Ivan Lubbe");                                          
            myReader = myCommand.ExecuteReader();
            while (myReader.Read())
            {
                Console.WriteLine(myReader["x_id"]);
                Console.WriteLine(myReader["fullname"]);
                Console.WriteLine(myReader["email"]);
            } 
        sqlcon.Close();
        }
    }
}

public class SQLConnect
{
    public string myConnection;
    public SqlConnection Connection { get; private set; }

    public SQLConnect()
    {
        myConnection = ConfigurationManager.ConnectionStrings["EQCas"].ConnectionString;
        //SqlConnection connectSQL = new SqlConnection(myConnection);
        //connectSQL.Open();
        Connection = new SqlConnection(myConnection);
        Connection.Open();
    }

    internal void Close()
    {
        Connection.Close();
    }
}

Anything else that i should improve on? Or specific things i should read up on?

Posted 9-Sep-14 23:24pm

Ivan Lubbe

Updated 10-Sep-14 0:59am

v2

Add a Solution

2 solutions

Solution 1

You have not pass the connection object to sqlcommand

replace your main function

static void Main(string[] args)
{
SQLConnect sq=new SQLConnect();
SqlConnection con=new Sqlconnection(sq.myConnection);
con.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand("select x_id, fullname, email FROM cas_user_ext WHERE fullname = @fullname",con);
myCommand.Parameters.AddWithValue("@username", "Ivan Lubbe");
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
Console.WriteLine(myReader["x_id"]);
Console.WriteLine(myReader["fullname"]);
Console.WriteLine(myReader["email"]);
}
con.Close();
}

Posted 9-Sep-14 23:36pm

Salman622

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-09-09T23:39:00

Um...I think you need a bit of practice at this...

You create a new instance of the class:

C#

new SQLConnect();

But you don't keep a reference to it: that's like buying a packet of sweets in a shop and leaving them behind when you leave: when you get home, you can't eat any of the sweeties because you no longer have the instance you bought.

So start by keeping the reference:

C#

SqlConnect con = new SQLConnect();

But...you don't keep the SqlConnection you create within your class either! So when you exit the constructor, once again you can't get at the sweeties! :laugh:
So add a Property to allow you to retrieve it:

C#

public class SQLConnect
{
    private string myConnection;
    public SqlConnection Connection { get; private set; }

    public SQLConnect()
    {
        myConnection = ConfigurationManager.ConnectionStrings["EQCas"].ConnectionString;
        Connection = new SqlConnection(myConnection);
        Connection.Open();
    }
}

Now, the connection will last as long as the reference to your class does.

Now, you need to "attach" the Command to the Connection or the SqlCommand doesn't know where it is supposed to execute:

C#

SqlCommand myCommand = new SqlCommand("select x_id, fullname, email FROM cas_user_ext WHERE fullname = @fullname", con.Connection);

This isn't an optimal solution: It doesn't allow for closing the connection (which you need to do) or Disposing of it (which you also need to do)

I'd probably do it differently: have a static method (or property) in my class which returned the connection string, and let the external code create, open, use and Dispose the SqlConnection object directly.

[edit]Typos - OriginalGriff[/edit]