Connection problem between console program and MS SQL

scolty · June 4th, 2011, 09:00 AM

Hi, i was wondering if someone could help me with two issues

1) I created a simple console program (code file and then set the properties to console app). then just typed in the namespaces i required but for some reason it wouldnt let me compile it without go to the Project -> Add reference menu and physically selecting them there as well. System.Data.SqlClient was missing but im assuming thats just because its part of the System.Data Namespace?

2) The main problem im having is with connecting to the copy of MS SQLEXPRESS 2008 r2 that i have installed on my pc. The server name is OPHIUCHUS and i have Windows Authentication selected so i dont need to enter my password in the code (so i believe)? With that in mind, im confused as to why i cant create a connection to it with the following code. Note im trying to use a ConnectionStringBuilder object.

Is there something stupid i have missed??

Code:

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

class Program
{
    static void Main(string[] args)
    {
        Console.WriteLine("****Fun with Data Readers****\n");

// START OF Updated Method Using Connection String Builder Objects

        // Create A Connection String Via the ConnetionStringBuilder Object

        SqlConnectionStringBuilder cnStrBuilder = new SqlConnectionStringBuilder();
        cnStrBuilder.InitialCatalog = "AutoLot";
        cnStrBuilder.DataSource = @"(local)\SQLEXPRESS";
        cnStrBuilder.ConnectTimeout = 30;
        cnStrBuilder.IntegratedSecurity = true;

        using(SqlConnection cn = new SqlConnection()){
            cn.ConnectionString = cnStrBuilder.ConnectionString;
            cn.Open();
            ShowConnectionStatus(cn);
            string strSQL = "Select * From Inventory";
            SqlCommand myCommand = new SqlCommand(strSQL, cn);
            SqlCommand testCommand = new SqlCommand();
            testCommand.Connection = cn;
            testCommand.CommandText = strSQL;
            SqlDataReader myDataReader = myCommand.ExecuteReader();
                while (myDataReader.Read())
                {
                    Console.WriteLine("**** Records ****");
                    for (int i= 0; i < myDataReader.FieldCount; i++)
                    {
                        Console.WriteLine("{0} = {1} ",
                            myDataReader.GetName(i),
                            myDataReader.GetValue(i).ToString());
                    }
                    Console.WriteLine();
                }
            }
    }

    static void ShowConnectionStatus(SqlConnection cn)
    {
        Console.WriteLine("***Info about your connection***");
        Console.WriteLine("Database location: {0}", cn.DataSource);
        Console.WriteLine("Database name: {0}", cn.Database);
        Console.WriteLine("Timeout: {0}", cn.ConnectionTimeout);
        Console.WriteLine("Connection state: {0}", cn.State.ToString());
    }
}

Thanks in advance!!

scolty · June 5th, 2011, 07:51 AM

The syntax was incorrect, the below is the correct syntax

Code:

        SqlConnectionStringBuilder cnStrBuilder = new SqlConnectionStringBuilder();
        cnStrBuilder["Initial Catalog"] = "AutoLot";
        cnStrBuilder["Data Source"] = "(local)";
        cnStrBuilder["Connect Timeout"] = 30;
        cnStrBuilder["Integrated Security"] = true;

DrPurdum · June 6th, 2011, 08:26 AM

Have you tried omitting the connection string builder and directly build the connection string yourself? Page 412 shows how to do this plus giving you a web site for finding the connection strings. Try that and see if the direct connect works. If it doesn't, I'd start to think the DB didn't get installed properly.

scolty · June 6th, 2011, 08:39 AM

Would u recommend using the ConnectionStringBuilder or just the connection string as most of the examples i have found on the web seem to just use the connection string. Specifically im trying to find info on how to just connect to the server and then create a database but couldnt find any examples with the ConnectionStringBuilder.

On a side note, im a little confused as to how the connections are managed. I read that once they are closed they reside on the connection pool. Does this mean that i should always be checking the state of the connection to the database before i execute a method and if its closed i should call the same method as i did earlier to recreate the connection??

Im sorry if this doesnt make alot of sense, im still having some issues getting my head around this.

Thanks again

DrPurdum · June 6th, 2011, 11:24 AM

I always just use the connection string and work with the DB directly.

As far as managing the DB connection, I always release the connection after I'm done with whatever it was that I was doing with the DB. Depending upon the DB used, if you keep control of the DB, you could be locking it and preventing someone else from using it while you control the connection. If it isn't networked, no problem since you're the only user. However, I think it's a good habit to release any resource when you're done with it. You then have to reconnect the next time you want to use the DB. Personally, I think this is a "cleaner" approach to it.

scolty · June 6th, 2011, 11:43 AM

Aye ok, so if i had 3 separate methods in a class, you would enclose each method with the using keyword or explicitly call nameofconnection.close().

Thanks again for ur help

DrPurdum · June 6th, 2011, 03:11 PM

I would probably use a const definition of the connection string as a private member of the DB class and then write methods to open, use, and close the connection. I would probably also write a constructor with a string argument that would allow me to pass in a new connection string if I wanted to use one. That way, the default constructor would use the const connection string you defined privately within the class, but you could pass in a new connection string if you wanted to use the class with a different DB.

As is so often the case, there is no right answer...only one that serves your purposes.