Transaction SQL

Add a try/catch block, if the transaction succeeds it will commit the changes, if the transaction fails the transaction is rolled back:

 BEGIN TRANSACTION [Tran1]  
  BEGIN TRY  
    INSERT INTO [Test].[dbo].[T1] ([Title], [AVG])  
    VALUES ('Tidd130', 130), ('Tidd230', 230)  
    UPDATE [Test].[dbo].[T1]  
    SET [Title] = N'az2' ,[AVG] = 1  
    WHERE [dbo].[T1].[Title] = N'az'  
    COMMIT TRANSACTION [Tran1]  
  END TRY  
  BEGIN CATCH  
    ROLLBACK TRANSACTION [Tran1]  
    SELECT @ErrorMessage = 'Lỗi: ' + ERROR_MESSAGE()
    RAISERROR(@ErrorMessage, 16, 1)
  END CATCH   

Read More »

Move connection string into code

Firstly I had to create myself a new class in my DB ORM assembly Then I had to add a reference to the System.Data.Entity assembly. Then added this static method to my static class (including the namespaces required)

 using System.Data.SqlClient;  
 using System.Data.EntityClient;  
 namespace MY_DB_ORM  
 {  
   static class MY_DB  
   {  
     public static string ConnectionString()  
     {  
       SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();  
       // Set the properties for the data source.  
       sqlBuilder.DataSource = "MY_SERVER_IP,1433";  
       sqlBuilder.InitialCatalog = "MY_DB_NAME";  
       sqlBuilder.UserID = "MY_USERID";  
       sqlBuilder.Password = "MY_PASSWORD";  
       sqlBuilder.IntegratedSecurity = false;  
       // Build the SqlConnection connection string.  
       string providerString = sqlBuilder.ToString();  
       // Initialize the EntityConnectionStringBuilder.  
       EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();  
       //Set the provider name.  
       entityBuilder.Provider = "System.Data.SqlClient";  
       // Set the provider-specific connection string.  
       entityBuilder.ProviderConnectionString = providerString;  
       // Set the Metadata location.  
       entityBuilder.Metadata = @"res://*/MY_CONTEXT_CLASS_NAME.csdl|  
                         res://*/MY_CONTEXT_CLASS_NAME.ssdl|  
                         res://*/MY_CONTEXT_CLASS_NAME.msl";  
       return entityBuilder.ToString();  
     }  
   }  
 }  

Then in my context class I could alter the constructor to use this method..

 public partial class MY_CONTEXT_CLASS_NAME : DbContext  
   {  
     public MY_CONTEXT_CLASS_NAME_CONSTRUCTOR()  
       : base(MY_DB.ConnectionString())  
     {  
     }  

Now I am able to remove the connection string to my model from the app.config file where it was held in clear text for all to see! https://community.spiceworks.com/topic/815431-ef-can-t-get-it-to-work

Read More »

The difference between break and yield break

Using yield break as opposed to break might not be as obvious as one may think. There are lot of bad examples on the Internet where the usage of the two is interchangeable and doesn't really demonstrate the difference. The confusing part is that both of the keywords (or key phrases) make sense only within loops (foreach, while...) So when to choose one over the other? It's important to realize that once you use the yield keyword in a method you effectively turn the method into an iterator. The only purpose of the such method is then to iterate over a finite or infinite collection and yield (output) its elements. Once the purpose is fulfilled, there's no reason to continue method's execution. Sometimes, it happens naturally with the last closing bracket of the method }. But sometimes, you want to end the method prematurely. In a normal (non-iterating) method you would use the return keyword. But you can't use return in an iterator, you have to use yield break. In other words, yield break for an iterator is the same as return for a standard method. Whereas, the break statement just terminates the closest loop. Let's see some examples: ///

/// Yields numbers from 0 to 9 ///

/// {0,1,2,3,4,5,6,7,8,9} public static IEnumerable YieldBreak() { for (int i = 0; ; i++) { if (i < 10) { // Yields a number yield return i; } else { // Indicates that the iteration has ended, everything // from this line on will be ignored yield break; } } yield return 10; // This will never get executed } ///

/// Yields numbers from 0 to 10 ///

/// {0,1,2,3,4,5,6,7,8,9,10} public static IEnumerable Break() { for (int i = 0; ; i++) { if (i < 10) { // Yields a number yield return i; } else { // Terminates just the loop break; } } // Execution continues yield return 10; } Source: https://riptutorial.com/csharp/example/29811/the-difference-between-break-and-yield-break

Read More »