Save result of ExecuteCommand in a string

Question

I want to execute a SQL command from c#

I have:

ccDc = getDataContext();
int MD5 = ccDc.ExecuteCommand("SELECT HASHBYTES('MD5', ChunkData) FROM dbo.x where id={0}", Id);

When I run this

SELECT HASHBYTES('MD5', ChunkData) FROM dbo.x where Id = '40'

I can see the string of md5 but in c# it just returns an integer. But I need to save the result in a string.

How can I do it?

you seem to be assigning the returned value to an integer... — user1666620, Oct 09 '15 at 13:01
Wouldn't [`ExecuteQuery`](https://msdn.microsoft.com/en-us/library/bb534292.aspx) be a more appropriate choice? — Paul Roub, Oct 09 '15 at 13:05
@user1666620 Appropriate, since that's exactly what `ExecuteCommand()` returns. — Paul Roub, Oct 09 '15 at 13:06
[`ExecuteCommand()`](https://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.executecommand(v=vs.110).aspx) returns the number of rows modified by the command executed, not a result of the select statement. You probably want `ExecuteQuery()`, something like -- `string md5 = ccDc.ExecuteQuery("SELECT HASHBYTES('MD5', ChunkData) FROM dbo.x where id={0}").FirstOrDefault();` — GarethD, Oct 09 '15 at 13:06
@PaulRoub you are right but I don't know what should I write for the first parameter of exectequery. I would appreciate if you can help me — laila, Oct 09 '15 at 13:15
@GarethD the only problem with this is that with this gives me the error: Unable to cast object of type 'System.Byte[]' to type 'System.String'. — laila, Oct 09 '15 at 14:46

user1666620 · Answer 1 · 2015-10-09T13:16:01.973

The below assumes that getDataContext() is what creates the SQL connection.

ccDc = getDataContext();

using (SqlCommand command = new SqlCommand(
    string.Format("SELECT HASHBYTES('MD5', ChunkData) FROM dbo.x where id={0}", Id),
    ccDc))
{
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                Console.WriteLine(reader.GetValue(i)); // or do whatever you want with the results...
            }
        }
    }
}

You should parameterize your query to prevent SQL injection, but there are plenty of tutorials and questions on that.

score -1 · Answer 2 · answered Oct 09 '15 at 13:12

-1

SqlCommand command = new SqlCommand(string.Format("SELECT HASHBYTES('MD5', ChunkData) FROM dbo.x where id={0}", Id), connection);
string md5 = (string)command.ExecuteScalar();

answered Oct 09 '15 at 13:12

Hemario

140
9

Save result of ExecuteCommand in a string

2 Answers2