Parameter Add Causing Timeout in DBCommand

Question

I have a Parameter add function like bellow:

public static void AddParameter(DbCommand comm, string ParamName, Object objValue, DbType Paramtype,int paramSize)
{
      //comm.Parameters.Clear();
      DbParameter param = comm.CreateParameter();
      param.ParameterName = ParamName;
      param.DbType = Paramtype;
      param.Size = paramSize;
      param.Direction = ParameterDirection.Input;
      param.Value = objValue;
      comm.Parameters.Add(param);
}

Now when do the following:

conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM tableName WHERE MemberID=@MemberID";
AddParameter(cmd, "@MemberID", "00000970-b1fd-49ad-89fc-1de066c0076c", DbType.String, 50);
cmd.ExecuteReader();

I get Timeout exception. But if I run the same command Like

SELECT * FROM tableName WHERE MemberID='00000970-b1fd-49ad-89fc-1de066c0076c'

I get my desired output... Can anybody help me find out where is my problem? conn here is a SqlConnection. Thanks.

What is the type of `MemberID` in the database? *exactly*? `[n][var]char(max|size)` (which specifically), or `uniqueidentifier` ? — Marc Gravell, Jul 29 '13 at 11:54
Are you sure that the type of `MemberID` is VarChar? It seems a Guid to me.. try with : `AddParameter(cmd, "@MemberID", "00000970-b1fd-49ad-89fc-1de066c0076c", DbType.Guid, 0);` — nemesv, Jul 29 '13 at 11:55
Re your "But if I run the same command" - is that "via the same .NET code above" ? or is that "in SQL Server Management Studio" (or whatever other tool)? — Marc Gravell, Jul 29 '13 at 12:00
@ Marc Gravell same C# code .... Sorry to all. I made an edit. DbType.String is what I actually used. — Mehbube Arman, Jul 29 '13 at 12:02
@MehbubeArman hunch: can you try that with `DbType.AnsiString` instead? — Marc Gravell, Jul 29 '13 at 12:04
@MarcGravell Thanks.. that really solved my problem... Does String has any problem with varchar? — Mehbube Arman, Jul 29 '13 at 12:07
@MehbubeArman explained more in an answer; it isn't that "String has any problem with varchar"... more that "String isn't varchar; it is nvarchar" — Marc Gravell, Jul 29 '13 at 12:12

score 1 · Accepted Answer · answered Jul 29 '13 at 12:12

You state that the column is varchar(50), but you are using DbType.String; however, DbType.String is nvarchar(...) - i.e. unicode. Now, sometimes the server will do the conversion the "right way around" (where "right" here means: the one that doesn't kill performance), but sometimes it will make the alternative choice (for example, it might decide that it can't use the index and do a table scan, or even worse - it might decide to convert every row in turn for the comparison). The best thing to do, then, is to specify the correct data-type in the first place: by using DbType.AnsiString. There's nothing "wrong" with either DbType.String or DbType.AnsiString - so long as they are correctly being used to represent nvarchar and varchar respectively.

Parameter Add Causing Timeout in DBCommand

1 Answers1