我整理了以下方法：public static ArrayList DbQueryToArry() { string SqlCString = "connString"; SqlConnection connection = null; ArrayList valuesList = new ArrayList(); connection = new SqlConnection(SqlCString); connection.Open(); SqlCommand command = new SqlCommand("Select CLIENTNO, ACCOUNT_Purpose from audit.ACCOUNTS_AUDIT", connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { valuesList.Add(Convert.ToString(reader[0])); } return valuesList; }我希望能夠運(yùn)行這樣的斷言：var a = DbQueryToArry(); Assert.IsTrue(a.Contains("some value"));給定讀者 [0]valuesList.Add(Convert.ToString(reader[0]));我只將第一列 ( CLIENTINFO) 放入數(shù)組中，而不是第二列 (ACCOUNT_Purpose)。我應(yīng)該如何修改代碼以獲得兩者？此外，返回值可以是 String 或 Int，那么我當(dāng)前的代碼版本是否應(yīng)該同時(shí)處理這兩者？提前致謝。
4 回答
波斯汪
TA貢獻(xiàn)1811條經(jīng)驗(yàn) 獲得超4個(gè)贊
如果我們從過時(shí) 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來查詢r(jià)esult：
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"));
如果你不想多次執(zhí)行查詢，你可以具體化結(jié)果：
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"));
最后（見下面的評(píng)論），如果我們想測(cè)試任何記錄中的任何字段是否具有該值：
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");
牧羊人nacy
TA貢獻(xiàn)1862條經(jīng)驗(yàn) 獲得超7個(gè)贊
這是因?yàn)槟阒蛔x了讀者的第一個(gè)值。Reader.Read()逐行讀取每一行，這Convert.ToString(reader[0]))意味著您要將第一列作為字符串讀取。
鳳凰求蠱
TA貢獻(xiàn)1825條經(jīng)驗(yàn) 獲得超4個(gè)贊
使用DataTableandSqlDataAdapter以表格形式獲取查詢結(jié)果。是這樣的：
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對(duì)象來查看特定值是否存在。
慕容708150
TA貢獻(xiàn)1831條經(jīng)驗(yàn) 獲得超4個(gè)贊
最佳做法是首先檢查閱讀器是否有行
reader.HasRows
然后關(guān)閉閱讀器和連接
你的代碼應(yīng)該是這樣的：
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 關(guān)注
- 171 瀏覽
添加回答
舉報(bào)
0/150
提交
取消