SQL Help

[xeroized]

Using the following Code, the value returned is System.Data.SqlClient.SqlDataReader rather than the data in the SQL Table. Can someone tell me how to get the data from the table, and why this doesnt work as intended? I am new with connecting C# to SQL so any advise is appreciated.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            try
            {
                SqlCommand comm = new SqlCommand();
                comm.Connection = new SqlConnection(
                   "Data Source=(local); Integrated Security=SSPI");
                String sql = @"use RAPOS_Replication SELECT * from XmlPartner";
                comm.CommandText = sql;
                comm.Connection.Open();
                SqlDataReader cursor = comm.ExecuteReader();
                while (cursor.Read())
                    label1.Text = (cursor.ToString());
                MessageBox.Show(cursor.ToString());
                comm.Connection.Close();
            }
            catch (Exception error)
            {
                Console.WriteLine(error.ToString());
            }


        }
    }
}

[szlamany]

I don't have an C# examples - but in VB it's this

                    Using sdrReader As SqlDataReader = cmd.ExecuteReader
                        While sdrReader.Read
                            m_dcxE.dcxCKeys = New dcxEngine.dcxC(CInt(sdrReader(0)), sdrReader(1).ToString)
                        End While
                    End Using

Each "column" must be indicated

something like

MessageBox.Show(cursor(0).ToString());

or is it []'s in C#

MessageBox.Show(cursor[0].ToString());

I do a mix of VB and C++/Javascript - not much C#...

[dee-u]

Something like this, depends on what type of column. This gets the 1st column and assuming it is a string:

while (cursor.Read())
                    label1.Text = (cursor.GetString(0))

[detlion1643]

You could also loop through all the fields (columns) if you need to. Another good rule about Connections, Commands, Readers, etc is to wrap them in using blocks.

using(SqlConnection sqlConn = new SqlConnection())
	{
		sqlConn.Open() //open connection
		using(SqlCommand sqlCommand = new SqlCommand(@"query here", sqlConn))
		{
			using(SqlDataReader sqlReader = sqlCommand.ExecuteReader())
			{
				if(sqlReader.HasRows)
				{
					while(sqlReader.Read())
					{
						//reader[int index].ToString(); = value of data in that field of the index, 0=field 1, 1=field 2, etc
						//or loop the fields
						for(int i=0; i<sqlReader.FieldCount; i++)
						{
							//reader.GetName(i); = field name
							//reader[i].ToString(); = same as above, gets the data/value for that field
						}
					}
				}
			}
		}
	}

[xeroized]

Something like this, depends on what type of column. This gets the 1st column and assuming it is a string:

while (cursor.Read())
                    label1.Text = (cursor.GetString(0))

That's almost exactly what I was looking for. The only problem I have now is that the data returned is Enterprise01, which is the first row of the table, how do I return the entirety of the table? The attached image is the other data in the table so you can see exactly what I am talking about.


Thank you for the help so far, I definitely appreciate it.

[detlion1643]

Each time you iterate through the

while(readervariable.Read()) {}

you effectivly iterate through the rows.

int i = 0;
using(SqlCommand sqlCommand = new sqlCommand(@"query here"))
{
	using(SqlDataReader sqlReader = sqlCommand.ExecuteReader())
	{
		while(sqlReader.Read())
		{
			switch(i)
			{
				case(0): //row 1
					break;
				case(1): //row 2
					break;
				case(2): //row 3
					break;
			}
			i++;
		}
	}
}

[dee-u]

That's almost exactly what I was looking for. The only problem I have now is that the data returned is Enterprise01, which is the first row of the table, how do I return the entirety of the table? The attached image is the other data in the table so you can see exactly what I am talking about.


Thank you for the help so far, I definitely appreciate it.

As I've said, my sample code just gets the FIRST column (index 0), to get the other columns you just have to use the other indexes (1, 2,3,etc).

cursor.GetString(1) //second column
cursor.GetString(2) //third column

[jmcilhinney]

I'm wondering whether you actually should be using a data reader at all. A data reader provides read-only, forward-only access to the result set of a query. It's generally used when you want to retrieve data and process it one record at a time, discarding each record as you move onto the next. Is that what you want? If you want to display the data then you may or may not be best to use a data reader but you probably shouldn't be calling Read. Try following the CodeBank link in my signature and checking out my thread on Retrieving & Saving Data. It shows how use a data reader to access data record by record, how to use a data reader to populate a DataTable with data that won't be edited and also how to use a data adapter to populate a DataTable with data that will be edited.

[wakawaka]

Depending on what you want to do, it is probably easier to load it into a data table and work with it that way.

var dt = new System.Data.DataTable();

dt.Load(dataReader);

foreach (var row in dt.Rows)
{
    var partnerName = row["partnername"].ToString();
    //... so on and so forth
}

[jmcilhinney]

Depending on what you want to do, it is probably easier to load it into a data table and work with it that way.

var dt = new System.Data.DataTable();

dt.Load(dataReader);

foreach (var row in dt.Rows)
{
    var partnerName = row["partnername"].ToString();
    //... so on and so forth
}

I sure wish I'd thought of that three days ago. Oh, hang on... I did!

[Axcontrols]

Using the following Code, the value returned is System.Data.SqlClient.SqlDataReader rather than the data in the SQL Table. Can someone tell me how to get the data from the table, and why this doesnt work as intended? I am new with connecting C# to SQL so any advise is appreciated.

                label1.Text = (cursor.ToString());       // Here is the problem.

               // Do this instead
              label1.Text = cursor["COLUMNNAME"].ToString(); // see comments below

As others have said, a DataReader might not be ideal for all cases but I'm assuming OP has a reason for using the reader so I'll answer the OP's question.

Actually the OP's original code isn't so far off. Just that one line is cr*p. Of course he's getting the text "System.Data.SqlClient.SqlDataReader" because that what he asked for.

But if he wants the actual data from a column called COLUMNNAME using a datareader called cursor then he needs to do

label1.Text = cursor["COLUMNNAME"].ToString();

Obviously replace COLUMNNAME with the name of the actual column name - yes you need to do this separately for each column. And also understand that if NULL is returned then the ToString will trigger the exception. (Personally I rarely use ToString() for anything but that's a different topic)