c# Express and database

Nova_C

Hey.

I've been trying to write any kind of database access in c# using C# 2008 Express. I've tried PostGreSQL, MySQL, SQL Server Express and even reading from Excel spreadsheets. Whenever I get the connection set up and try to access data, the same exception pops up (In this case, it was for PostGreSQL):

A first chance exception of type 'System.InvalidOperationException' occurred in Npgsql.dll

Here's my code:

            NpgsqlConnection postConn = new NpgsqlConnection("Server=localhost;Port=5432;User Id=ldt;Password=ldt;Database=long_distance_tracker;");

            NpgsqlCommand postCommand = postConn.CreateCommand();
            NpgsqlDataReader reader;

            
                postCommand.CommandText = "SELECT * FROM quotes;";
           
                reader = postCommand.ExecuteReader();

            
                while (reader.Read())
                {
                    System.Console.Out.WriteLine("Check");
                    System.Console.Out.WriteLine(reader.GetString(2));
                }

I'm out of ideas. I've been trying to get any kind of data access going for three days. Nothing has stumped me before like this. I get this error when cutting and pasting example code. The exact same error. I don't know where to look next - my next step was to give up, but I have to store data somehow. Any way at all. I do not care.

Fuck, I'm about to just use text files if C# will let me do that.

Kakodaimonos

What's the full exception text and what's the stack trace when it pops? That should give you more details on what's going on.

If you do just want to use text, look at the FileStream classes, StreamWriter and StreamReader classes in .Net.

http://msdn.microsoft.com/en-us/library/system.io.aspx

Pheezer

I'm pretty well versed in Excel COM progamming so maybe if you give me the full error message and code re: xl I miiiight be able to help but most of my recent experience revolves around access 97 and excel 2k3. I lust for the days when I could roll out .NET apps.

wmelon

Well in your example, I never see you call the Open() function on your database connection. You'll need to call that function before you try to execute a command on that connection. Then you'll also need to call the Close() function after you're done with the datareader.

bowen

wmelon wrote: »

Well in your example, I never see you call the Open() function on your database connection. You'll need to call that function before you try to execute a command on that connection. Then you'll also need to call the Close() function after you're done with the datareader.

Assuming all the other parts of your code are okay, and the database server is up and running:

            NpgsqlConnection postConn = new NpgsqlConnection("Server=localhost;Port=5432;User Id=ldt;Password=ldt;Database=long_distance_tracker;");

            NpgsqlCommand postCommand = postConn.CreateCommand();
            NpgsqlDataReader reader;

            
                postCommand.CommandText = "SELECT * FROM quotes;";

                [COLOR="Lime"]postConn.Open();[/COLOR]

                reader = postCommand.ExecuteReader();

            
                while (reader.Read())
                {
                    System.Console.Out.WriteLine("Check");
                    System.Console.Out.WriteLine(reader.GetString(2));
                }

                [COLOR="Lime"]postConn.Close();[/COLOR]

Tejs

I'd give SQL Server express another chance, especially if you have SQL Server Management Studio. Try this code when connecting to a SQL Server instance.

Another good resource is connectionStrings.com - find any connection string template you want.

[COLOR="RoyalBlue"]string[/COLOR] connectionString = System.Configuration.[COLOR="Cyan"]ConfigurationManager[/COLOR].ConnectionStrings[[COLOR="Red"]"myConnectionString"[/COLOR]].ConnectionString;
[COLOR="Cyan"]SqlConnection[/COLOR] connection = [COLOR="RoyalBlue"]new[/COLOR] [COLOR="Cyan"]SqlConnection[/COLOR](connectionString); [COLOR="Lime"]/* If you are using a configuration file, pull from that. Otherwise, replace "connectionString" with your specific connection string */[/COLOR]
[COLOR="Cyan"]SqlCommand[/COLOR] command = [COLOR="Royalblue"]new[/COLOR] [COLOR="Cyan"]SqlCommand[/COLOR]([COLOR="Red"]"SELECT * FROM quotes"[/COLOR], connection);
[COLOR="Cyan"]SqlDataAdapter[/COLOR] adapter = [COLOR="Royalblue"]new[/COLOR] [COLOR="Cyan"]SqlDataAdapter[/COLOR](command);
[COLOR="Cyan"]DataSet[/COLOR] resultSet = [COLOR="Royalblue"]new[/COLOR] [COLOR="Cyan"]DataSet[/COLOR]();

[COLOR="RoyalBlue"]using[/COLOR](connection)
{
      [COLOR="Royalblue"]try[/COLOR]
      {
            connection.Open();
            adapter.Fill(resultSet);
      }
      [COLOR="Royalblue"]catch[/COLOR]([COLOR="Cyan"]Exception[/COLOR] e)
      {
            [COLOR="Lime"]// Inspect exceptions, such as Connection Failures or SQL Errors[/COLOR]
            [COLOR="Lime"]// Console.WriteLine(e.Message);[/COLOR]
      }
}

[COLOR="RoyalBlue"]foreach[/COLOR]([COLOR="Cyan"]DataRow[/COLOR] row in resultSet.Tables[0].Rows)
{
      [COLOR="RoyalBlue"]string[/COLOR] columnData = row[[COLOR="Red"]"MyColumnId"[/COLOR]].ToString();

      [COLOR="Lime"]// Manipulate the result set you received here[/COLOR]
}

bowen

I love the using statement.

Nova_C

Thanks for the replies.

SQL Server Express will not allow me to start a database - that is, I can only work from a database file. Neither the management studio, nor Visual Studio, have been able to make live connections to a database instance of SQL Server.

With Excel, I can't use COM because I don't have Excel. I'll get you code for my attempt at using Ole DB to access it when I get home.

Connection.open() and Connection.close() have no effect on my code.

The full message is what I posted - I'll link the stacktrace, but for some reason the exception isn't handled in the IDE at all - it's simply written to the console and ignored.

By the way, how do I get the console to show while a program is running? It always showed for me before, but since I installed VC#2008 Express on this new install of Windows 7 it doesn't.

Ganluan

By Console, do you mean the actual console portion of the app (i.e., you're working on a Console App and not a background process app) or the Output Window?

If it's the former, make sure you've actually created a Console Application and not something like a Windows Forms app or a Class Library. It will also disappear quickly if you don't ask for user input or something else which will cause it to continue to display.

For the latter, it's under View -> Output.

As for the exception - is Visual Studio not halting execution when the exception is thrown? Virtually all exceptions thrown by the CLR will provide a Message property that gives actual information on what caused the exception, as well as a possible InnerException property which may have bubbled up.

If you're not seeing it, put a try/catch around your code with a breakpoint in the catch, which should let you examine the Exception being thrown.

Tejs

Nova_C wrote: »

Thanks for the replies.

SQL Server Express will not allow me to start a database - that is, I can only work from a database file. Neither the management studio, nor Visual Studio, have been able to make live connections to a database instance of SQL Server.

With Excel, I can't use COM because I don't have Excel. I'll get you code for my attempt at using Ole DB to access it when I get home.

Connection.open() and Connection.close() have no effect on my code.

The full message is what I posted - I'll link the stacktrace, but for some reason the exception isn't handled in the IDE at all - it's simply written to the console and ignored.

By the way, how do I get the console to show while a program is running? It always showed for me before, but since I installed VC#2008 Express on this new install of Windows 7 it doesn't.

When you installed SQL Server express, did you encounter any errors? Is the Sql Server Service running? When you try to connect, what error message do you get?

Also, what kind of project does this code live in? Did you create a console application and then add code to it? Or is this a class library in an ASP.NET project?

Guanluan is right, and since I don't see a try/catch block around this code (maybe it's just not copied from your solution), the debugger should halt when it encountered an unhandled exception. Have you been able to debug your project? It partially sounds like debugging might not be enabled for you.

bowen

By default SQL Server Express uses named pipes and pretty much disables everything else. That makes using connection strings and setting up a db connection all that more difficult for someone new to it.

Nova_C

Yeah, the unhandled exception is confusing me too. There is no try/catch block and the system does not inform me of it. The only reason I know it's happening is going back through the output after the program closes. In order for me to find where it's happening I had to comment out one line at a time until I saw the exception appear in the output.

SQL Server install had no errors and the service is running.

When I open management studio, it asks me to log into a database. When I browse databases, the list is empty. No local databases. The only way for me to work with data is to work from a .sdf database file, which Visual Studio does not recognize.

Tejs

Nova_C wrote: »

Yeah, the unhandled exception is confusing me too. There is no try/catch block and the system does not inform me of it. The only reason I know it's happening is going back through the output after the program closes. In order for me to find where it's happening I had to comment out one line at a time until I saw the exception appear in the output.

SQL Server install had no errors and the service is running.

When I open management studio, it asks me to log into a database. When I browse databases, the list is empty. No local databases. The only way for me to work with data is to work from a .sdf database file, which Visual Studio does not recognize.

Ah, I think you might be confused. You need to enter the name of the server you wish to connect to... the drop down only lists your recently connected to servers.

Enter ".\SQLEXPRESS" (assuming you named your instance SQLEXPRESS) into the server name field and use Windows Authentication. That should connect you to your local instance.

Jasconius

This is how ya do it in SQL2005. Helper.CommandObject only creates a storedproc command. That part will be different for you since you are doing inline queries.

But

using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)){}

Is the greatest.

SqlParameter param1 = new SqlParameter("@digitalproductID", digitalproductID);
        SqlCommand cmd = Helper.CommandObject("getdigitalproduct");
        cmd.Parameters.Add(param1);
        cmd.Connection.Open();

        using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
        {
            if (reader.HasRows)
            {
                retval = new DigitalProduct();
                reader.Read();
                retval.Name = reader.GetString(0);
                retval.FilePath = reader.GetString(1);

                return retval;
            }
            else
            {
                return null;
            }

        }

That's for pulling a single item.

I believe if you have multiple items you can do

while(reader.Read()) and it will loop through the rows automatically. If not then it's some other similarly named reader method.

Nova_C

YYYYYEEEEAAAAAAAAAAAAHHHHHHHHHH!

SQL Server Express 2008 was always working right (I was just an idiot and didn't know how to log in) and the code snippet by tejs worked (with a little editing) like a charm. Thanks for all your help guys!

PS. Tonight was the first time I've had a chance to work on this again. :P Yeah, it's been a little crazy.