CODEWITHSUNDEEP
c#asp.netsql-server-2008
MA Moselhie
MA Moselhie

Reputation: 49

Select all values in a column from SQL Server 2008 database?

Could someone tell me how to get all the values in a column from my SQL Server database?

I have a table with the following structure:

ELECTS

And I want to retrieve all values from the SSN column and store them in an integer array in an ASP.NET project.

I using Visual Studio 2010 Ultimate SP1 2010 and SQL Server 2008 SP3.

Upvotes: 2

Views: 3307

Answers (2)

Jeff Ogata
Jeff Ogata

Reputation: 57783

You might also take a look at Dapper, which is used on the StackOverflow site, and is available as a NuGet package (search for "Dapper dot net").

With Dapper you can use the following to get an IEnumerable<int>:

IEnumerable<int> allSSN;

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    allSSN = SqlMapper.Query<int>(connection, "SELECT SSN FROM dbo.Elects");
}

As a personal note, this was my first time taking Dapper for a spin; before this, I would have used a SqlCommand and SqlDataReader as per @marc_s's answer, but I believe I'll be using Dapper in my future projects.

Upvotes: 0

marc_s
marc_s

Reputation: 754488

string yourConnectionString = "......";
List<int> allSSN = new List<int>();

string sqlStmt = "SELECT SSN FROM dbo.Elects";

using(SqlConnection conn = new SqlConnection(yourConnectionString) 
using(SqlCommand cmd = new SqlCommand(sqlStmt, conn))
{
    conn.Open();

    using(SqlDataReader rdr = cmd.ExecuteReader())
    {
        while(rdr.Read())
        {
           int ssn = rdr.GetInt32(0);
           allSSN.Add(ssn);
        }

        rdr.Close();
    }

    conn.Close();
}

At the end of this code, you have all the SSN column values in a List<int> for your use

Upvotes: 5

Related Questions