How Do I Read A Specific Users Info From Sql C#

Question

2.67/5 (3 votes)

See more:

Please see code and question below.

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());
            }
        }
    }
}

So my question is how would i retrieve a specific users "fullname" "email" "x_id" "department" "password" if the database contained the following data. Example if i wanted to retrieve Nelsons data but only know his username.

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

Any other advice on my code in general would be much appreciated :D don't want to pick up 'bad habits' from the start

Posted 8-Sep-14 1:42am

Ivan Lubbe

Add a Solution

Comments

Ashi0891 8-Sep-14 12:10pm

"Example if i wanted to retrieve Nelsons data but only know his username."
you can use 'where' clause in somewhat following way
where fullname like '%'+@name+'%';
myCommand.Parameters.AddWithValue("@name", 'mandela');

this will help you get data for person having name containing 'mandela'. it will work only if data contains first name and last name and no last name would be first name for any person.
hope this helps.

Afzaal Ahmad Zeeshan 8-Sep-14 15:04pm

Well, I am going to rate this question 4 for being well structed and written.

5 solutions

Solution 3

This link will definitely help you

follow this link

Posted 8-Sep-14 2:15am

Ranjeet Patel

Comments

Afzaal Ahmad Zeeshan 8-Sep-14 15:04pm

If only a link would have helped him, he would have Googled it. He wants a solution.

Ranjeet Patel 9-Sep-14 1:31am

do you want me to copy and paste the code? The similar code can help him. hi just need to put his own variables and objects. and there are other solutions also provided. he can refer that too...

Afzaal Ahmad Zeeshan 9-Sep-14 2:26am

Yes, I want you to show some basics about the code and exlain what is being done there. I don't want you to copy paste the code, just explain the code, however if you wanted to post this only, it would have been a comment not an answer.

Ivan Lubbe 9-Sep-14 4:14am

Hi guys i appreciate the assistance. Unfortunately most tutorials are either too complex or seem to have a long way around a simple solution. And also much doesn't get explained. I find it much easier to try and understand existing code and learning from that working my way down the code and breaking it down. You will see my terminology is completely out too as i don't learn from scratch but as i try code something.

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)

Richard Deeming · Accepted Answer · 2014-09-08T02:56:00

Some suggestions:

Always use parameterized queries to pass parameters to a query, never string concatenation.
(Not a problem in the code you've posted, but writing code that's susceptible to SQL Injection[^] seems to be very popular at the moment!)
Don't call reader.NextResult() within the while loop. It's only intended to be used for queries which return multiple sets of data, and only once you've finished reading the current set.
Wrap the SqlConnection, SqlCommand and SqlDataReader objects in using blocks. That way, their resources will be cleaned up even if an exception is thrown.
As RyanDev said, don't use SELECT * in your queries. Specify the column names you want to return explicitly.
Hard-coding the connection string will make your code harder to maintain. It's usually better to retrieve it from the application configuration file:
How to get Connection String from App.Config in C#[^]
Console.WriteLine has an overload which accepts an object, so there's no need to call .ToString() on the object returned from the SqlDataReader.
It's usually not a good idea to catch all exceptions. However, having a try...catch block in the Main method which simply logs the error and exits is one place where this is OK.

C#

try
{
    using (SqlConnection myConnection = new SqlConnection("..."))
    using (SqlCommand myCommand = new SqlCommand("SELECT [x_id], [username], [fullname], [email], [department], [password] FROM cas_user_ext WHERE [username] = @username"))
    {
        myCommand.Parameters.AddWithValue("@username", "nelsonm1");

        myConnection.Open();
        using (SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
        {
            while (myReader.Read())
            {
                Console.WriteLine(myReader["x_id"]);
                Console.WriteLine(myReader["username"]);
                Console.WriteLine(myReader["fullname"]);
                Console.WriteLine(myReader["email"]);
                Console.WriteLine(myReader["department"]);
                Console.WriteLine(myReader["password"]);
            }
        }
    }
}
catch (Exception e)
{
    Console.WriteLine(e);
}

Herman<T>.Instance · Accepted Answer · 2014-09-08T01:50:00

Solution 1

Option 1
add where clause to query 'select * from cas_user_ext'

Option 2:
use LINQ or Entity Framework to pick the user desired. In that case you don't need your code but some classes only.

Posted 8-Sep-14 1:50am

Herman<T>.Instance

ZurdoDev · Accepted Answer · 2014-09-08T01:55:00

Solution 2

SELECT * is considered by many to be a bad habit because unless you end up using every field from the table you are returning more than is needed and that cost resources.

If you want full name you do

SQL

SELECT fullname FROM cas_user_ext WHERE x_id = 4

Or to get multiple fields do

SQL

SELECT fullname, email FROM cas_user_ext WHERE x_id = 4

You may want to go through some SQL tutorials to learn how to do basic CRUD operations (Created, Read, Update, Delete)

Posted 8-Sep-14 1:55am

ZurdoDev

Comments

Ivan Lubbe 8-Sep-14 8:07am

Thank you. Well assuming i don't know what the x_id of the user is? I have been trying to go through some tutorials but honestly i don't know where to start. They are either too complex for beginners or don't explain properly. I have been trying to read and write data from SQL for a week now and before that i haven't really touched SQL.

ZurdoDev 8-Sep-14 8:08am

You'll have to know something about the user to be able to narrow it down to a single user. Either name, email or something.

Ivan Lubbe 8-Sep-14 8:12am

So lets say i know the username of the user. Would the Query be like this?

SELECT fullname, email FROM cas_user_ext WHERE username = nelsonm ?

ZurdoDev 8-Sep-14 8:16am

Close. You'll need single quotes around 'nelsonm' however you'll want to use a parameterized query so that you are not vulnerable to SQL injections.

Do something like this:

SqlCommand myCommand = new SqlCommand("select fullname, email from cas_user_ext WHERE username = @username", myConnection);
myCommand.Parameters.AddWithValue("@username", 'nelsonm');
...

MariwanIT · Accepted Answer · 2014-09-08T07:22:00

you can do it in many ways :
1. Create a textbox and put the desired id then >> button to search.
button:

C#

bnt1_click(.....)
{
    SqlConnection conn;
    string select = "select * from YourTableNane where id='"+Int32.Parse(textbox.Text)+"'";
    sqlCommand cmd = new sqlcommand(select,conn);
    sqlDataReder reader = cmd.ExecuteReader();
    if(reader.HasRows)
    {
        while(reader.Read)
        {
            name = reader["name"].ToString();
            ..... 
        }
    }
    reader.close();
}

2. Instead you can change id to name or .....
I think this will definitely help you.
but respell the commands and codes

How Do I Read A Specific Users Info From Sql C#

5 solutions

Solution 4

Solution 1

Solution 2

Solution 3

Solution 5

Add your solution here

Preview 0