返回一个int而不是一个数据表

Question

如何更改下面的语法以返回Int而不是数据表?我不需要一个数据表，它只是一个值，将在查询中返回。我是数据访问的新手。谢谢你的帮助。

 public DataTable GetMemberID(string guid)
   {
       string strConectionString = ConfigurationManager.AppSettings["DataBaseConnection"];
       //set up sql
       string StrSql = "SELECT MemberID FROM MEMBERS WHERE (Guid = @GuidID)";
       DataTable dt = new DataTable();
       using (SqlDataAdapter daObj = new SqlDataAdapter(StrSql, strConectionString))
       {
           daObj.SelectCommand.Parameters.Add("@GuidID", SqlDbType.Int);
           daObj.SelectCommand.Parameters["@GuidID"].Value = guid;
           //fill data table
           daObj.Fill(dt);
       }
       return dt;
   }

Answer 1

您可以使用SqlCommand代替SqlDataAdapter:

int memberId = 0;
using (var connection = new SqlConnection(conectionString))
using (var command = new SqlCommand(StrSql, connection))
{
    command.Parameters.Add("@GuidID", SqlDbType.Int).Value = guid;
    memberId = (int) command.ExecuteScalar();
}
return memberId;

Answer 2

用SqlCommand和ExecuteScalar代替DataTable:

string StrSql = "SELECT MemberID FROM MEMBERS WHERE (Guid = @GuidID)";
using(var cmd = new SqlCommand(sql, connection))
{
   cmd.Parameters.Add("@GuidID", SqlDbType.Int).Value = guid;
   return (int)cmd.ExecuteScalar();
}

Answer 3

   public int GetMemberID(string guid) 
   { 
       string strConectionString = ConfigurationManager.AppSettings["DataBaseConnection"]; 
       //set up sql 
       string StrSql = "SELECT MemberID FROM MEMBERS WHERE (Guid = @GuidID)"; 
       DataTable dt = new DataTable(); 
       using (SqlDataAdapter daObj = new SqlDataAdapter(StrSql, strConectionString)) 
       { 
           daObj.SelectCommand.Parameters.Add("@GuidID", SqlDbType.Int); 
           daObj.SelectCommand.Parameters["@GuidID"].Value = guid; 
           //fill data table 
           daObj.Fill(dt); 
       } 
       return Convert.ToInt32(dt["MemberID"][0]); 
   } 

Answer 4

代替:

 return dt;

使用

:

if (dt.rows.count > 0)
   return (int)dt.rows[0][0];

声明也需要修改为:

 public int GetMemberID(string guid)