smalldatetimetimebomb

SQL Smalldatetime ends in 2079 (June 6^th).

Try

select CONVERT(smalldatetime,'26 Apr 2016')

select CONVERT(smalldatetime,'26 Apr 2106')

select CONVERT(datetime,'26 Apr 2106')

select convert(smalldatetime,CONVERT(datetime,'26 Apr 2106'))

See https://msdn.microsoft.com/en-GB/library/ms182418.aspx

 

To find which columns are affected,

select t.name, c.name from sys.columns c

inner join sys.tables t on t.object_id=c.object_id

where system_type_id=58

whoo! that’s my pension sorted out. no, wait, I’ll be dead…

SQL timeout from C# SqlCommand: check parameter direction, use SQL Trace

Unexpected SQL timeouts in a SqlCommand?

I think it was a lock conflict… i was opening (what i thought was a firehose cursor) recordset with many rows. I reduced this and the problem went away. In this case it’s a regular job so I can process 100 rows each time.

However it also helped to check parameter .Direction

writebackCmd = new System.Data.SqlClient.SqlCommand();
writebackCmd.CommandType = System.Data.CommandType.Text;
writebackCmd.CommandText = sqlWritebackCmd;
writebackCmd.Connection = sqlWritebackConnection;
writebackCmd.Parameters.Add(“@P0”, System.Data.SqlDbType.Int);//new ab_id aka rowId
writebackCmd.Parameters.Add(“@P1”, System.Data.SqlDbType.Int);//primary key
writebackCmd.Parameters[0].Direction = System.Data.ParameterDirection.Input;
writebackCmd.Parameters[1].Direction = System.Data.ParameterDirection.Input;
writebackCmd.Prepare();

When I added the .Direction =  code this reduced the number of timeouts.

It was interesting to look at the sql calls using Sql Server Profiler.
For convenience set the ApplicationName to something recognisable then filter on that…

var csb = new System.Data.SqlClient.SqlConnectionStringBuilder();
csb.DataSource = …
csb.MultipleActiveResultSets = true;
csb.ApplicationName = “ChilliABLink”;

In Sql Query Analyzer, set APP=ChilliABLink

(found this here…)

From that I can see that the first call that C# makes is

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N’@P0 int,@P1 int’,N’update [tblEvent] set [sample_sent_ab_id]=@P0 where[EventID] = @P1 and [sample_sent_ab_id] is null or [sample_sent_ab_id] @P0 ‘,@P0=213191,@P1=450661
select @p1

…after which it calls…

exec sp_execute 1,@P0=213191,@P1=450689

C# string split with string separator: have to use second parameter

Don’t leave out the StringSplitOptions or you will waste time wondering why it’s not picking up the correct overload…

        [TestMethod]
        public void NickShouldKnowHowToSplitABloodyStringByNow()
        {
            string identityTablesSql = @"
/****** Object:  Table [AspNetRoles]    Script Date: 30/03/2016 12:17:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[AspNetRoles]’) AND type in (N’U’))
BEGIN

        }";

            string[] stringSeparators = new string[] { "GO\r\n" };
            foreach (var sql in identityTablesSql.Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries))
            {
                System.Diagnostics.Debug.WriteLine(sql);
            }
        }

Node tedious cannot connect to localhost ECONNREFUSED ESOCKET

Failed to connect to localhost:1433 – connect ECONNREFUSED

code:ESOCKET

OK. go to Sql Configuration Manager – SQL Network Configuration – Protocols for SQL – check you have TCPIP turned on for SQL NETWORK CONFIGURATION.

tedious does not use the SQL Native Client

I started with this… https://azure.microsoft.com/en-gb/documentation/articles/sql-database-develop-nodejs-simple-linux/

hope this helps!

ionic button ng-click within ion-item not working

…with <button type=’button’ … it didn’t work, but with
<div class=”‘button'”> it did. It may also not like being within a <label>

Access listbox / combobox RowSource does not like leading spaces

sql = “      select ProductId, ProductName from tblProduct”

me.ProductsListBox.RowSource = sql

NO! you won’t get anything in your list box. Remove the leading space in the SQL statement.

Upload and download files to Azure blob storage via a WebApi

I need to let users upload videos and PDFs to my web site. I would like to store these in Azure blob storage.

Making this work takes many steps.

Then I thought I’d like to download files thru my Web Api. That part took many more.

Getting started

Start Visual Studio 2013
Create a new Web App project
Create a new ApiController /api/MediaFileController
Install Azure Storage: in package manager console,
PM> Install-Package Azure.Storage

In web.config, increase the size of your maxRequestLength and maxAllowedContentLength for /api/MediaFile only

…</system.webServer>

<location path="api/MediaFile">
  <!–http://weblogs.asp.net/jongalloway/large-file-uploads-in-asp-net–>
  <!–http://stackoverflow.com/questions/2759461/increasing-max-upload-file-size-on-iis7-win7-pro–>
  <system.web>
    <httpRuntime maxRequestLength="1048576" />
    <!– in kB–>
  </system.web>
  <system.webServer>
    <security>
      <requestFiltering>
        <requestLimits maxAllowedContentLength="1073741824" />
        <!– in bytes, aka max value of uint–>
      </requestFiltering>
    </security>
  </system.webServer>
</location>

<runtime>…

(it’s in https://github.com/NickAjderian/FileUploadWebApp/blob/master/FileUploadWebApp/Web.config)

When you add your first WebApi controller you may need edit Global.asax.cs to add a call            
    GlobalConfiguration.Configure(WebApiConfig.Register);
    …this needs to before RouteConfig… how do you think i know…?

Use the code for MediaFileController.cs (attached below) in your project. This includes 3 helper classes.

But the essentials are a Post method

public void Post()
{
    var request = HttpContext.Current.Request;
    var fileId = request.Form["FileId"];
    if (!string.IsNullOrEmpty(fileId) && request.Files.Count == 1)
    {
        FileHelper.Save(fileId, request.Files[0].InputStream);
    }
}

…and a Save method…

public void Save(string fileId, Stream stream)
{
    var storageAccount = CloudStorageAccount.Parse(
                       ConnectionString
                       );
    var blobClient = storageAccount.CreateCloudBlobClient();
    var container = blobClient.GetContainerReference("media");

    container.CreateIfNotExists();

    container.SetPermissions(
        new Microsoft.WindowsAzure.Storage.Blob.BlobContainerPermissions { PublicAccess = Microsoft.WindowsAzure.Storage.Blob.BlobContainerPublicAccessType.Blob });

    var blob = container.GetBlockBlobReference(fileId);

    blob.UploadFromStream(stream);

    //http://justazure.com/azure-blob-storage-part-6-blob-properties-metadata-etc/

}

Using File or Blob Storage

I wrote a FileHelper and an AzureBlobHelper: they are at… https://github.com/NickAjderian/FileUploadWebApp/blob/master/FileUploadWebApp/Api/MediaFileController.cs

If you want to use blob storage,
Sign up for an Azure storage account and get the Blob Client key.
Tell MediaFileController to use this storage account
private static IFileHelper fileHelper = new AzureBlobHelper() { ConnectionString = "DefaultEndpointsProtocol=https;AccountName=flikkuploadtoblob;AccountKey=a-key==" };

If you want to use file storage,
private static IFileHelper fileHelper = new FileHelper();

Listing files

I created a method to list files in storage.

public IEnumerable<string> Get()
{           
    //TODO: check what files the current user can see
    return FileHelper.Get();
}

The list of files comes from the AzureBlobHelper .Get method…

public IEnumerable<string> Get()
{
    var storageAccount = CloudStorageAccount.Parse(
                       ConnectionString
                       );
    var blobClient = storageAccount.CreateCloudBlobClient();
    var container = blobClient.GetContainerReference("media");

    var blobs = container.ListBlobs().ToList();
    //get relative uri from /media/, strip off leading /
    return blobs.Select(x => x.Uri.ToString().Replace(container.Uri.ToString(), "").Replace("/", ""));

}

You should now be able to see a list of blobs or files by going to
http://localhost:1234/api/MediaFile

Keep your Storage Key out of source control

You should not put your storage key into your code or source control,
so put a temp value into web.config and change the ConnectionString when you Publish the project
<connectionStrings>
    <add name="StorageConnection" connectionString="…
or, better, put it in appSettings.secret
    change your .gitignore to ignore *.secret
    Link your web.config to the file
    <appSettings file="appSettings.secret">
        <add key="StorageConnection" value="Your

Uploading files

You now want to send a Post request to http://localhost:1234/api/MediaFile. In the HTML form below you can name the file and then upload it. It will be stored with the name (FileId) you type.

<form action="~/api/MediaFile" enctype="multipart/form-data" method="post">
    <input type="file" name="file" class="btn" />
    <p>
        <button type="submit">Upload File</button>
    </p>
    <label for="FileId">File name</label>
    <input type="text" name="FileId" placeholder="Media Id" />
</form>

Improved file upload with Angular

The Angular libarry ng-file-upload adds some file upload functions, including drag-and-drop uploads. I’ve used it in a small way in https://github.com/NickAjderian/FileUploadWebApp/blob/master/FileUploadWebApp/Views/Home/Index.cshtml

 

Some other points

IIS does not like . in the last part of a URL (http://weblogs.asp.net/owscott/handing-mvc-paths-with-dots-in-the-path)
So if you are writing a link to go to your API to download a file, add a / at the end of each URL.
Then add routes in WebApiConfig for each of the combinations…

https://github.com/NickAjderian/FileUploadWebApp/blob/master/FileUploadWebApp/App_Start/WebApiConfig.cs

There are overloads in MediaFileController for each possibility

If you want a byte[] from a MemoryStream and you use ms.GetBuffer() the buffer can be a lot longer than the size of the data.
Trim it to ms.Length.

If you click the link (e.g. to http://fileuploadwebapp.azurewebsites.net/Api/MediaFile/FoiledAgain33.mp4/) Chrome will download the file as FoiledAgain33.mp4

The Excuses

The source control contains little or no error handling.

I’ve kept relevant code in single files so there are several classes in

https://github.com/NickAjderian/FileUploadWebApp/blob/master/FileUploadWebApp/Api/MediaFileController.cs

Similarly there’s HTML and javascript in

https://github.com/NickAjderian/FileUploadWebApp/blob/master/FileUploadWebApp/Views/Home/Index.cshtml

There’s a live web site at

http://fileuploadwebapp.azurewebsites.net/

If I get lots of junk uploaded to it then I’ll take it down.

The file upload limit is several 10s of MB but I’m saving to byte[] so this website is super-vulnerable to a DOS attack. Go on, see if i care.