Index Creation In SQL Azure

Doing some testing in SQL Azure, I found that table creation is a slightly different beast.  Simply using a SQL table creation script generated by the SQL Server Management Studio does not translate into the Azure environment.  I needed to make some modifications first to get the code working correctly, specifically how Indexes are created.

Below is a sample index generated by SQL Server Management Studio:

CREATE UNIQUE NONCLUSTERED INDEX [IX_TestTable] ON [dbo].[TestTable]
(
    [StringField] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

To get this index code to successfully work within Azure, I had to make the code look like the following:

CREATE UNIQUE NONCLUSTERED INDEX [IX_TestTable] ON [dbo].[TestTable]
(
    [StringField] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)

I had to remove “ON [PRIMARY” from the end of the code, as well as removing the following commands: PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, ALLOW_ROW_LOCKS  = ON, AND ALLOW_PAGE_LOCKS  = ON.  After that, the index creation code worked successfully in this new SQL environment.

Retry Logic

Sometimes an exception isn’t just an exception. For example, SQL can have connection or timeout issues and file IO can have problems competing for file access. In these situations, sometimes it makes sense to try executing the code again rather than letting it completely error out. Below is some C# sample code for executing some simple retry logic.

for (int retryAttempt = 1; retryAttempt <= MAX_RETRY_ATTEMPTS; retryAttempt++)
{
     try
     {
          // Perform code execution here
          break;
     }
     catch (Exception ex)
     {
          // Perform any logging here

          if (retryAttempt < MAX_RETRY_ATTEMPTS)
          {
               Thread.Sleep(1000); // Sleep 1 second before retrying
          }
          else
          {
               throw;
          }
     }
}

As you can see, it’s pretty simple.  Execute the actual code logic within the try block, and perform any necessary exception logging within the catch block.
Richard Franzen
Sr. Developer
ImageSource, Inc.

How to Convert Office File to PDF File Format in C#

How to Convert Office file to pdf in C#.

These codes are used for Microsoft Office products with the Save As PDF add-in installed.

Note that You will need to add a reference to Microsoft.Office.Interop.(word,excel, or powerpoint)

<Word To PDF>

public string ConvertWordToPdf(string inputFile)
{
string outputFileName = “Desired Output File Path”;
Microsoft.Office.Interop.Word.ApplicationClasswordApp =
new rosoft.Office.Interop.Word.ApplicationClass();
Microsoft.Office.Interop.Word.Document wordDoc = null;
object inputFileTemp = inputFile;

try
{
wordDoc = wordApp.Documents.Open(refinputFileTemp);
wordDoc.ExportAsFixedFormat(outputFileName, WdExportFormat.wdExportFormatPDF);
}
finally
{
if (wordDoc != null)
{
wordDoc.Close(WdSaveOptions.wdDoNotSaveChanges);
}
if (wordApp != null)
{
wordApp.Quit(WdSaveOptions.wdDoNotSaveChanges);
wordApp = null;
}
}

return outputFileName;
}

<Excel To PDF>

public static string ConvertExcelToPdf(string inputFile)
{
string outputFileName = “DesireOutput File Path”;
Microsoft.Office.Interop.Excel.Application excelApp =
new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = false;
Workbook workbook = null;
Workbooks workbooks = null;
try
{
workbooks = excelApp.Workbooks;
workbook = workbooks.Open(inputFile);
workbook.ExportAsFixedFormatXlFixedFormatType.xlTypePDF,outputFileName,
XlFixedFormatQuality.xlQualityStandard, true, true, Type.Missing,Type.Missing, false,Type.Missing);
}
finally
{
if (workbook != null)
{
workbook.Close(XlSaveAction.xlDoNotSaveChanges);
while(Marshal.FinalReleaseComObject(workbook) != 0) { };
workbook = null;
}
if (workbooks != null)
{
workbooks.Close();
while(Marshal.FinalReleaseComObject(workbooks) != 0) { };
workbooks = null;
}
if(excelApp != null)
{
excelApp.Quit();
excelApp.Application.Quit();
while(Marshal.FinalReleaseComObject(excelApp) != 0) { };
excelApp = null;
}
}

return outputFileName;
}

<PowerPoint To PDF>

public static string ConvertPowerPointToPdf(string inputFile)
{
string outputFileName = “DesireOutput File Path”;
Microsoft.Office.Interop.PowerPoint.ApplicationClass powerPointApp =
new Microsoft.Office.Interop.PowerPoint.ApplicationClass();
Presentation presentation = null;
Presentations presentations = null;
try
{
presentations = powerPointApp.Presentations;
presentation = presentations.Open(inputFile, MsoTriState.msoFalse,MsoTriState.msoFalse,
MsoTriState.msoFalse);

presentation.ExportAsFixedFormat(outputFileName, PpFixedFormatType.ppFixedFormatTypePDF,
PpFixedFormatIntent.ppFixedFormatIntentScreen, MsoTriState.msoFalse,
PpPrintHandoutOrder.ppPrintHandoutVerticalFirst,PpPrintOutputType.ppPrintOutputSlides,
MsoTriState.msoFalse,null,PpPrintRangeType.ppPrintAll, string.Empty, false, true, true, true, false,
Type.Missing);
}
finally
{
if (presentation != null)
{
presentation.Close();
Marshal.ReleaseComObject(presentation);
presentation = null;
}
if (powerPointApp != null)
{
powerPointApp.Quit();
Marshal.ReleaseComObject(powerPointApp);
powerPointApp = null;
}
}
return outputFileName;

}

Kyoungsu Do
Software Quality Engineer
ImageSource, Inc.

SQL Transactions in .NET

In my last blog post, I talked about performing SQL transactions as part of the SQL statement itself.  Now I want to talk about an alternative method for implementing SQL transactions within .NET code.  Basically, the SqlTransaction object can perform the function of grouping together the execution of multiple SQL statements within .NET code.

Below is an example of a SqlTransaction object in action:

 //Create and open a connection to the datase
 using (SqlConnection conn = new SqlConnection(connStr))
 {
      conn.Open();

      //Next, create a sql transaction using our current connection.
      using (SqlTransaction transact = conn.BeginTransaction())
      {
           try
           {
                //Execute the first sql command
                string sqlStr1 =
                    "INSERT INTO TestTable1 (Field1) values ('This is a sample value.')";
                using (SqlCommand cmd1 = new SqlCommand(sqlStr1, conn, transact))
                {
                     cmd1.ExecuteNonQuery();
                }

                //Execute the second sql command
                string sqlStr2 =
                     "INSERT INTO TestTable2 (Field1, Field2) values ('Performing a test insert', 'Into SQL')";
                using (SqlCommand cmd2 = new SqlCommand(sqlStr2, conn, transact))
                {
                     cmd2.ExecuteNonQuery();
                }

                //Execute the third sql command
                string sqlStr3 =
                     "INSERT INTO TestTable3 (Field1) values ('All of these inserts should be successful.')";
                using (SqlCommand cmd3 = new SqlCommand(sqlStr3, conn, transact))
                {
                     cmd3.ExecuteNonQuery();
                }

                //Finally, make sure to commit the transaction
                transact.Commit();
           }
           catch (Exception)
           {
                //If there is a sql exception, we need to back out the transaction
                transact.Rollback();
                throw;
           }
      }
  }

As you can see, the SqlTransaction object is created through a SqlConnection object’s BeginTransaction method.   It must be included with all of the SqlCommand objects for all of the commands to function as part of the same transaction.  The Commit method must be called on the SqlTransaction object in order to complete the transaction.  If there is an exception thrown, it must be caught and dealt with using the Rollback method in the SqlTransaction object.  I hope that helps with any future SQL coding.

 

Richard Franzen
Sr. Developer
ImageSource, Inc. 

How to Count Multi-Page TIFF Files

Here is the simple TIFF file page counter using TiffBitmapDecode.

This code works for most cases. Some TIFF file compression may not work.

 

Kyoungsu Do
Software Quality Engineer
ImageSource, Inc. 

 

Inline Code with XAML

One of the hidden tricks in XAML is that you can actually write inline code directly on the page instead of using the typical code-behind files.  Below is an example on how this works

 

And here is the running code

Backing Out Of A SQL Transaction

When writing MS SQL statements, sometimes it’s necessary to have several queries that work together in unison.  Whether it’s updating, deleting or inserting multiple rows into multiple, different tables, it’s nice to be able to run a series of SQL commands as one single step.  This is where running a batch transaction comes in handy, as the following set of sample code demonstrates.

BEGIN TRANSACTION BigGroupInsert;
INSERT INTO TestTable1 (Field1) values ('This is a sample value.');
INSERT INTO TestTable2 (Field1, Field2) values ('Performing a test insert', 'Into SQL');
INSERT INTO TestTable3 (Field1) values ('All of these inserts should be successful.');
COMMIT TRANSACTION BigGroupInsert;

As you can see, this transaction will perform all of the Insert statements specified.  If there is an error processing one of the statements, like one of the values being to large for the target column, an error will occur for just that statement and all of the rest of the SQL commands will process.  That includes commands that were intended to occur after the failed step, not just the ones before.

But it’s possible this is not the correct course of action to take.  What if the entire batch needs to either perform all of the changes correctly or not make any changes at all?  It could be bad for data integrity if some tables have the updated rows, but related data is missing from other tables because of a SQL error.  Well fear not, there is a solution.  Add the command SET XACT_ABORT ON before the BEGIN TRANSACTION command to have SQL back out all changes in the Transaction if any error occurs.  This way the transaction works as a single unit and you can be guaranteed that that the entire transaction either worked or it didn’t, and there is nothing in between.

SET XACT_ABORT ON;
BEGIN TRANSACTION BigGroupInsert;
INSERT INTO TestTable1 (Field1) values ('If one of these values was too long.');
INSERT INTO TestTable2 (Field1, Field2) values ('Then this whole transaction', 'should fail');
INSERT INTO TestTable3 (Field1) values ('And none of these values should be inserted.');
COMMIT TRANSACTION BigGroupInsert;

For more information about SET XACT_ABORT, please refer to the MSDN article.

Serialization and Deserialization

Serialization is a process of converting an object into a stream of data. So it easily transfers over the network or can save to disk.   So, using this concept of serialization, we can serialize any object to XML string.
Here is a person class that we can serialize.

Here is the code that takes the person object into string of xml format.

Using Serialization in .NET is provided by the System.Runtim.Serialization name space.

Once you have the XML string, now you can save it to disk, store it into database, or transfer over the network.
Here is the deserialization method that takes serialized string of XML into the person object.

 

Kyoungsu Do
Software Quality Engineer
ImageSource, Inc. 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

My digital life

I am fortunate to have a wife that let me use our living room as a playground and not having to worry about the wife acceptance factor for my toys like some of my hifi friends.  I got a bit of time this weekend so I decided to do some cleanup.

Current mess

Below is what I have before the cleaning.  Many of these devices were brought and some even modified by me over the years.

1) Custom – with some help of a gentlemen up in Gig Harbor, WA, I built these Pluto speakers and the subs.  I have listened to and owned a few of the brand names speakers over the years including, JM Lab (owner), Wilson Audio, ProAc (owner), Joseph Audio (owner), THIEL, B&W and many more before settling on the speakers & subs above. 

2) Modified/hack from original.  I know I voided all the warranty on these.

• DAC – brought the original equipment directly from a hifi dealer in China and swapped a bunch of stuff in there ever since.
• Squeeze box – this little item is my digital source where it streams all the lossless songs from my laptop and feed that signal into my DAC, then to crossover, to amp, to the line filter and finally to the speakers.  Like other owners of this device, I upgraded the power supply, caps and others changes on the device.
• Laptop – this used to be my desktop replacement with many little mods including an expense $500 video card for gamming.  Yeah, I was a bit nuts.

behind the mess above is another mess on the back

 

Things removed

Having brought and installed the Xbox to the system, I no longer need the external hard drive, squeezebox and its custom power supply, Oppo DVD player and two remote controls.  These items were removed:

Because I am using a custom DAC, swapping the Squeezebox out with the Xbox did not make any noticeable difference to sound quality.

 

What’s next

I need to buy a real media server to move songs, movies and pictures off from that laptop.  I like the look of the HP MediaSmart Server so I might pick one up when I get some time to work on it.

 

Quick speakers placement tips

Most speakers need room to breath so give them some space between the back and the side walls.  Also, placing a TV right between two speakers is typically not a good idea, move the TV a bit back.

Deadly sins of software development

InfoWorld published a really good article on the 7 sins of software development.  While this is a good list, I would add the following sins as well:

8) Not picking the right technology

This one mainly due to comfort zone and failing to keep up with better tooling or proven techniques out on the industry.  It’s the old saying of trying to force a square peg into a round hole or if all you have is a hammer than everything else looks like a nail.

9) Not thinking about performance

If performance is critical for a project, test it early and test it often.  Don’t wait until near shipping to start making performance changes because it would be too late and too little then.

10) Not considering about security

Isn’t this item obvious?

11) Not talking to users

Unless you are writing the software for yourself, which I do every once in a while , get up and go talk to the users.  They would love you and appreciate your software even more.

If they tell you your software is terrible, thank them, think about what they said and improve on it.  If you get too attached to your software, you will never be able to make it better.