devonuto devonuto - 25 days ago 6
C# Question

Unable to get data to commit to SQL Server

I've been scouring the net for an answer, but haven't found anything yet.

I have a small console app, which I'm trying to insert some data into a SQL Server database (

.mdf
database file), everything runs without error, but when I open the DB after the fact in Server Explorer, the data is not there.

This is the code:

using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["FrameBudgetDB"].ToString()))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

conn.Open();

cmd.CommandText = string.Format("SELECT TOP 1 category_id FROM businesses WHERE '{0}' LIKE CONCAT('%',description,'%')", transDescription.Replace("'", "''"));

SqlDataReader reader = cmd.ExecuteReader();

try
{
if (reader.HasRows)
{
while (reader.Read())
{
categoryId = (int)reader[0];
}
}
else
{
categoryId = 44; // Unknown
}
}
finally
{
reader.Close();
}

// Get Transaction Type
int transTypeId = 0;
cmd.CommandText = string.Format("SELECT trans_type_id FROM transaction_types WHERE description = '{0}'", transType);

reader = cmd.ExecuteReader();

try
{
if (reader.HasRows)
{
while (reader.Read())
{
transTypeId = (int)reader[0];
}
}
}
finally
{
reader.Close();
}

SqlTransaction trans = conn.BeginTransaction("InsertTransactiolns");

try
{
cmd.Transaction = trans;
cmd.CommandText = string.Format(
"BEGIN " +
"IF NOT EXISTS(SELECT * FROM transactions " +
"WHERE transaction_date = '{0}' " +
"AND description = '{1}' " +
"AND trans_type_id = {2} " +
"AND amount = {3} " +
"AND(category_id = {5} OR previous_category_id = {5} )) " +
"BEGIN " +
"INSERT INTO transactions(transaction_date, description, trans_type_id, import_date, category_id, amount) " +
"VALUES('{0}', '{1}', {2}, '{4}', {5}, {3}) " +
"END " +
"END", transDate, transDescription.Replace("'", "''"), transTypeId, amount, DateTime.Now, categoryId);

rowsInserted = rowsInserted + cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);

try
{
trans.Rollback();
}
catch (Exception e2)
{
Console.WriteLine(e2.Message);
}
}
}
}


The db connection string is:

<connectionStrings>
<add name="FrameBudgetDB"
connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|FrameBudget.mdf;Integrated Security=True;Connect Timeout=30"
providerName="System.Data.SqlClient" />
</connectionStrings>


and the data directory is:

AppDomain.CurrentDomain.SetData("DataDirectory", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\FrameBudget\"));

Answer

Really it is not clear why you have both a TransactionScope instance and a call to BeginTransaction. I would use only one of them. However this line

using (TransactionScope scope = new TransactionScope())
{
   ...

needs to be completed in case of success with

   scope.Complete();
}

Without the call to Complete, exiting the using block implies a rollback.
I would remove the block with the call to

 SqlTransaction trans = conn.BeginTransaction("InsertTransactiolns");

and the relative Rollback or Commit calls leaving only the TransactionScope instance (a lot simpler to handle).

As a side note. Your code is vulnerable to Sql Injection. That string.Format is a form of string concatenation and we all know the nasty things that can happen with a string concatenation approach to build sql queries