Return an int instead of a datatable

Question

How would I change my syntax below to return an Int instead of a data table. I dont need a datatable its just one value that will be returned in the query. New to this whole data access thing. Thanks for your help.

 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;

   }

I'm curious why people are downvoting this question. – Lynn Crumbling Dec 01 '11 at 19:14 — Lynn Crumbling, Dec 01 '11 at 19:14
The question is very unclear. – lesderid Dec 01 '11 at 19:25 — lesderid, Dec 01 '11 at 19:25

score 4 · Answer 1 · answered Dec 01 '11 at 18:43

4

You can use SqlCommand instead of 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;

answered Dec 01 '11 at 18:43

Xint0

5,221
2
27
29

Maybe its just an example code, but your parameter "guid" is not an int. I might suspect that its not a string either. If it is really a guid, pass it as such - or rename it. – Black Light Dec 07 '11 at 13:35
...and, you might like to use "Parameters.AddWithValue(...)", which will add a parameter with the type determined from whatever the value is (in your case, a String). – Black Light Dec 07 '11 at 15:10

score 3 · Answer 2 · answered Dec 01 '11 at 18:40

Use SqlCommand and ExecuteScalar instead of filling a 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();
}

score 2 · Answer 3 · answered Dec 01 '11 at 18:39

   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]); 

   }

Please note, I only modified your above code to return an int, instead of a datatable. A cleaner and more lightweight way to refactor this would be to switch over to using the SqlCommand.ExecuteScalar() function, which is specifically designed to only return one value, not an entire DataTable. — Lynn Crumbling, Dec 01 '11 at 18:47

score 1 · Answer 4 · answered Dec 01 '11 at 18:39

1

instead of:

 return dt;

use this:

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

The declaration also needs to be changed to:

 public int GetMemberID(string guid)

answered Dec 01 '11 at 18:39

Decker97

1,643
10
11

Return an int instead of a datatable

4 Answers4