Need database example code

[WDrago]

All,

I can't seem to find a good example of how to work with databases in C#. I'm using sqlserver and I can do simple things like insert and retrieve data, but when I am retrieving data I have to refer to the ordinal index values of the columns (reader[0], reader[1]) instead of the column names. I think this is stupid because I may not know what order the columns are in. Is there any way to modify this code to use column names instead of index values? Code sample is from: http://msdn.microsoft.com/en-us/library/dw70f090%28v=vs.80%29.aspx

Thanks,
Bill

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = GetConnectionString();
        string queryString = 
            "SELECT CategoryID, CategoryName FROM dbo.Categories;";
        using (SqlConnection connection = 
                   new SqlConnection(connectionString))
        {
            SqlCommand command = connection.CreateCommand();
            command.CommandText = queryString;

            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine("\t{0}\t{1}",
                        reader[0], reader[1]); //***I want to use column names here***
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }

    static private string GetConnectionString()
    {
        // To avoid storing the connection string in your code, 
        // you can retrieve it from a configuration file.
        return "Data Source=(local);Initial Catalog=Northwind;"
            + "Integrated Security=SSPI";
    }
}

[daniel955]

Here is how I get my data from the datareader

while (reader.Read())
{
    Console.WriteLine("\t{0}\t{1}",
    dataReader.GetString(dataReader.GetOrdinal("column1")), 
    dataReader.GetString(dataReader.GetOrdinal("column2"))); 
    //column1, and column2 are the column names from your dbtable

}

Source: College

Though there is also another way, by using a datatable
but I haven't tried it in C#. My sample is in VB.net

'Variables
Dim dt as DataTable
Dim x,y as String
Dim dr as DbDataReader

'DbDataReader's load function
dt.Load(dr)

'Sample code
x = dt.Rows(0)("column1").ToString
y = dt.Rows(0)("column2").ToString

dt.Load(dr) passes dr data to the datatable dt

Hope this helps :D

[Pamilerin]

Try this
This is a snippet of code from a call center system that i developed.
The Comm command works as you would in sql server and the parameters for text boxes if you have any.

  SqlConnection Conn = new SqlConnection(Program.Connect());
            try
            {
                Conn.Open();

            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.Message,"Exception");
            }
            SqlCommand Comm = new SqlCommand("Select [Login ID], Name, Password,Role from [Login Info] where [Login ID]=@LoginID", Conn);
            Comm.Parameters.Add(new SqlParameter("@LoginID", txtLoginID.Text));

            SqlDataReader dr = Comm.ExecuteReader();

Else try another

        SqlConnection con = new SqlConnection("Data Source=.;DataBase=employee;Integrated Security=true;");

        SqlCommand cmd = new SqlCommand("select * from emp", con);
        SqlDataAdapter adap = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        adap.Fill(ds);
        GridView1.DataSource = ds;
        GridView1.DataBind();

Work around both. One should work for you

[WDrago]

Wow, great answers! Thanks!

This: dataReader.GetString(dataReader.GetOrdinal("column1")) works great and was a simple mod to my existing code.

The other examples lead me to ask what is the difference between DataReader, DataTable, and DataAdapter, and what is the approcriate use of each?

My application is small (6 tables) and very low volume (4 users reading/writing sporadically during the day) and I'm not sure if I should continue using DataReader or if there's a better way.

(BTW, 25 years ago I designed databases for a living. Things are much different now and I'm just getting up to speed with all this new style database stuff (connection strings, etc...)

Thanks,
Bill

[Pamilerin]

For me I believe in 1)Connected Architecture and 2)disconnected architecture.

For a web based system in which the server should not be touched since there is no limit to the amount of people using the server a disconnected architecture where the system gets a dataset(like the part of the database you want) and you work on the dataset. The adapter is what gets the command from the database to fill the dataset. The dataset is later used to upate the database

Else the datareader works in connected architecture. Probably one on one or not too many people so that the server does not hang. Hope this helps