Connecting to a DB in service

[rotten69]

Hello everyone,

I'm just having a problem that is stopping me from doiing my work and I've tried fixing it. I don't know what's really wrong with what I've got typed in visual studio.

string connString = @"Data Source=.\SQLEXPRESS;AttachDbFilename='C:\..................\Database1.mdf'; Integrated Security=True; User Instance=True";
                            
        SqlConnection conn = new SqlConnection();
       
        conn.ConnectionString = connString;  
        conn.Open();

--- > the word "conn" is underlined in visual studio so this means that I cannot connect to the BD I created. I have got my DB in my service section instead of having in my application.

Your help is very much appreciated.

[JuhaW]

Do you running c# program in windows service. The changin user of service should help. Other option is to use Sql authentication.

Juhaw

[rotten69]

Yes, I am running c# program on Windows but the thing is when I instantiate a DBconnection,for example, SqlConnection conn = new SqlConnection();
conn.ConnectionString = connString; I can not use the object "conn" for some reasons. I also rebuilt the service and the entire project and nothing seems to be fixing the problem

[Momerath]

Post all the code in your connection method.

[rotten69]

public class Service1 : System.Web.Services.WebService
    {
       
         string connString = @"Data Source=.\SQLEXPRESS;AttachDbFilename='C:\.....\ADO.NET\App_Data\Database1.mdf';
                            Integrated Security=True; User Instance=True";
                            
        SqlConnection conn = new SqlConnection();
       
        conn.ConnectionString = connString;
        conn.Open();

       
       /*
        string sQueryString = "SELECT users FROM users";
        OleDbCommand myCommand = new OleDbCommand(sQuery, conn);
        OleDbDataReader myReader = myCommand.ExecuteReader();

        while (myReader.Read() )
        {
            Console.WriteLine(myReader.GetString(0) );
        }
        
        myReader.Close(); */                            If I'm querying my database, do I close the query command at the end (after all methods)or before the methods?
        
        
        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }

conn.Close();

[rotten69]

Your help is pretty much appreciated. And, thanks in advance.

[Momerath]

I don't see anything obviously wrong, what error is it telling you? You said some of your code had a red underline, what does the tooltip say when you hover the mouse over it?

[nick.crane]

Your connection string uses "User Instance=True".
Is this enabled in your SQL Express installation?
MSDN - User Instance=True causes invalid connection string
See the link below for a whole load of connection string examples.
Connection strings for SQL Server 2008

[rotten69]

OK. When I hover on the underlined objs, the compiler says "Conn is 'field' but used like a type".. Any idea what it is ? how to fix it>?

[nick.crane]

I've just noticed that line 10 and 11 are in the class body.
You can not execute object methods at this point.
They should be in a method!
[Edit]
Line 8 can be changed to

SqlConnection conn = new SqlConnection(connString);

Then you should not need line 10, but line 11 (and all the commented code) needs to be moved to a method.

[rotten69]

Cool. Hmmm, what's the difference between ADO.net and the sql way of querying a DB?

What classes do I need to use for each of them?

[JuhaW]

Why don't you use database as normal way. When referensing file there are some limitation. This type of connection is primarily for debugging for visual studio.

Attach mdf file to engine using management studio and use instead following connection string:

Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=mybase;Data Source=.\sqlexpress

If this does not work check out executing username with:

WindowsIdentity.GetCurrent().Name;

Now you can add right for user.

[rotten69]

Hmmm, I'm required to use ADO.NET database for my little project that is a user account management. Once I have it up and running then I need to use LINQ to SQL. I'm very new to c# so I don't know that much about the different classes and things. I'd be very thankful if someone takes the time to explain the major differences between those ways/classes. What packages do I need to import into my service in order to use ADO.NET or LINQ to SQL?

And, I am not sure how this would work. I think I need to invoke different methods when a user clicks on the button in the registration form. So, I'd have a few methods such as checking user, updating user and deleting user. So, if user is existing in my DB then allow them to do certain things like updating their info or even deleting their info. If not, then make them register. Any ideas how to go about this small system?

CHEERS,

[rotten69]

Anyone there to take the time to explain that and be eventually thanked for the time as well :)