Windows Azure – How to migrate data from SQL Server to a Storage account

Starting from this great article https://azure.microsoft.com/en-gb/documentation/articles/storage-dotnet-how-to-use-tables/ we started by including the required imports:

using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Auth;
using Microsoft.WindowsAzure.Storage.Table;
using System.Configuration;
using System.Data.SqlClient;

Code

This code is reliant on a form that requests the user to enter the database where the data will be copied from.

Capture

In this example it’s “Inv2046_VZA”. The code below is associated with the “Go” button. The delete table can be seen in this post.

The data is copied from the vendors table into a local class object which inherits from the TableEntity structure. You will need to set the PartitionKey and the RowKey. In this example, the data is partitioned by the fileid (the file which generated the uploaded data) and the rowid which is a unique row per each file.

Capture

You will also need to create an empty constructor.

The rest are properties that will need to be added to the tables.

We are using batch operations and we have a few restrictions: we can only add a maximum of 100 rows per batch and the PartitionKey needs to be unique per batch. This is why we keep track of the fileid and when it changes, we insert the data. Alternatively, if we are on our 100th item, we insert the data.

Capture

Once the data is moved, you can truncate the SQL Server as the data is now in the storage account.

 

try
            {
                // Retrieve the storage account from the connection string.
                CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
                        ConfigurationManager.AppSettings["StorageConnectionString"]);

                // Create the table client.
                CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

                // Create the table if it doesn't exist.
                //Table names must be unique within an account.
                //Table names may contain only alphanumeric characters.
                //Table names cannot begin with a numeric character.
                //Table names are case-insensitive.
                //Table names must be from 3 to 63 characters long.
                //Some table names are reserved, including "tables". Attempting to create a table with a reserved table name returns error code 404 (Bad Request)..
                string strTableName = txtClientDB.Text.Trim().Replace("_","") + "vendors";
                strTableName = strTableName.ToLower();
                CloudTable table = tableClient.GetTableReference(strTableName);
                // Delete the table it if exists.
                table.CreateIfNotExists();

                // Create the batch operation.
                TableBatchOperation batchOperation = new TableBatchOperation();

                SqlConnection cn = new SqlConnection("Server=statementmatch;Database=" + txtClientDB.Text.Trim() + ";User ID=invUser;Password=CarraLucia$%;Trusted_Connection=False;Encrypt=False;Connection Timeout=30;");
                SqlCommand com = new SqlCommand("SELECT * from tblVendorImport", cn);
                com.CommandType = System.Data.CommandType.Text;
                com.CommandTimeout = 99999;
                try
                {
                    lblSuccess.Text = "";
                    cn.Open();
                    SqlDataReader dr = com.ExecuteReader();
                    int i = 0; string strOriginalFileID = "";
                    while (dr.Read())
                    {
                        // Create a new customer entity.

                        string fileid = ""; string rowID = "";
                        fileid = dr["FileID"].ToString();
                        rowID = dr["RowID"].ToString();
                        VendorEntity customer1 = new VendorEntity(fileid, rowID);
                        customer1.ClientID = dr["ClientID"].ToString();
                        customer1.SupplierNumber = dr["SupplierNumber"].ToString();
                        customer1.CorporateGroupID = dr["CorporateGroupID"].ToString();
                        customer1.Name1 = dr["Name1"] + "";
                        customer1.Name2 = dr["Name2"] + "";
                        customer1.Description = dr["Description"] + "";
                        customer1.POBox = dr["POBox"] + "";
                        customer1.StreetSupplement = dr["StreetSupplement"] + "";
                        customer1.PostalCode = dr["PostalCode"] + "";
                        customer1.City = dr["City"] + "";
                        customer1.CountryCoded = dr["CountryCoded"] + "";
                        customer1.CountryName = dr["CountryName"] + "";
                        customer1.TelephoneNumber = dr["TelephoneNumber"] + "";
                        customer1.FaxNumber = dr["FaxNumber"] + "";
                        customer1.VATRegistrationNumber = dr["VATRegistrationNumber"] + "";
                        customer1.TaxNumber1 = dr["TaxNumber1"] + "";
                        customer1.TaxNumber2 = dr["TaxNumber2"] + "";
                        customer1.PaymentTerm = dr["PaymentTerm"] + "";
                        customer1.PaymentMethod = dr["PaymentMethod"] + "";
                        customer1.Blocked = dr["Blocked"] + "";
                        customer1.Systemid = dr["Systemid"] + "";
                        customer1.EmailAddress = dr["EmailAddress"] + "";
                        customer1.AccountWithVendor = dr["AccountWithVendor"] + "";
                        i++;
                        // Add both customer entities to the batch insert operation.
                        //All entities in a given batch must have the same partition key.
                        if (strOriginalFileID=="")
                        {
                            strOriginalFileID = fileid;
                        }
                        if (fileid == strOriginalFileID)
                        {
                            batchOperation.Insert(customer1);

                        }
                        else
                        {
                            strOriginalFileID = fileid;
                            if (batchOperation.Count > 0)
                            {
                                table.ExecuteBatch(batchOperation);
                                batchOperation.Clear();
                                lblSuccess.Text += "[" + strOriginalFileID + "] Added " + i.ToString() + " rows.. 
";
                                i = 0;
                            }
                        }
                        if (i == 100)
                        {
                            if (batchOperation.Count > 0)
                            {
                                // Execute the batch operation.
                                table.ExecuteBatch(batchOperation);
                                batchOperation.Clear();
                                lblSuccess.Text += "[" + strOriginalFileID + "] Added 100 rows.. 
";
                            }
                            i = 0;
                        }
                    }
                    if (i > 0)
                    {
                        if (batchOperation.Count > 0)
                        {
                            // Execute the batch operation.
                            table.ExecuteBatch(batchOperation);
                            batchOperation.Clear();

                            lblSuccess.Text += "Added " + i.ToString() + " rows.. 
";
                        }
                    }
                    lblSuccess.Visible = true;

                    dr.Close();
                }
                catch (Exception ex)
                {
                    lblError.Visible = true;
                    lblError.Text = ex.Message;
                }
                finally
                {
                    cn.Close(); cn.Dispose();
                    com.Dispose();
                }
            }
            catch (StorageException exc)
            {
                lblError.Visible = true;
                lblError.Text = exc.RequestInformation.ExtendedErrorInformation.ErrorMessage;
            }catch(Exception ex)
            {
                lblError.Visible = true;
                lblError.Text = ex.Message;
            }
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s