C# & SQL 无法从权限表中提取数据

Question

我创建了一个权限数据库，其中有10个数据点。我可以通过程序将数据插入到这个表中，但当我试图提取数据时，这让我很头疼。我尝试过使用Using循环，但每次都会出错，所以我试着去做超基础的，一行一行地提取数据，但它根本不起作用。我的目标是将所有数据拉入一个列表变量中，以便以后可以调用每个单独的权限。除了第一个Emp_ID是Int.外，每个权限中存储的只是Text True或False

Con是我的连接脚本，它运行得很好，就像它在程序中的其他地方一样。

Settings.Emp_ID是当前登录用户的Emp_ID。这意味着我们可以在提取数据时跳过权限中的Emp_IID，但我曾尝试执行EXCLUDE或skip，但每次都失败了。

SQL格式，Emp_ID=INT，所有其他=文本

关于错误：

System.NullReference Exception'对象引用未设置为对象的实例。

namespace TMS
{
public partial class Login_Form : Form
{
string[] Data;
void verify()
{
SqlDataReader rdr = null;
SqlCommand cmd = new SqlCommand("SELECT * FROM Permissions WHERE Emp_ID = '"
+ Settings.Emp_ID + "'", Con);
try
{
Con.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int Emp_ID = (int)rdr["Emp_ID"];
Data[0] = (string)rdr["Check_Out"];
Data[1] = (string)rdr["Check_In"];
Data[2] = (string)rdr["Self_His_Tool"];
Data[3] = (string)rdr["Self_His_User"];
Data[4] = (string)rdr["Tool_His"];
Data[5] = (string)rdr["User_His"];
Data[6] = (string)rdr["Add_Users"];
Data[7] = (string)rdr["Add_Tools"];
Data[8] = (string)rdr["Remove_Users"];
Data[9] = (string)rdr["Remove_Tools"];
}
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (Con != null)
Con.Close();
}
}

Answer 1

Data数组仍然是null，因为这一行实际上并没有创建数组对象：

string[] Data;

它所做的只是创建一个变量，将来可能会引用数组对象。

稍后，当你有这个和类似的行时：

Data[0] = (string)rdr["Check_Out"];

最终会出现NullReferenceException。Data仍然为空，因此不允许尝试访问Data[0]。

我们想要更像这样的东西：

public class Permissions
{
//probably these should be "bool", but I adapted the types from the old code
public string Check_Out {get;set;}
public string Check_In {get;set;}
public string Self_His_Tool {get;set;}
public string Self_His_User {get;set;}
public string Tool_His {get;set;}
public string User_His {get;set;}
public string Add_Users {get;set;}
public string Add_Tools {get;set;}
public string Remove_Users {get;set;}
public string Remove_Tools {get;set;}
}
// ...
Permissions Data;
Permissions verify()
{
string SQL = "SELECT * FROM Permissions WHERE Emp_ID = @Emp_ID";
// Do NOT re-use the some connection object throughout an app or class!
// Only re-use the connection string.
// using directive will ensure connection is closed, so no need for finally block
using var con = new SqlConnection("connection string here");
using var cmd = new SqlCommand(SQL, con);
// Do NOT use string concatation to substitute data into a query!
cmd.Parameters.Add("@Emp_ID", SqlDbType.Int).Value = Settings.Emp_ID;
// don't need a "try" if there's no catch or finally
con.Open();
rdr = cmd.ExecuteReader();
Permissions result = null;
if (rdr.Read()) //Don't need "while" if we only expect one record
{
result = new Permissions();
int Emp_ID = Settings.Emp_ID;
result.Check_out =(string)rdr["Check_Out"];
result.Check_In = (string)rdr["Check_In"];
result.Self_His_Tool = (string)rdr["Self_His_Tool"];
result.Self_His_User = (string)rdr["Self_His_User"];
result.Tool_His = (string)rdr["Tool_His"];
result.User_His = (string)rdr["User_His"];
result.Add_Users = (string)rdr["Add_Users"];
result.Add_Tools = (string)rdr["Add_Tools"];
result.Remove_Users = (string)rdr["Remove_Users"];
result.Remove_Tools = (string)rdr["Remove_Tools"];
}
return result;
}