SQL SELECT With Stored Procedure and Parameters?

问题:

I've been writing a lot of web services with SQL inserts based on a stored procedure, and I haven't really worked with any SELECTS.

The one SELECT I have in mind is very simple.

SELECT COUNT(AD_SID) As ReturnCount FROM AD_Authorization
WHERE AD_SID = @userSID

However, I can't figure out based on my current INSERT code how to make that into a SELECT and return the value of ReturnCount... Can you help? Here is my INSERT code:

string ConnString = "Data Source=Removed";
string SqlString = "spInsertProgress";

using (OleDbConnection conn = new OleDbConnection(ConnString))
{
   using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
   {
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.AddWithValue("attachment_guid", smGuid.ToString());
      cmd.Parameters.AddWithValue("attachment_percentcomplete", fileProgress);
      conn.Open();
      cmd.ExecuteNonQuery();
      conn.Close();
   }
}

回答1:

Here is where you are going wrong:

cmd.ExecuteNonQuery();

You are executing a query.

You need to ExecuteReader or ExecuteScalar instead. ExecuteReader is used for a result set (several rows/columns), ExecuteScalar when the query returns a single result (it returns object, so the result needs to be cast to the correct type).

var result = (int)cmd.ExecuteScalar();

The results variable will now hold a OledbDataReader or a value with the results of the SELECT. You can iterate over the results (for a reader), or the scalar value (for a scalar).

回答2:

Since you are only after a single value, you can use cmd.ExecuteScalar();

A complete example is as follows:

    string ConnString = "Data Source=Removed"; 
    string userSid = "SomeSid";
    string SqlString = "SELECT COUNT(AD_SID) As ReturnCount FROM AD_Authorization WHERE AD_SID = @userSID;"; 
    int returnCount = 0;
    using (OleDbConnection conn = new OleDbConnection(ConnString)) 
    { 
        using (OleDbCommand cmd = new OleDbCommand(SqlString, conn)) 
        { 
            cmd.CommandType = CommandType.Text; 
            cmd.Parameters.AddWithValue("@userSID", userSid); 
            conn.Open(); 
            returnCount = Convert.ToInt32(cmd.ExecuteScalar()); 
        } 
    } 

If you wanted to return MULTIPLE rows, you can use the ExecuteReader() method. This returns an IDataReader via which you can enumerate the result set row by row.

回答3:

You need to use ExecuteScalar instead of ExecuteNonQuery:

String query = "SELECT COUNT(AD_SID) As ReturnCount FROM AD_Authorization WHERE AD_SID = @userSID ";
using (OleDbConnection conn = new OleDbConnection(ConnString)) {
    using (OleDbCommand cmd = new OleDbCommand(query, conn))
    {
        cmd.Parameters.AddWithValue("userSID", userSID.ToString());
        conn.Open();
        int returnCount = (Int32) cmd.ExecuteScalar();
        conn.Close();
    }
}

回答4:

cmd.executescalar will return a single value, such as your count.

You would use cmd.executereader when you are returning a list of records