SqlConnection from class into SqlCommand

Question

Hello I have got this code in class which runs SqlConnection:

using System.Data.SqlClient;
using System.IO;

namespace tours
{
    class myConnection
    {
        public static SqlConnection GetConnection()
        {
                string path = "C:\\Users\\marek\\Documents\\Visual Studio 2012\\Projects\\tours\\tours\\sql_string.txt";
                StreamReader sr = new StreamReader(File.Open(path, FileMode.Open));

                string str = "Data Source='" + sr.ReadLine() + "';Initial Catalog ='" + sr.ReadLine() + "' ;User ='" + sr.ReadLine() + "';Password = '" + sr.ReadLine() + "'";

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

In other forms I got SqlCommand or SqlDataAdapter and I need to add to them SqlConnection, but don't know how can I access them? Would you please suggest me something? for example this:

DataTable dt = new DataTable();
SqlDataAdapter SDA = new SqlDataAdapter("SELECT * FROM zajezd",);
SDA.Fill(dt);

You don't "add" a SqlCommand to a SqlConnection. Were you perhaps looking for the SqlDataAdapter(string, SqlConnection) constructor? — Jon Skeet
– Jon Skeet, Commented Jul 27, 2013 at 7:46

Damith · Accepted Answer · 2013-07-27 08:23:05Z

5

try

new SqlDataAdapter("SELECT * FROM zajezd",tours.myConnection.GetConnection())

or you already add using tours; in top of your file then you can call as myConnection.GetConnection()

Note:

make myConnection as public

public class myConnection

if myConnection is in separate class library you need to add reference to data class library

I would change the connection class return only the connection string, like below

public class myConnection
{
    public static string GetConnectionString()
    {
        string connection = string.Empty;
        string path = "C:\\Users\\marek\\Documents\\Visual Studio 2012\\Projects\\tours\\tours\\sql_string.txt";
        using (StreamReader sr = new StreamReader(File.Open(path, FileMode.Open)))
        {
            connection = "Data Source='" + sr.ReadLine() + "';Initial Catalog ='" + sr.ReadLine() + "' ;User ='" + sr.ReadLine() + "';Password = '" + sr.ReadLine() + "'";

        } 

        return connection;

    }
}

When we need to access database,

DataTable dt = new DataTable();

using (SqlConnection con = new SqlConnection(myConnection.GetConnectionString()))
using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM zajezd", con))
{
    adapter.Fill(dt);
}

In case of you need ExecuteNonQuery or ExecuteScalar

using (SqlConnection con = new SqlConnection(myConnection.GetConnectionString()))
using (SqlCommand commad = new SqlCommand("sql statements", con))
{
    con.Open();
    commad.ExecuteNonQuery(); 
    //var result = commad.ExecuteScalar();

}

edited Jul 27, 2013 at 8:23

answered Jul 27, 2013 at 7:48

Damith

63.2k13 gold badges106 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Marek Over a year ago

Hello, thank you so much, I tried to do this: ("SELECT * FROM zajezd", myConnection.myConnection()); but it said: tours.myConnection does not contaion definition for 'myConnection' How should I improve it ? thanks again.

Damith Over a year ago

@Marek have you change myConnection as public?

Marek Over a year ago

Yes I did. I'm not sure now where the problém is :/

Marek Over a year ago

Thank oyu so much, Is there way to do something like: connection.Close(); and connection.Open()? I think I need this for Execute

Damith Over a year ago

@Marek for SqlDataAdapter you don't need to have open connection, SqlDataAdapter will open the connection when needed, you better get connection string from static class and create connection from what when you need. i'll update the answer. give me few minutes

|

Kavipriya · Accepted Answer · 2013-07-27 08:00:50Z

0

your method name is GetConnection() not myConnection

answered Jul 27, 2013 at 8:00

Kavipriya

544 bronze badges

Collectives™ on Stack Overflow

SqlConnection from class into SqlCommand

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related