1.使用SqlDataReader读数据
public bool CheckResultFromDatabase(string guid)
{
string connStr = "Data Source = localhost; Initial Catalog = TestDB; User ID = sa; Password = Abc12345";
// Create connection
SqlConnection Myconnect = new SqlConnection(connStr);
Myconnect.Open();
// Build up query
StringBuilder sb = new StringBuilder();
sb.Append($"select id, name from dbo.table_a where id = '{guid}';");
sb.Append($"select student_id, student_name from dbo.student where student_name = 'zhangsan';");
string sql = sb.ToString();
SqlCommand sqlQuery = new SqlCommand(sql, Myconnect);
int rowCount = 0;
bool [] checkresult = { false, false };
int expectedResultNum = 2;
int currentResult = 0;
using (SqlDataReader reader = sqlQuery.ExecuteReader())
{
do
{
rowCount = 0;
while (reader.Read())
{
rowCount++;
}
if (rowCount == 1)
{
checkresult[currentResult++] = true;
if (currentResult == expectedResultNum)
break;
}
} while (reader.NextResult()); // go to result of next sql query
reader.Close();
}
Myconnect.Close(); // close databse
return checkresult[0] && checkresult[1];
}
2.使用SqlDataReader读数据
public bool CheckResultFromDatabaseAdapter(string guid)
{
string connStr = "Data Source = localhost; Initial Catalog = TestDB; User ID = sa; Password = Abc12345";
bool resultConfig = false;
bool resultSummary = false;
using (SqlConnection conn = new SqlConnection(connStr))
{
string strSql = $"select id, name from dbo.table_a where id = '{guid}';";
using (SqlDataAdapter adapter = new SqlDataAdapter(strSql, conn))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
if (dt.Rows.Count == 1)
{
resultConfig = true;
}
//dt.Rows[0][1] //1st record 1st row
//foreach (DataRow dataRow in dt.Rows)
//{
// Console.WriteLine(dataRow["SimulationId"]);
//}
}
strSql = $"select student_id, student_name from dbo.student where student_name = 'zhangsan';";
using (SqlDataAdapter adapter = new SqlDataAdapter(strSql, conn))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
if (dt.Rows.Count == 1)
{
resultSummary = true;
}
}
}
return resultConfig & resultSummary;
}
3. 两种方法的对比
上面的两段代码,功能相同,即:从两张表中读取符合某一条件的数据,如果各自的记录数都为1,则返回true,否则返回false。
SqlDataReader的特点是:
- 多个查询可以一次性做完,也必须一次性做完。统一返回再通过NextResult()来切换到下一个结果集。这样最显著的好处是减少了client和db server的交互次数,某些情况下能大幅提高程序的效率。
- 没有RowCount属性或方法,如果要知道某一个结果集中记录的条数,只能遍历以后再自行统计。总之,不太友好。
说完前者,SqlDataAdapter的特点自然就显现出来了:
- 接口丰富,对编程者而言,方便许多。
- 可以多次查询,有助于提高程序的易读性。