如何从Sql C＃读取特定用户信息 [英] How Do I Read A Specific Users Info From Sql C#

问题描述

请参阅下面的代码和问题。

 使用系统; 
 使用 System.Collections.Generic; 
 使用 System.Linq; 
 使用 System.Text; 
 使用 System.Threading.Tasks; 
 使用 System.Data.SqlClient; 
 
 命名空间 EQCasTest 
 {
  class  Program 
 {
 静态  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）; 
 尝试 
 {
 myConnection.Open（）; 
} 
  catch （例外e）
 {
 Console.WriteLine（e.ToString（））; 
} 
 尝试 
 {
 SqlDataReader myReader =  null ; 
 SqlCommand myCommand =  new  SqlCommand（  select *来自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 （例外e）
 {
 Console.WriteLine（e.ToString（ ））; 
} 
} 
} 
} 

所以我的问题是我怎么会如果数据库包含以下数据，则检索特定用户fullnameemailx_iddepartmentpassword。示例，如果我想检索Nelsons数据但只知道他的用户名。



[x_id] [用户名] [姓名] [电子邮件] [部门] [密码]

501，johnr，John Rambo，john.rambo @ test.com，IT，密码

502，nelsonm1，Nelson Mandela，nelson.m @ test.com，HR，密码1

503，jblack，Joe Black，joe.black @ test.com，销售，密码2



关于我的代码的任何其他建议我将不胜感激：D不想从一开始就接受'坏习惯'

解决方案

一些建议：

• 始终使用参数化查询将参数传递给查询，永远不会串联连接。
  
  （在您发布的代码中不是问题，但编写的代码容易受到 SQL注入 [ ^ ]目前似乎非常受欢迎！）
• 请勿致电 reader.NextResult（）在内，而循环。它仅用于返回多组数据的查询，并且只有在您读完当前集后才会使用。
• 包装 SqlConnection ， SqlCommand 和 SqlDataReader 中的对象使用块。这样，即使抛出异常，他们的资源也会被清理。
• 正如RyanDev所说，不要在查询中使用 SELECT * 。指定要显式返回的列名。
• 对连接字符串进行硬编码将使您的代码难以维护。通常最好从应用程序配置文件中检索它：
  
  如何从C＃中的App.Config获取连接字符串 [ ^ ]
• Console.WriteLine 有一个重载，接受一个对象，所以不需要调用 .ToString（）从 SqlDataReader 返回的对象。
• 捕获所有异常通常不是一个好主意。但是，在 Main 方法中有一个 try ... catch 块，只记录错误并退出一个地方这没关系。

 试试 
 {
 使用（SqlConnection myConnection =  new  SqlConnection（  ...））
 使用（SqlCommand myCommand =  new  SqlCommand（  SELECT [x_id ]，[用户名]，[全名]，[电子邮件]，[部门]，[密码] FROM cas_user_ext WHERE [用户名] = @username））
 {
 myCommand.Parameters。 AddWithValue（  @ username，  nelsonm1）; 
 
 myConnection.Open（）; 
 使用（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 （例外e）
 {
 Console.WriteLine（e）; 
} 

选项1

添加where子句来查询' select * from cas_user_ext '



选项2：

使用LINQ或Entity Framework来挑选所需的用户。在这种情况下，你不需要你的代码，只需要一些类。

许多人认为SELECT *是一个坏习惯，因为除非你最终使用表中的每个字段，否则你是返回超过需要和成本资源。



如果你想要全名你做



< pre lang =SQL> SELECT fullname FROM cas_user_ext WHERE x_id = 4

或者获取多个字段

  SELECT  fullname，email  FROM  cas_user_ext  WHERE  x_id =  4  

您可能需要阅读一些SQL教程来学习如何进行基本的CRUD操作（创建，读取，更新，删除）

Please see code and question below.

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, [e-mail], I.T, password
502, nelsonm1, Nelson Mandela, [e-mail], HR, password1
503, jblack, Joe Black, [e-mail], 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

解决方案

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.

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

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.

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

SELECT fullname FROM cas_user_ext WHERE x_id = 4

Or to get multiple fields do

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)

这篇关于如何从Sql C＃读取特定用户信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！