How to copy files in Azure Storage (Using AzCopy)

If you ever need to copy files from one “folder” in Azure Storage you can do this by running the following powershell command.

Example:

If you need to copy all files from mystorage.blob.core.windows.net/old-location to mystorage.blob.core.windows.net/new-location including all sub-paths (ex. old-location/folder1, old-location/folder2, etc.) you can do this by running the following command.

 AzCopy  /Source:https://mystorage.blob.core.windows.net/old-folder
/Dest:https://mystorage.blob.core.windows.net/new-folder

/SourceKey: your_key /DestKey:your_key /S 

/S command will perform recursive copy. If you do not need to copy recursively just remove this command.

If you are copying within the same storage your SourceKey and DestKey will be the same. You can find your storage key using powershell, azure portal, or with Microsoft Azure Storage Explorer

With Azure Storage Explorer you can right-click on the storage and click “Copy primary key”

Advertisements

How to export all data from the SQL server database to Excel (using C#)

Sometimes non-technical users prefer to work with the raw data in Excel instead of querying the database. It is easy to export one db table to Excel using simple C# script. But how to do this for the whole database?

Let’s say you have a button in the interface called “Export everything”. After clicking on it an excel file will be downloaded with one table per tab including column names as headers.

My version of the script uses Epplus library:

Install-Package EPPlus -Version 4.1.1
And below is a full script. It is pretty straightforward :

public byte[] ExportAllTables()
{
var cs = ConfigurationManager.ConnectionStrings[“DrexelContext”].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
var listTables = new List<string>();
con.Open();
DataTable t = con.GetSchema(“Tables”);

using (ExcelPackage p = new ExcelPackage())
{
for (var i = 0; i < t.Rows.Count; i++)
{
var x = Convert.ToString(t.Rows[i][“TABLE_NAME”]);
using (SqlCommand sqlCmd = new SqlCommand(“select * from ” + x , con))
{
using (SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd))
{
DataSet ds = new DataSet();
sqlDa.Fill(ds);
if (ds.Tables.Count == 1)
{
foreach (DataTable table in ds.Tables)
{
table.TableName = x;
ExcelWorksheet worksheet = p.Workbook.Worksheets.Add(x);
worksheet.Cells[“A1”].LoadFromDataTable(table, true, OfficeOpenXml.Table.TableStyles.Medium2);
}
}
}
}
}
con.Close();
return p.GetAsByteArray();
}
}
}

How to automatically truncate/trim strings on insert in entity framework

Recently we had a task when we had a database with limited varchar length. Example:


TaskId int not null

TaskName nvarchar(5) null

Description nvarchar(50) null

We have data coming from this table from multiple sources and sometimes data overflows the fields. In entity framework if you try to assign value to taskName that is larger than 5 characters it will fail with “Entity Validation Failed”

for example:

task.TaskName ="MyLongTaskName";
ctx.SaveChanges();

Now, in our case we wanted to substring all values to fit in the field without throwing an exception.

so, in our example we wanted to have TaskName=”MyLon”

Since we are using EF6.1.3 it was possible to utilize EF interceptors to do this.

Below is the full code for Entity Framework interceptor that will truncate/trim all of the strings to the max size of a column. Just create a separate file and it should work.

You will also need to modify configuration to use this interceptor.

public class MyConfiguration : DbConfiguration
{
public MyConfiguration()
{

AddInterceptor(new StringTrimmerInterceptor());

}

}

 


public class StringTrimmerInterceptor : IDbCommandTreeInterceptor
{
private static readonly string[] _typesToTrim = {"nvarchar", "varchar", "char", "nchar"};

public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
{
if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace)
{
var insertCommand = interceptionContext.Result as DbInsertCommandTree;
if (insertCommand != null)
{

List<DbModificationClause> finalSetClauses =
new List<DbModificationClause>(
(IEnumerable<DbModificationClause>) insertCommand.SetClauses.Select(
a =>
{
var dbSetClause = a as DbSetClause;
if (dbSetClause != null)
{
var dbPropertyExpression = dbSetClause.Property as DbPropertyExpression;

if (dbPropertyExpression != null)
{
var edmProperty = dbPropertyExpression.Property as EdmProperty;
if (edmProperty != null && edmProperty.MaxLength != null &&
_typesToTrim.Contains(edmProperty.TypeName))
{
var dbConstantExpression = dbSetClause.Value as DbConstantExpression;
if (dbConstantExpression != null && dbConstantExpression.Value != null)
{
var value = dbConstantExpression.Value.ToString();
if (!string.IsNullOrEmpty(value) &&
value.Length > (int) edmProperty.MaxLength)
{
return DbExpressionBuilder.SetClause(
DbExpressionBuilder.Property(
DbExpressionBuilder.Variable(
insertCommand.Target.VariableType,
insertCommand.Target.VariableName),
dbPropertyExpression.Property.Name),
EdmFunctions.Trim(
dbConstantExpression.Value.ToString()
.Substring(0, (int) edmProperty.MaxLength)));
}

}
}
}
}

return a;
}));

var newInsertCommand = new DbInsertCommandTree(
insertCommand.MetadataWorkspace,
insertCommand.DataSpace,
insertCommand.Target,
new ReadOnlyCollection<DbModificationClause>(finalSetClauses),
insertCommand.Returning);

interceptionContext.Result = newInsertCommand;
}
}

&nbsp;

var updateCommand = interceptionContext.Result as DbUpdateCommandTree;
if (updateCommand != null)
{

List<DbModificationClause> finalSetClauses =
new List<DbModificationClause>(
(IEnumerable<DbModificationClause>) updateCommand.SetClauses.Select(
a =>
{
var dbSetClause = a as DbSetClause;
if (dbSetClause != null)
{
var dbPropertyExpression = dbSetClause.Property as DbPropertyExpression;

if (dbPropertyExpression != null)
{
var edmProperty = dbPropertyExpression.Property as EdmProperty;
if (edmProperty != null && edmProperty.MaxLength != null &&
_typesToTrim.Contains(edmProperty.TypeName))
{
var dbConstantExpression = dbSetClause.Value as DbConstantExpression;
if (dbConstantExpression != null && dbConstantExpression.Value != null)
{
var value = dbConstantExpression.Value.ToString();
if (!string.IsNullOrEmpty(value) &&
value.Length > (int) edmProperty.MaxLength)
{
return DbExpressionBuilder.SetClause(
DbExpressionBuilder.Property(
DbExpressionBuilder.Variable(
updateCommand.Target.VariableType,
updateCommand.Target.VariableName),
dbPropertyExpression.Property.Name),
EdmFunctions.Trim(
dbConstantExpression.Value.ToString()
.Substring(0, (int) edmProperty.MaxLength)));
}

}
}
}
}

return a;
}));

var newInsertCommand = new DbUpdateCommandTree(
updateCommand.MetadataWorkspace,
updateCommand.DataSpace,
updateCommand.Target,
updateCommand.Predicate,
new ReadOnlyCollection<DbModificationClause>(finalSetClauses),
updateCommand.Returning);

interceptionContext.Result = newInsertCommand;
}
}

 

 

HTTP Error 500.19 – Related configuration data for the page is invalid – Visual Studio

After you get latest from your repository you see the following error:

HTTP Error 500.19

The requested page cannot be accessed because the related configuration data for the page is invalid.

ConfigError.pngSometimes, the reason is because wrong config file path was commited to the repository and Visual Studio couldn’t pick up the right one on your machine just yet.

How to fix:

  1. Stop debugging
  2. Right click on your project that you try to run and click Unload Project
  3. Right click on your project and click Reload Project.

Unload.png

The error should be gone.

SaveChanges() always returns 0 – Entity Framework

Today, I experienced a bug in the application where SaveChanges() always returned 0 even though records were updated successfully in the database.

According to EF documentation, SaveChanges() should return a number of affected rows.

Solution:

In my case, I had child method that already called SaveChanges(). So, it means that only the first SaveChanges() will have correct return.

Here’s what happened in my case:

Context.Students.Add(Student);

Context.SaveChanges();  //Returns 1

Context.SaveChanges(); //Returns 0

If you experience similar issues just make sure that you only have Context.SaveChanges() once.

How to remove un-removable resources from Azure.

Recently, I’ve had an issue when I couldn’t remove Tinfoil Security 3rd party addon from my Azure subscription. When I tried to remove it from Extensions, it failed with “Operation couldn’t be completed” error.

So, in order to solve the error I tried to use PowerShell ISE and it worked!

This applies to all resources that do not have a delete button or have errors when you try to remove them.

You will need to perform the following steps:

RemoveResourcePowershell.png

 

Login-AzureRmAccount -SubscriptionId {your_id}

Select-AzureRmSubscription -SubscriptionId {your_id}

Remove-AzureRmResource -ResourceId {Your full resourcename. Can be copied from the portal}

After these steps are done you will need to refresh the portal and see this resource gone. You might want to give it a couple of minutes.

 

How to remove extensions (3rd party addons) from the app service in Azure

Azure provides a marketplace with different 3rd party extensions available: NewRelic, SendGrid, Tinfoil Security, etc. After installation, you usually get a free trial but then you will be charged monthly for these services. If you no longer wish to use these extensions you can remove them through the portal directly. (Or through the PowerShell)

Here’s how you can do this from the portal:

  1. Navigate to the app service:

Screenshot_1.png

2. Click extensions

3. Click delete.

Screenshot_2.png

And you are done. I couldn’t find this in the docs but it was an easy fix.

Azure Function: Publish Failed in Visual Studio 2017 (Solved)

So, I’ve been trying to publish an Azure Function app using the latest Visual Studio with built-in support for Azure functions (Visual Studio 2017 preview 3)

When I clicked deploy button I received bland “Publish Failed” without any details.

publish.png

failed.png

In my case the solution was simple: I had to close Fiddler. I used fiddler to test my Azure HTTP functions and didn’t close it. VS couldn’t connect to the server, thus the error.

After closing fiddler and making sure internet is working, everything works again.

Azure http function returns 404 when passing parameter

Yesterday, I was working on the Azure function that can be called via GET method with a parameter.

Here’s my function:

GET http://mysample.azurewebsites.net/api/CheckDomain/test.com

So, I needed to verify if domain exists.  When calling it from Fiddler it returned 404.

When calling it from the browser it also returned 404 with a message:

The resource you are looking for has been removed, had its name changed, or is temporarily unavailable.

The solution is simple. Browser cannot correctly identify parameter with a dot, so the correct call should look like this:

GET http://mysample.azurewebsites.net/api/CheckDomain/test.com/

 

 

How to copy an azure function

Copying an azure function can be really helpful in some cases. Let’s say you just want to change trigger parameters and keep the rest of the functionality the same. You have two choices:

  1. Recreate logic
  2. Copy existing function and modify parameters

Recreating logic can produce bugs and takes more time but Azure doesn’t have an easy way to copy function at the moment from the UI.

Below are the steps you can take to copy a function:

  1. Log in to azure portal and download publish profile of your Azure functions app

Screenshot_6.png

2. Open publish profile and search for ftp info. (You can open it with notepad)

3. Connect to the FTP and then navigate to: site\wwwroot

4. Create a new folder for new function (Name of the folder = Name of the function)

Screenshot_1.png

5. Copy contents of your original folder to the new folder.

6. Refresh portal page and you will see two functions now. All done!

Screenshot_2.png