How Do I Query Sql Tables From C#

Question

0.00/5 (No votes)

See more:

Hi all,

Sorry if this is a really stupid question but i'm new to c# and i have yet to find any decent tutorials for beginners.

What i'm trying to do is read and write to an existing SQL database.

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace EQCasTest
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection myConnection = new SqlConnection("user id=username;" +
                                           @"password=password;server=test-pc\sqlexpress;" +
                                           "Trusted_Connection=yes;" +
                                           "database=eqcas; " +
                                           "connection timeout=10");
            try
            {
                myConnection.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            try
            {
                SqlDataReader myReader = null;
                SqlCommand myCommand = new SqlCommand("select * from cas_user_ext",
                                                         myConnection);
                myReader = myCommand.ExecuteReader();
                while (myReader.Read())
                {
                    Console.WriteLine(myReader["fullname"].ToString());
                    Console.WriteLine(myReader["email"].ToString());
                    Console.WriteLine(myReader["x_id"].ToString());
                    myReader.NextResult();
                } 
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }
    }
}

This above code is my code i'm using to establish the connection and when it reads from the database i am only receiving one row of data which would be

John Rambo
john.rambo@ispowerfull.com
501

Now i want it to return all "fullname" "email" and "x_id" data to me instead of just 1 row. And also when i want to read all the columns for a specific "fullanme" user how would i do that?

Would appreciate any assistance.

Posted 8-Sep-14 0:14am

Ivan Lubbe

Add a Solution

Comments

Ivan Lubbe 8-Sep-14 7:01am

If i may take the time to ask another question related to my post. Lets say the database contained the following entries

[x_id] [fullname] [email] [department] [password]
501 John Rambo john.rambo@test.com I.T password
502 Nelson Mandela nelson.m@test.com HR password1
503 Joe Black joe.black@test.com Sales password2

And i want to retrieve Nelsons fullname, email, department and password. How would i achieve this, assuming i don't know his x_id.

3 solutions

Solution 3

As Syed Asif Iqbal said remove

myReader.NextResult();

after removing this you can get all record at a time.

For your second question, read all the columns for a specific "fullname" you can write linq:

XML

DataTable dt = new DataTable();
              dt.Load(myReader );
              var result = (from m in dt.AsEnumerable()
                             where m.Field<string>("fullname") == "Name"
                             select m).ToList();

Posted 8-Sep-14 0:52am

Sanchayeeta

Comments

Ivan Lubbe 8-Sep-14 7:19am

Could you please explain the above code? Its creating a new data table then loading it with data from myReader? please just explain the var result.

Sanchayeeta 8-Sep-14 11:10am

AS you said you want to retrieve all data for a specific fullname value, this linq query will retrieve all data specific to a particular Fullname value from datatable dt and it will save the data in result.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Accepted Answer · 2014-09-08T00:30:00

Solution 1

Comment out this line and try again.

myReader.NextResult();

Posted 8-Sep-14 0:30am

_Asif_

Comments

_Amy 8-Sep-14 6:44am

Exactly, +5!

Ivan Lubbe 8-Sep-14 6:47am

Ah thank you!

_Amy · Accepted Answer · 2014-09-08T00:43:00

NextResult() returns true if there are more result sets; otherwise it'll return false.

In this case, query is returning only one record set and breaking the loop.

Try this:

C#

while (myReader.Read())
{
    Console.WriteLine(Convert.ToString(myReader["fullname"]));//Convert.ToString() handles null values
    Console.WriteLine(Convert.ToString(myReader["email"]));
    Console.WriteLine(Convert.ToString(myReader["x_id"]));
}

--Amy

How Do I Query Sql Tables From C#

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us