CODEWITHSUNDEEP
c#asp.netsql-server
Joe Scotto
Joe Scotto

Reputation: 10887

How to read each row and value with SqlCommand in C#?

I'm new to using C# and am now messing around with SQL Server connections. My connection is working and I can run queries, I just can't view any of the data that is returned. I've seen that you need to run command.ExecuteReader() to get the data back but I cannot get any other data than the first column and row.

Here is my code:

<%@ WebHandler Language="C#" Class="biquery_query" %>
using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;

public class biquery_query : IHttpHandler {

    public void ProcessRequest (HttpContext context) {
        var query = context.Request.QueryString["query"];
        query = "SELECT * FROM [hidden].[dbo].[hidden]";

        SqlConnection sqlConnection = new SqlConnection(@"Data Source=hidden;Initial Catalog=hidden;User ID=hidden;Password=hidden;");

        // Create Query Command
        SqlCommand command = new SqlCommand();
        command.CommandType = CommandType.Text;
        command.CommandText = query;
        command.Connection = sqlConnection;

        // Open connection and execute query
        sqlConnection.Open();
        command.ExecuteNonQuery();

        using(SqlDataReader rdr = command.ExecuteReader())
        {
            while(rdr.Read())
            {
                string app_id= rdr["app_id"].ToString();
                string app_os= rdr["app_os"].ToString();
                string date= rdr["date"].ToString();
                context.Response.Write(app_id);
            } 
        }

        sqlConnection.Close();
    }

    public bool IsReusable {
        get {
            return false;
        }
    }
}

My database table has 5 columns (app_id, app_os, date, unix, users).

Is there a way I can select rdr["app_id"][0] to get the first app_id or rdr["users"][30].

Any help would be greatly appreciated, thanks!

Upvotes: 5

Views: 13949

Answers (3)

Xavier J
Xavier J

Reputation: 4726

With a SqlDataReader, everything has to be done row-by-row - sequential access. The advantage with a SqlDataReader is that you can process a very large recordset without having all the data in memory all at once. The disadvantage is that you can't do as you're asking.

Your other alternative would be to drop the entire recordset into a DataTable or DataSet object by way of a SqlDataAdapter. Here's sample code from MS:

private static DataSet SelectRows(DataSet dataset, string connectionString, string queryString) 
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand(queryString, connection);
        adapter.Fill(dataset);
        return dataset;
    }
}

Upvotes: 1

csharpbd
csharpbd

Reputation: 4076

When you have a DataReader you can read your data by using your column name. I've just read as string, you can convert as you want. Please check this:

public class test
{
    public string app_id { get; set; }
    public string app_os { get; set; }
    public string date { get; set; }
    public string users { get; set; }
}

List<test> list = new List<test>();
using (SqlDataReader rdr = command.ExecuteReader())
{
    while (rdr.Read())
    {
        test ob = new test();
        ob.app_id = rdr["app_id"].ToString();
        ob.app_os = rdr["app_os"].ToString();
        ob.date = rdr["date"].ToString();
        ob.users = rdr["users"].ToString();
        list.Add(ob);
    }
}

Please check above code, I've created a class name test and read full data from the DataReader into list. Now you can use your data for further processing.

You can validate your data when reading from Database, check below:

//ob.app_id = rdr["app_id"].ToString();
// if app_id is string
ob.app_id = (rdr["app_id"] == DBNull.Value) ? string.Empty : Convert.ToString(rdr["app_id"]);
// if app_id is int
ob.app_id = (rdr["app_id"] == DBNull.Value) ? 0 : Convert.ToInt64(rdr["app_id"]);

Upvotes: 3

MrZander
MrZander

Reputation: 3120

ExecuteNonQuery is used when the query is not going to return a resultset (like INSERT, or UPDATE).

Remove the call to ExecuteNonQuery and just use ExecuteReader.

Upvotes: 7

Related Questions