Accessing Data and Connection String?

Question

I'm creating a WinForms application. I put my connection string in App.config file. Then create the connection string variable

string gLwConnStr = ConfigurationManager.ConnectionStrings["LWConnectionString"].ConnectionString;

string sql = "Select * from Users Where LoginName='" + System.Security.Principal.WindowsIdentity.GetCurrent().Name + "'";
string tbl = "User";
DataTable dt = new DataTable(tblName);
SqlDataAdapter da = new SqlDataAdapter(sql, gLwConnStr);            
da.Fill(dt);

Does this code look correct? I don't call any .open command like other post so I don't know if I have to close and how to close it. Also I only read data, not write data so I don't use context. Other post suggested "using" but how do I use that in this code? I tried put using around the block it returns red line (like bad syntax)

I saw other post using sqlconnection to open connection then close connection but I don't use it here. How to I close it?

I don't understand why other said this post is the duplicate. Please point it out if you think it is duplication and the answer is already provided. Sorry, I am new to C# language, sometime I don't see small differences.

Can you please suggest how should the code be?

You really should be using parameters for your queries and not using + in your sql query. `SqlDataAdapter` can use parameters too but it can be a little more complicated than if you where just using a `SqlCommand` — Scott Chamberlain, Dec 17 '15 at 22:22
Thanks Scott. I will look into that. I am lost with all the SQL related methods in C#. — KD2015, Dec 17 '15 at 22:49
Just edit the question. Hope that it is different question that the duplicate post — KD2015, Dec 18 '15 at 16:31

Scott Chamberlain · Accepted Answer · 2015-12-17T22:35:44.250

5

When you pass a string in to a DataAdapter it will close the connection for you after it does the Fill. If you pass a SqlConnection to the adapter it leaves it in whatever state it found it in (If closed it opens it, uses it, then closes it. If open it uses it and does not close it).

edited Dec 17 '15 at 22:35

answered Dec 17 '15 at 22:17

Scott Chamberlain

124,994
33
282
431

I'm not sure if I understand your answers correctly. From the help file, I am using `SqlDataAdapter(string , SqlConnection)`, so this means I pass a SqlConnection and need to close it. But I could also use `SqlDataAdapter(string)` so I don't need to close the connection? – KD2015 Dec 17 '15 at 22:47
You are correct. You only pass a string so you don't need to close anything. – Scott Chamberlain Dec 17 '15 at 22:51
In that case, where do I set connection string because I have 2 ? – KD2015 Dec 17 '15 at 22:56
You set it when you create the `SqlDataAdapter`, if you have two connection strings use two `SqlDataAdapter`s. – Scott Chamberlain Dec 17 '15 at 23:15
[MSDN Link](https://msdn.microsoft.com/en-us/library/bh8kx08z(v=vs.110).aspx)The Fill method implicitly opens the Connection that the DataAdapter is using if it finds that the connection is not already open. If Fill opened the connection, it also closes the connection when Fill is finished. – KD2015 Dec 22 '15 at 18:31

score 0 · Answer 2 · answered Dec 17 '15 at 22:52

I'd try to avoid 'long-running' connections, and isolate your reads into self-contained methods, like so;

public DataTable RunMyQuery(...)
{
    using(var connection = new SqlConnection(...))
    {
         connection.Open();
         ....
    }
}

The closing brace of the using statement will close the connection for you, if it's not already closed.

That shape of code will help you write code that doesn't interact with other parts of your program; that is, if you open one connection and use it in many places, you can end up getting problems (say, you're half-way through reading one data set when you open another, and it chokes over multiple concurrent reads).

This tends to be how Microsoft arrange things; in Entity Framework you do the same thing;

 using(var context = new MyDbContext())
 {
     ...
     context.SaveChanges();
 }

Accessing Data and Connection String?

2 Answers2