Displaying values from a SQL database in C# console application

Question

I am trying to connect to a database in C# and bring up certain data points. The database has numerous columns and tables and I simply want to display them in the console using Writeline(). Below is what I have so far. The code runs without error but does not display anything either.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data.Sql;

namespace SQLIntro
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection connection = new SqlConnection("Server=[SQ_SIXTEEN];Database=[PocketCentral];User ID=[un];Password=[pw];Trusted_Connection=true"))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand("SELECT * FROM tbl_Terminal", connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                Console.WriteLine(reader.GetValue(i));
                            }
                            Console.WriteLine();
                        }
                    }
                }
            }
        }
    }
}

The one thing is where the column info would go...The SQL command? or in the while loop?.

where the column info would go <= You should write this out hard coded (or from code anyway). Sql queries executed against a relational database should be executed knowing which columns will be returned. Using select * is considered bad practice, the schema could change (column added, columns moved, ...) instead specify the columns and if you want to display where the data is originating from in the app then code that in. — Igor
– Igor, Commented Jul 19, 2018 at 17:27
Are you getting any exceptions? The using statement hides some exceptions I prefer to use try/catch and display the exceptions. — jdweng
– jdweng, Commented Jul 19, 2018 at 17:29

Cetin Basoz · Accepted Answer · 2018-07-19 17:38:57Z

1

That code would actually throw an exception. You have surrounded the names with brackets in your connection string which would cause the connection to fail. Change it to:

using (SqlConnection connection = new SqlConnection("Server=SQ_SIXTEEN;Database=PocketCentral;Trusted_Connection=true"))

Note that when Trusted_Connection is true (windows authentication) you don't use UserID and Password.

EDIT: Additional notes.

You would normally know your data content (your columnname and types). From SQL viewpoint it is suggested that you should list all your columns. ie: Instead of simply using "select *" use something like "select firstName, lastName, ... from ...".

As per the reader, instead of reader.GetValue[i] you use reader[index] and cast the type to what it should be like:

(int)reader[0]
(DateTime)reader["OrderDate"]

Integer indexing is faster but depends on column position where string indexing with column name is more readable.

EDIT2: Don't skip looking into LINQ. It is easier IMHO.

edited Jul 19, 2018 at 17:38

answered Jul 19, 2018 at 17:33

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Norbert Ratiu Over a year ago

This was the issue, I removed the square brackets and the console displayed everything. The console was not throwing an exception though with the square brackets, it simply remained blank. i will also look into LINQ as well. Thank you

Collectives™ on Stack Overflow

Displaying values from a SQL database in C# console application

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related