Is it possible for me to create a snippet and have it analyze a current class, get the properties of said class, and then create a sql function that writes out line by line each property in a command parameter.
What I am looking for is doing something like this:
public static int Add(MyObject Message) {
MySqlConnection connection = new MySqlConnection(MySqlConnection);
MySqlCommand command = new MySqlCommand("Add_Message", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@IMFromUserID", Message.IMFromUserID);
command.Parameters.AddWithValue("@IMToUserID", Message.IMToUserID);
command.Parameters.AddWithValue("@IMMessage", Message.IMMessage);
command.Parameters.AddWithValue("@IMTimestamp", Message.IMTimestamp);
connection.Open();
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read()) {
Message.IMID = (int)reader["IMID"];
}
command.Dispose();
connection.Close();
connection.Dispose();
return Message.IMID;
}
Basically I want the snippet to populate the entire Add function and fill in the @PropertyName
and the Message.PropertyName
in the command.Parameters.AddWithValue
I don't think code snippets are powerful enough. Maybe the ReSharper's code templates are powerful enough but I don't think so, too. You could look into using T4 templates if you really need or want code generation.
Personally I would suggest to avoid compiletime code generation altogether. You could use reflection - easy but slow - or runtime code generation - complex but fast. If performance is not a primary concern I suggest to use reflection.
public static Int32 Add<TMessage>(TMessage message)
where TMessage: IMessageWithIMID
{
using (var connection = new MySqlConnection(connectionString))
using (var command = new MySqlCommand("Add_Message", connection))
{
command.CommandType = CommandType.StoredProcedure;
// We look only at public instance properties but you can easily
// change this and even use a custom attribute to control which
// properties to include.
var properties = typeof(TObject).GetProperties(BindingFlags.Public |
BindingFlags.Instance);
foreach (var property in properties)
{
var parameterName = "@" + property.Name;
var value = property.GetValue(message, null);
command.Parameters.AddWithValue(parameterName, value);
}
connection.Open();
message.IMID = (Int32)command.ExecuteScalar();
return message.IMID;
}
}
Note that you have to introduce and implement the interface IMessageWithIMID
in order to access the property IMID
.
internal interface IMessageWithIMID
{
Int32 IMID { get; set; }
}
Note that you also don't need a data read - you can just use ExecuteScalar()
. This turns
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
message.IMID = (Int32)reader["IMID"];
}
}
into
message.IMID = (Int32)command.ExecuteScalar();
and you are done.