Conversion failed when converting from a character string to uniqueidentifier when inserting value to sql

Question

I have simple SQL table called test which has two column.first column is an TINYINT and second one is a type of UNIQUEIDENTIFIER.

I have created simple method to insert values into "test" table using for loop and its working fine without any errors.But once i try to create string to uniqueidentifier conversion error it will roll back the transaction and delete all previous inserted values in same transaction.

This is the place where conversion happen

strCommand += "INSERT INTO Test(Test, Test2) VALUES(" + i.ToString() + ", '" + (i == 251 ? Guid.NewGuid().ToString().Remove(12, 1) : Guid.NewGuid().ToString()) + "'); ";

Here is the my complete code

private static string TryThisPlease()
    {
        SqlConnection connection = null;
        SqlCommand command = null;
        SqlTransaction transaction = null;



        string strRet = "OK";



        try
        {
            connection = new SqlConnection(connectionString);
            connection.Open();



            //starting transaction mode
            transaction = connection.BeginTransaction(IsolationLevel.Snapshot);



            command = new SqlCommand("Test", connection);
            command.CommandType = CommandType.Text;
            command.Transaction = transaction;



            //for (int i = 255; i < 257; i++)
            for (int i = 250; i < 255; i++)
            {
                string[] strData = new string[] { "", "3" };



                string strCommand = "";



                //strCommand += "INSERT INTO Test(Test, Test2) VALUES(" + i.ToString() + ", '" + Guid.NewGuid().ToString() + "'); ";
                strCommand += "INSERT INTO Test(Test, Test2) VALUES(" + i.ToString() + ", '" + (i == 251 ? Guid.NewGuid().ToString().Remove(12, 1) : Guid.NewGuid().ToString()) + "'); ";



                command.CommandText = strCommand;



                if (command.Connection.State != ConnectionState.Open)
                    command.Connection.Open();



                try
                {
                    command.ExecuteNonQuery();
                }
                catch (Exception EX)
                {
                    strRet = "FAIL";



                    try
                    {



                    }
                    catch (Exception)
                    {
                        strRet = "FAIL";
                    }
                }
            }



            transaction.Commit();
        }
        catch (Exception EX)
        {
            transaction.Rollback();



            strRet = "FAIL";
        }
        finally
        {
            connection.Close();
        }



        return strRet;
    }

Uncommenting the two lines commented and commenting out lines below,another error with same severity happens. Transactions are not rolled back in this scenario

Is there any way to prevent the transaction being rollback or did i miss something in my code ?

Answer 1

if you want previous inserts to be successful, what you have to do is, create and commit the transaction inside the foreach loop, so that each row is considered separate transaction.

using(SqlConnection connection = new SqlConnection(connectionString)) {

    connection.Open();

    for (int i = 250; i < 255; i++) {

        using(SqlCommand command = new SqlCommand("", connection, trans)) {
            command.CommandType = System.Data.CommandType.Text;

            using(SqlTransaction trans = connection.BeginTransaction()) {
                try {

                    strCommand = "INSERT INTO Test(Test, Test2) VALUES(" + i.ToString() + ", '" + (i == 251 ? Guid.NewGuid().ToString().Remove(12, 1) : Guid.NewGuid().ToString()) + "'); ";

                    command.CommandText = strCommand;

                    command.ExecuteNonQuery();
                    trans.Commit();
                }
                catch(Exception e) {
                    //Handle Error
                    trans.Rollback();
                }
            }

        }

    }
}

But, your command is prone for sql injection attacks. I would suggest you to parametrize the query as given below:

    SqlCommand cmd = new SqlCommand(
        "INSERT INTO Test(Test, Test2) VALUES(@id1,@id2)", conn);

cmd.Parameters.Add( new SqlParameter(@id1, SqlDbType.Int)).Value = i;
cmd.Parameters.Add( new SqlParameter(@id2, SqlDbType.Guid)).Value = (i == 251 ? Guid.NewGuid().ToString().Remove(12, 1) : Guid.NewGuid().ToString());

UPDATE If you want to still go with batch transaction, you can consider savepoint for the transaction. Instead of rolling back the whole transaction, you can rollback till the savepoint.Read more on Savepoint

                        command.CommandText = strCommand;
                        trans.Save($"save{i}");
                        command.ExecuteNonQuery();
                        trans.Commit();
                    }
                    catch(Exception e) {
                        //Handle Error
                        trans.Rollback($"save{i}");
                        trans.Commit();
                    }

Answer 2

The problem lies in this statement Guid.NewGuid().ToString().Remove(12, 1). The result of this statement will remove the 12th character from your generated GUID which is not a valid GUID and hence the database insertion fails.

Guid Format:
"00000000-0000-0000-0000-000000000000" 
             ^ 12th index character which will get removed from the Guid.

When the condition i==251 becomes true this code Guid.NewGuid().ToString().Remove(12, 1) will get executed and it will generate the error. You need to update this to produce GUID in correct format inorder to solve your issue.