serialize SqlCommand

[job]

how is it possible to serialize/de-serialize a SqlCommand?

 

[Dave Sexton]

Hi,

It's not possible to serialize an SqlCommand. However, you can serialize an instance of a custom class instead that contains all of
the relevant information for recreating the SqlCommand.

Here's an example that partially serializes an SqlCommand by storing the command Text, Type and Timeout properties and recreating
the SqlCommand after deserialization:

[Serializable]
public sealed class SqlCommandMessage
{
[XmlIgnore]
public SqlCommand Command
{
get
{
if (command == null)
CreateCommand();

return command;
}
}

public string CommandText
{
get
{
return commandText;
}
set
{
commandText = value;
}
}

public CommandType CommandType
{
get
{
return commandType;
}
set
{
commandType = value;
}
}

public int CommandTimeout
{
get
{
return commandTimeout;
}
set
{
commandTimeout = value;
}
}

[NonSerialized]
private SqlCommand command;
private string commandText;
private CommandType commandType;
private int commandTimeout;

/// <summary>
/// Parameterless constructor is required for xml serialization
/// </summary>
private SqlCommandMessage()
{
}

public SqlCommandMessage(SqlCommand command)
{
if (command == null)
throw new ArgumentNullException("command");

this.command = command;

commandText = command.CommandText;
commandTimeout = command.CommandTimeout;
commandType = command.CommandType;
}

private void CreateCommand()
{
command = new SqlCommand(commandText);

command.CommandTimeout = commandTimeout;
command.CommandType = commandType;
}
}


-- console program unit test --

class Program
{
static void Main(string[] args)
{
using (SqlCommand command = new SqlCommand())
{
// initialize the command
command.CommandText = "SomeStoredProc";
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 15;

// create stream to hold the serialized SqlCommandMessage
using (MemoryStream stream = new MemoryStream())
{
// create the message
SqlCommandMessage message = new SqlCommandMessage(command);

// binary serialization
BinaryFormatter formatter = new BinaryFormatter();
formatter.Serialize(stream, message);

// xml serialization
XmlSerializer serializer = new XmlSerializer(typeof(SqlCommandMessage));
serializer.Serialize(stream, message);

stream.Position = 0;

// read binary
message = (SqlCommandMessage) formatter.Deserialize(stream);

Console.WriteLine("Deserialized binary command: {0}", message.Command != command);
Console.WriteLine("Text: " + message.Command.CommandText);
Console.WriteLine("Type: " + message.Command.CommandType);
Console.WriteLine("Timeout: " + message.Command.CommandTimeout);

Console.WriteLine();

// read xml
message = (SqlCommandMessage) serializer.Deserialize(stream);

Console.WriteLine("Deserialized xml command: {0}", message.Command != command);
Console.WriteLine("Text: " + message.Command.CommandText);
Console.WriteLine("Type: " + message.Command.CommandType);
Console.WriteLine("Timeout: " + message.Command.CommandTimeout);

Console.ReadLine();
}
}

//RegexTest();
}
}