CODEWITHSUNDEEP
dapper
Greg Schalk
Greg Schalk

Reputation: 73

Dapper return specfic field from query

I am working on connecting to SQL database and setting variables in my C# application to match returned values from a Dapper Query.

I am able to return the correct row information (I used a datagridview to show that i get the correct row, and when i debug i see the right data) but how do I set a program variable to just one of the columns? here is some code showing my process

Connect & run stored procedure:

            using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MfgDataCollector"].ToString()))
        {
            DynamicParameters param = new DynamicParameters();
            param.Add("@UserID", txt_userid.Text.Trim());

            List<User> userinfo = conn.Query<User>("GetUserInfo", param, commandType: CommandType.StoredProcedure).ToList<User>();

            Variables.UserID = txt_userid.Text.Trim();

            datagridview1.DataSource = userinfo; //this displays the right information, however I want to store the information as a public variable(like above)
            Variables.Userfull = userinfo; //when debugging this shows I have the right information but its all columns of user

User Class:

class User
{
    public string UserID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int AccessLevel { get; set; }
    public int FirstUse { get; set; }
    public string Password { get; set; }
    public int LoginCounter { get; set; }
    public string LastLogin { get; set; }
    public string FirstLogin { get; set; }
    public string UserFullName { get; set; }
}

EDIT

per answer, I have tried to switch the command to executescalar with this code:

var userinfo = conn.ExecuteScalar<User>("GetUserInfo", param, commandType: CommandType.StoredProcedure);

This allows me to set

Variables.UserID = userinfo.UserID;

without red-squiggle lines however during run-time i get an error on the execute scalar of "System.InvalidCastException: 'Invalid cast from 'System.String' to 'Name_Space.User'.'

I have checked the User Class to ensure the data type matches with the Database and i see no problems there, I'm not sure what I'm doing wrong?

Upvotes: 1

Views: 2689

Answers (1)

Amit Joshi
Amit Joshi

Reputation: 16408

If you want value from only one column of only one row, use ExecuteScalar which is similar to ADO.NET ExecuteScalar. It will return object with which you have to deal further.

string sql = "SELECT COL1 FROM Table1 WHERE ID = 1";
//OR
//string sql = "SELECT TOP 1 COL1 FROM Table1";
//OR similar
object colValue = conn.ExecuteScalar(sql, ....);

If matching record not found, return value will be null.

Check Dapper documentation for other generic variants of method:

public static T ExecuteScalar<T>(this IDbConnection cnn, CommandDefinition command);

If you want single column from multiple rows, the way you are doing this now is correct. Just modify your SQL query/Stored Procedure to return the same. Dapper will only map returned column. All other properties in your User will remain unassigned.

If you simply want to assign values to Variables.UserID instead of binding those using DataSource, then just do that like:

Variables.UserID = userinfo.UserID;
Variables.Userfull = userinfo.UserFullName;
//and so on....

So, complete code will be something like below:

using(SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MfgDataCollector"].ToString()))
{
    DynamicParameters param = new DynamicParameters();
    param.Add("@UserID", txt_userid.Text.Trim());

    User userinfo = conn.Query<User>("GetUserInfo", param, commandType: CommandType.StoredProcedure).ToList<User>().First();

    Variables.UserID = userinfo.UserID;
    Variables.Userfull = userinfo.UserFullName;
}

Upvotes: 1

Related Questions