7

This is code cut right from the Dapper examples:

var p = new DynamicParameters();
p.Add("@a", 11);
p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue); 
cnn.Execute("spMagicProc", p, commandType: commandType.StoredProcedure);
int b = p.Get("@b");
int c = p.Get("@c");

Anyone: In the above code from the example provided, I get an error, "can't resolve .Execute"--referring to cnn.Execute. I look at the connection object and there is no method for Execute. Dapper obviously works well, so what am I doing wrong?

J. Steen
  • 15,470
  • 15
  • 56
  • 63
Kevin Earley
  • 165
  • 2
  • 8
  • `cnn` is a `SqlConnection`; you need to have a reference to the SqlConnection assembly and the Dapper.net assembly. What is the surrounding code in your example? – Metro Smurf Sep 21 '12 at 13:59
  • Thanks for the quick response Metro. Here is the code surrounding. I have references to dapper as well as SqlClient. The only compile error I get is for ".Execute". (see next post for code) – Kevin Earley Sep 21 '12 at 14:27
  • using (var connection = new SqlConnection(connectionString)){ try{ connection.Open(); //from dapper example var p = new DynamicParameters(); p.Add("@a", 11); p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output); p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue); connection.Execute("MyDamnStoredProc", p, commandType: commandType.StoredProcedure); int b = p.Get("@b"); int c = p.Get("@c"); – Kevin Earley Sep 21 '12 at 14:31
  • Can you post the entire exception? I see 2 things that could be potential issues: 1. commandType.StoredProcedure should be CommandType.StoredProcedure (notice capitalization); 2. Does the stored proc exist in your database? – Metro Smurf Sep 21 '12 at 14:42
  • I changed capitolization. Also, that is the stored proc name. The code and proc is not meant to be production quality--just an fyi for anyone with concerns. I just want to see Dapper work. I'm not sure how to show all the code without breaking it up into multiple posts, so here goes. – Kevin Earley Sep 21 '12 at 15:01
  • using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using SqlMapper; – Kevin Earley Sep 21 '12 at 15:01
  • public static class Program {public static string GetOpenConnection(){ ConnectionStringSettingsCollection settings = ConfigurationManager.ConnectionStrings; Console.WriteLine(settings.Count); if (settings != null){foreach (ConnectionStringSettings cs in settings){ Console.WriteLine(cs.Name); Console.WriteLine(cs.ProviderName); Console.WriteLine(cs.GetType()); Console.WriteLine(cs.ConnectionString); Console.WriteLine(""); return cs.ConnectionString; }} Console.WriteLine("Not good. There was no connection string in the config file."); return null;} – Kevin Earley Sep 21 '12 at 15:06
  • public static void Main(){ string connectionString = GetOpenConnection(); //gets a valid connection string using (var connection = new SqlConnection(connectionString)){ try{//from dapper example var p = new DynamicParameters(); p.Add("@a", 11); p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output); p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue); connection.Open(); connection.Execute("MyDamnStoredProc", p, CommandType.StoredProcedure); int b = p.Get("@b"); int c = p.Get("@c");} catch (Exception ex){Console.WriteLine(ex.Message);}}}} – Kevin Earley Sep 21 '12 at 15:07
  • Sorry for the crap formattinig, but I can only post 500+ characters at a time. – Kevin Earley Sep 21 '12 at 15:08
  • This is the entire compile exception: Error 2 'System.Data.SqlClient.SqlConnection' does not contain a definition for 'Execute' and no extension method 'Execute' accepting a first argument of type 'System.Data.SqlClient.SqlConnection' could be found (are you missing a using directive or an assembly reference?) – Kevin Earley Sep 21 '12 at 15:11
  • Are you able to edit your question with all of this? That's quite a bit to decipher... – Metro Smurf Sep 21 '12 at 15:51

2 Answers2

9

I believe this should get you fixed up:

using( var connection = new SqlConnection( connectionString ) )
{
    try
    {
        var p = new DynamicParameters();
        p.Add( "a", 11 );
        p.Add( "b", dbType: DbType.Int32, direction: ParameterDirection.Output );
        p.Add( "c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue );
        connection.Open();
        connection.Execute( "MyDamnStoredProc", p, commandType: CommandType.StoredProcedure );
        int b = p.Get<int>( "b" );
        int c = p.Get<int>( "c" );
    }
    catch( Exception ex )
    {
        Console.WriteLine( ex.Message );
    }
}

Notes:

  1. the params don't need the @ symbol; dapper will handle that for you.
  2. be sure to use named parameters; see the updated connection.Execute method that specifies the commandType: parameter. You do this so the optional parameters can be omitted from the method call.
Metro Smurf
  • 37,266
  • 20
  • 108
  • 140
  • Metro, thank you for your help. I am still getting a compile error, Error 1 'System.Data.SqlClient.SqlConnection' does not contain a definition for 'Execute' and no extension method 'Execute' accepting a first argument of type 'System.Data.SqlClient.SqlConnection' could be found (are you missing a using directive or an assembly reference?). Does this compile for you? if so, I must be missing a reference or something. I'm using VS2010 Pro/sp1 and .NET 4.0 w/all service packs. – Kevin Earley Sep 21 '12 at 17:12
  • I'm a bit perplexed with this one now. Everything is pointing to Dapper.net not being properly referenced. – Metro Smurf Sep 21 '12 at 18:49
  • Thanks Metro. That is at least a great place to start. I'll post back what I find. Thanks again for your help. -Kevin – Kevin Earley Sep 21 '12 at 20:29
  • Metro, the things you recommended helped, and helped me to realize that a block of important code was missing from dapper (bad copy/paste on my part). I am able to execute a stored procedure with dynamic parameters, but I don't know how to retrieve the data selected by the procedure. – Kevin Earley Sep 24 '12 at 20:28
  • 1
    Kevin, take a look at this question: [Is there a way to call a stored procedure with dapper dot net?](http://stackoverflow.com/q/5962117/9664). If that doesn't help, I would recommend starting a new question on how to retrieve the results of a Sproc using dapper since this question has been answered, re: the error has been resolved. – Metro Smurf Sep 25 '12 at 00:22
7

"can't resolve .Execute"

That would be cause your extension method is missing, did you using Dapper; at the top of your file.

See also: http://msdn.microsoft.com/en-us/library/bb308966.aspx#csharp3.0overview_topic3

Sam Saffron
  • 128,308
  • 78
  • 326
  • 506