how to retrieve data from sql server to DataTable using c#

Question

    string j = "Data Source=FUJITSU-PC\\SQLEXPRESS;Initial Catalog=attendance_system;Integrated Security=True";
    DataTable dt = new DataTable();
    using (SqlConnection cn = new SqlConnection(j))
    {
        SqlCommand cmd = new SqlCommand("select dbo.faculty_status.username from dbo.faculty_status", cn);
        cn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        dr.Read();
        dt.Load(dr);
        cn.Close();
    }
    int x = dt.Rows.Count;
    foreach (DataRow row in dt.Rows)
    {
        foreach (var item in row.ItemArray)
        {
            string xx = item.ToString();
        }
    }

MySQL server database table

I used this code it wors but it does not show me my desire first row value it givs me out put like----

13-24516-2

what are you doing with xx? Since it's local variable, it could be getting overwritten with the second row? — Krishna Chaithanya Muthyala
– Krishna Chaithanya Muthyala, Commented May 9, 2016 at 10:02
I don't see that code here. Can you edit the post and give that code? — Krishna Chaithanya Muthyala
– Krishna Chaithanya Muthyala, Commented May 9, 2016 at 10:10

Krishna Chaithanya Muthyala · Accepted Answer · 2016-05-09 10:19:38Z

1

I think the problem is the way you are reading. Try this:

    string j = "Data Source=FUJITSU-PC\\SQLEXPRESS;Initial Catalog=attendance_system;Integrated Security=True";
    using (SqlConnection cn = new SqlConnection(j))
    {
        SqlCommand cmd = new SqlCommand("select dbo.faculty_status.username from dbo.faculty_status", cn);
        cn.Open();
        using (var reader = cmd.ExecuteReader())
        {
            while(reader.Read())
            {
                Console.WriteLine(reader["username"]);
            }
        }
    }

Let me know if it works!

answered May 9, 2016 at 10:19

Krishna Chaithanya Muthyala

6512 gold badges6 silver badges14 bronze badges

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

1 Comment

Krishna Chaithanya Muthyala Over a year ago

good to know...and please try to use stored procedures instead of direct commands like these...

Sinnich · Accepted Answer · 2016-05-09 10:10:29Z

0

It gives you the last row. the XX string is first loaded with the first results, afterwards on the second row it overwrites the first row with the second row, this goes on as long as there is rows.

if you want the first rows the be displayed you could use somthing like:

    DataTable dt = new DataTable();
    string xx = dt.Rows[0].ToString();

or

foreach (DataRow row in dt.Rows)
{
        foreach (var item in row.ItemArray)
        {
            Console.WriteLine(item.ToString());
        }
}

dt.Rows[the row you want to retrieve]

answered May 9, 2016 at 10:10

Sinnich

3425 silver badges17 bronze badges

1 Comment

Chowdhury Nayan Over a year ago

its not work.still it gives me second row..after second it works fine..it give me 3,4,5 and so on

R C · Accepted Answer · 2016-05-09 11:26:25Z

0

Try this code example:-

SqlCommand cmd = new SqlCommand("select dbo.faculty_status.username from dbo.faculty_status", cn);
DataTable dt = new DataTable();
SqlDataAdapter mda = new SqlDataAdapter(cmd);
mda.Fill(dt);

Let me know if it works!

answered May 9, 2016 at 11:26

R C

4714 silver badges17 bronze badges

Collectives™ on Stack Overflow

how to retrieve data from sql server to DataTable using c#

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related