Wiki

##DbHug ##Execute commands

You can execute SQL commands easely by DbHug. Just pass command text or DbCommand into proper method.
Few simple examples:

#!csharp

dbHug.ExecuteNonQuery("INSERT INTO Phones (Number, ContactId) VALUES ('+1 111 1111 111', 2)");

with DbParameters

#!csharp
var numberParam = new SqlParameter("Number","+1 111 1111 111");
var idParam = new SqlParameter("Id", 1);
dbHug.ExecuteNonQuery("INSERT INTO Phones (Number, ContactId) VALUES (@Number, @Id)", numberParam, idParam);

or just command

#!csharp
var sqlConnection = new SqlConnection(...);
var sqlCommand = new SqlCommand("INSERT INTO Phones (Number, ContactId) VALUES (@Number, @Id)");
sqlCommand.Connection = sqlConnection;
sqlCommand.Parameters.Add(numberParam );
sqlCommand.Parameters.Add(idParam );
dbHug.ExecuteNonQuery(sqlCommand);

###Templates Also we can use templates. For more details see this

#!csharp
dbHug.ExecuteScalar("EXEC RemovePerson {Id}, {Permanently};", 
                  new { Id=55, Permanently=true }};

We can execute procedures with output parameters.

#!csharp

dbHug.ExecuteScalar("EXEC AddPerson {FirstName}, {LastName}, {Date}, @Id OUTPUT;", 
                  new { FirstName = "John", LastName = "Smith", Date = new DateTime(1910, 1, 1) }, idParam);

###Many execution You are able to execute some command in 'many' manner. It means all your commands will be split and then executed by parts. 50 commands at once.

#!csharp

var phoneIds = new[] { new { Id = 1 }, new { Id = 2 } }
dbHug.ExecuteNonQueries("DELETE FROM PHONES WHERE Id={Id}", phoneIds));

#!csharp

var phones = new[] { new { ContactId= 1, Number="+1500500500" }, new { ContactId= 2, Number="+1501501501" } }
var phoneIds = dbHug.ExecuteNonQueries("INSERT INTO PHONES (ContactId, Number) Values({ContactId}, {Number}) SELECT @@IDENTITY;", phones));

###Parameters in template It is possible to pass DbParameter explicitly in template.

#!csharp
var IdParam = new SqlParameter
{
  Direction = System.Data.ParameterDirection.Output,
  Value = 0
};
dbHug.ExecuteNonQuery("EXEC AddPerson {FirstName}, {LastName}, {Date}, {Id} OUTPUT",
new
{
  FirstName = "John",
  LastName = "Smith",
  Date = new DateTime(1910, 1, 1),
  Id = IdParam
});

Same for many:

#!csharp
var IdParam1 = new SqlParameter
{
  Direction = ParameterDirection.Output,
  Value = 0
};
var IdParam2 = new SqlParameter
{
  Direction = ParameterDirection.Output,
  Value = 0
};
dbHug.ExecuteQueries("EXEC AddPerson {FirstName}, {LastName}, {Date}, {Id} OUTPUT",
new[]{
  new
  {
    FirstName = "John",
    LastName = "Smith",
    Date = new DateTime(1910, 1, 1),
    Id = IdParam1
  },
  new
  {
    FirstName = "Jane",
    LastName = "Smith",
    Date = new DateTime(1910, 1, 1),
    Id = IdParam2
  }
});

###Stored Procedures DbHug it self has no built-in mechanism to pass into methods CommandType (CommandType.Text, CommandType.StoredProcedure, etc) of executing DbCommand. All commands type is CommandType.Text, so to execute procedure you just need to add EXEC word (in case of T-SQL) in the command string like shown before ("EXEC AddPerson ...").
If you really want to do this you can pass DbCommand explicitly into DbHug's methods.
On other hand you can use PrepareCommand / ComposeCommand functionality. For more detals see this.

###Transactions ExecuteNonQueries() and ExecuteNonScalars() are executing within transactions.
Also you can setup transactions to commands. see commands

Also we can use TransactionScope. For details see this.

#!csharp
using (var transScope = new TransactionScope())
{
  dbHug.ExecuteScalar("EXEC RemovePerson {Id}, {Permanently};", new { Id=55, Permanently=true }};
  dbHug.ExecuteScalar("EXEC RemovePerson {Id}, {Permanently};", new { Id=56, Permanently=true }};
  transScope.Complete();
}

* > Aware that asynchronous operations may not work! See this.

####SUID .... Commands