Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning C# 3.0 : An Introduction to Object Oriented Programming ISBN: 978-0-470-26129-3
This is the forum to discuss the Wrox book Beginning C# 3.0 : An Introduction to Object Oriented Programming by Jack Purdum; ISBN: 9780470261293
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning C# 3.0 : An Introduction to Object Oriented Programming ISBN: 978-0-470-26129-3 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 4th, 2011, 10:00 AM
Authorized User
 
Join Date: Feb 2011
Posts: 21
Thanks: 2
Thanked 0 Times in 0 Posts
Default Connection problem between console program and MS SQL

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!!

Last edited by scolty; June 4th, 2011 at 10:07 AM.
Reply With Quote
  #2 (permalink)  
Old June 5th, 2011, 08:51 AM
Authorized User
 
Join Date: Feb 2011
Posts: 21
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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;
Reply With Quote
  #3 (permalink)  
Old June 6th, 2011, 09:26 AM
Friend of Wrox
Points: 1,104, Level: 13
Points: 1,104, Level: 13 Points: 1,104, Level: 13 Points: 1,104, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2008
Location: Indianapolis, IN, USA.
Posts: 224
Thanks: 0
Thanked 30 Times in 29 Posts
Default Connection problem

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.
__________________
Jack Purdum, Ph.D.
Author: Beginning C# 3.0: Introduction to Object Oriented Programming (and 14 other programming texts)
Reply With Quote
  #4 (permalink)  
Old June 6th, 2011, 09:39 AM
Authorized User
 
Join Date: Feb 2011
Posts: 21
Thanks: 2
Thanked 0 Times in 0 Posts
Default Hi

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
Reply With Quote
  #5 (permalink)  
Old June 6th, 2011, 12:24 PM
Friend of Wrox
Points: 1,104, Level: 13
Points: 1,104, Level: 13 Points: 1,104, Level: 13 Points: 1,104, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2008
Location: Indianapolis, IN, USA.
Posts: 224
Thanks: 0
Thanked 30 Times in 29 Posts
Default DB's and connection strings

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.
__________________
Jack Purdum, Ph.D.
Author: Beginning C# 3.0: Introduction to Object Oriented Programming (and 14 other programming texts)
Reply With Quote
  #6 (permalink)  
Old June 6th, 2011, 12:43 PM
Authorized User
 
Join Date: Feb 2011
Posts: 21
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old June 6th, 2011, 04:11 PM
Friend of Wrox
Points: 1,104, Level: 13
Points: 1,104, Level: 13 Points: 1,104, Level: 13 Points: 1,104, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2008
Location: Indianapolis, IN, USA.
Posts: 224
Thanks: 0
Thanked 30 Times in 29 Posts
Default Connection string

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.
__________________
Jack Purdum, Ph.D.
Author: Beginning C# 3.0: Introduction to Object Oriented Programming (and 14 other programming texts)
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
C++ program console to win32 how to do code? dreamboy Visual C++ 2005 3 April 1st, 2011 02:15 AM
MS Access to SQL server connection imaley Access VBA 3 February 16th, 2007 01:59 PM
take arguments when running program in console iulian C++ Programming 8 January 16th, 2007 09:17 AM
MS SQL Database Connection Adam H-W SQL Server 2000 0 July 20th, 2005 01:01 PM
remote connection to MS SQL 2000 db Adam H-W Classic ASP Databases 0 July 19th, 2005 03:54 AM



All times are GMT -4. The time now is 05:23 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.