4 回答
TA贡献1811条经验 获得超4个赞
如果我们从过时 ArrayList的切换到类似的东西IEnumerable<T>:
public static IEnumerable<IDataRecord> DbQueryToArray(string sql) {
if (null == sql)
throw new ArgumentNullException(nameof(sql));
//TODO: do not hardcode connetcion string but read it (say, from Settings)
string SqlCString = "connString";
//DONE: Wrap IDisposable into using
using (SqlConnection connection = new SqlConnection(SqlCString)) {
connection.Open();
//DONE: Wrap IDisposable into using
using (SqlCommand command = new SqlCommand(sql, connection)) {
//DONE: Wrap IDisposable into using
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read()) {
yield return reader as IDataRecord;
}
}
}
}
}
那么您可以使用Linq来查询result:
var a = DbQueryToArray("Select CLIENTNO, ACCOUNT_Purpose from audit.ACCOUNTS_AUDIT");
Assert.IsTrue(a.Any(record =>
Convert.ToString(record["CLIENTNO"]) == "some value"));
Assert.IsTrue(a.Any(record =>
Convert.ToString(record["ACCOUNT_Purpose"]) == "some other value"));
如果你不想多次执行查询,你可以具体化结果:
var a = DbQueryToArray("Select CLIENTNO, ACCOUNT_Purpose from audit.ACCOUNTS_AUDIT")
.ToList();
Assert.IsTrue(a.Any(record => Convert.ToString(record[0]) == "some value"));
Assert.IsTrue(a.Any(record => Convert.ToString(record[1]) == "some other value"));
最后(见下面的评论),如果我们想测试任何记录中的任何字段是否具有该值:
var a = DbQueryToArray("Select CLIENTNO, ACCOUNT_Purpose from audit.ACCOUNTS_AUDIT")
.SelectMany(line => {
// Flatten the cursor into IEnumerable<String>
string[] result = new string[line.FieldCount];
for (int i = 0; i < result.Length; ++i)
result[i] = Convert.ToString(line[i]);
return result;
});
a.Any(item => item == "some value");
TA贡献1862条经验 获得超7个赞
这是因为你只读了读者的第一个值。Reader.Read()
逐行读取每一行,这Convert.ToString(reader[0]))
意味着您要将第一列作为字符串读取。
TA贡献1825条经验 获得超4个赞
使用DataTableandSqlDataAdapter以表格形式获取查询结果。是这样的:
string connString = @"your connection string here";
string query = "select * from table";
DataTable dataTable = new DataTable();
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
// create data adapter
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(dataTable);
conn.Close();
da.Dispose();
然后您可以使用dataTable对象来查看特定值是否存在。
TA贡献1831条经验 获得超4个赞
最佳做法是首先检查阅读器是否有行
reader.HasRows
然后关闭阅读器和连接
你的代码应该是这样的:
public static ArrayList DbQueryToArry()
{
string SqlCString = "connString";
SqlConnection connection = null;
ArrayList valuesList = new ArrayList();
connection = new SqlConnection(SqlCString);
using (connection)
{
connection.Open();
SqlCommand command = new SqlCommand("Select CLIENTNO, ACCOUNT_Purpose from audit.ACCOUNTS_AUDIT", connection);
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
valuesList.Add(Convert.ToString(reader[0]));
valuesList.Add(Convert.ToString(reader[1])); // add to valuelist
}
}
reader.Close(); // close reader
} //dispose connection
return valuesList;
}
- 4 回答
- 0 关注
- 95 浏览
添加回答
举报