0 Comments

Lazy productive I as a developer am, I did not want to write a stored procedure. So I ended up with a lot of SQL statements which needed to be combined and executed only if the previous one succeeds.

However there was a timeout during the execution. It was strange because when I copy pasted the SQL statement in SMSS it worked perfectly and fast too. My mistake was to use a new SqlCommand object which was not part of the transaction. I needed to re-use the same sqlcommand. Here is some working code:

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConString"].ToString()))
{
    con.Open();

    SqlCommand cmd = con.CreateCommand();
    SqlTransaction transaction = con.BeginTransaction();

    cmd.Connection = con;
    cmd.Transaction = transaction;

    try{
    
        cmd.CommandText = "SELECT productcode FROM products WHERE productid = " + productid;
        string code = cmd.ExecuteScalar().ToString();
    
        cmd.CommandText = "SELECT ProductID, ProductWeight, ProductEuroPrice, OrderID FROM ORDERPICKINGPRODUCTS WHERE orderid = " + orderid + " AND productid = " + productid;
        SqlDataReader sdr = cmd.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Load(sdr);
    
        cmd.CommandText = "DELETE FROM ORDERPICKINGPRODUCTS WHERE orderid = " + orderid + " AND productid = " + productid;
        cmd.ExecuteNonQuery();
        transaction.Commit();
    }
    catch(Exception e)
    {
        transaction.Rollback();
    }
}
I know that it is better to use parameters https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters%28v=vs.110%29.aspx

And the AddWithValue method… But this is just pseudo code and not in production. So don’t worry!

This is just a small sample to help people with a strange timeout which you can encounter if you use an sqlcommand outside the transaction.

 

Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments

Microsoft SQL Server has the ability to format the query output to xml, which is great. I used version “Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)” to output XML to have a nested data structure, but there is unfortunately no first citizen support for JSON which is big on the web these days. Perhaps it is left out on purpose to make sure that people are unable to direct output their data as JSON and are forced to build a layer in between so that it can also authenticate and add security to a request. Web API is great for this. But back to the subject: I wanted to output my nested data as JSON really quickly with the minimum amount of code.

In a nutshell, I got the data as xml and converted that to JSON and send it to the output of a generic handler (*.ashx)

Here is my source code for my easy SQL2JSON solution:

private XDocument GetProducts(string q)
{
    SqlCommand comProd = new SqlCommand(@"SELECT ProductCode, ProductEan13, CategoryName FROM PRODUCTS 
        inner join CATEGORIES on products.CategoryID = CATEGORIES.CategoryID
        WHERE ((FactoryCode LIKE @name) OR (ProductCode LIKE @name)) AND (ProductDeleted = 0) for xml raw ('Product'), ROOT ('Products');");
    comProd.Parameters.AddWithValue("name", q + "%");
    string xml = SsDb.GetScalar(comProd).ToString();
    return XDocument.Parse(xml);
}

This gave me an `XDocument` object which I still have to convert to JSON

Since I was using an generic handler I retrieved the search parameter from the query string and used it as input parameter for the `GetProducts` method.

public void ProcessRequest(HttpContext context)
{
    context.Response.ContentType = "application/json";
    context.Response.Cache.SetCacheability(HttpCacheability.Public);
    context.Response.Cache.SetExpires(DateTime.Now.AddDays(1));
    context.Response.Cache.SetMaxAge(new TimeSpan(24, 0, 0));

    string q = context.Request.QueryString.Get("q");
    context.Response.Write(JsonConvert.SerializeXNode(GetProducts(q)));
}

So the most important line is the last one:

context.Response.Write(JsonConvert.SerializeXNode(GetProducts(q)));

Do not forget to use the one-nuget-package-that-rules-them-all (Install-Package Newtonsoft.Json )

using Newtonsoft.Json;

 

Now you can access your JSON data source with the C# generic handler from your JavaScript

yourHandler.ashx?q=yourproductcode

I hope this has been of any help. good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments

This post covers storing and retrieving settings with a WPF combobox for the UI. If you are looking for a solution to store settings across devices, for instance a Windows Surface tablet and a Windows Phone powered device, you should check out the blog of Mike Taulty. That is called roaming data storage. And if you are not working on an app for Windows RT/metro or Windows Phone but are just coding some WPF application, you should read this post from Scott Hanselman.

Let’s start with some code now that it’s clear what this post covers and what it doesn’t cover.

Here is my XAML from my userinterface:

<ComboBox x:Name="cbCountry" 
    SelectionChanged="ComboBox_SelectionChanged" 
    PlaceholderText="Select country" SelectedValuePath="Tag">
    <ComboBoxItem Content="Australia" Tag="au"/>
    <ComboBoxItem Content="New Zealand" Tag="nz"/>
</ComboBox>

Please note the ‘SelectedValuePath’ is being set to ‘Tag’, otherwise it would grab the ‘comboboxitem’ or ‘content’ like this:

var comboboxContent = ((ComboBoxItem)cbCountry.SelectedValue).Content;

With the value path set to tag, it will grab ‘au’ or ‘nz’ as selected value.

This is the constructor of the xaml page containing my settings.

var localSettings = Windows.Storage.ApplicationData.Current.LocalSettings;

this.InitializeComponent();

if (localSettings.Values.ContainsKey("country"))
{
    cbCountry.SelectedValue = localSettings.Values["country"];
}

and this is the code for the selectionchanged event of the combobox.

private void ComboBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
    var localSettings = Windows.Storage.ApplicationData.Current.LocalSettings;
    localSettings.Values["country"] = cbCountry.SelectedValue;
}

Good luck with this small code sample. If this is not what you are looking for, please check the links mentioned at the start. I strongly believe that they take you to the code that you are looking for (if the code above is insufficient)

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments

Here is a small C# code snippet to upload an XML file to an explicit SSL secured FTP server.

When you search nuget for FTP you will get:

image

But which one do you need? Not all of them support FTPS. Well the checkbox gives it away.

If you search for FTPS you will only get these seven results (at this moment):

image

So you need the System.Net.FtpClient from https://netftp.codeplex.com/

And here is the C# code to upload an XDocument to an FTPS server:

private void UploadFile(XDocument xDoc, string filename)
{
    using (FtpClient fc = new FtpClient())
    {
        fc.Credentials = new NetworkCredential(username, password);
        fc.Host = hostname;
        fc.EncryptionMode = FtpEncryptionMode.Explicit;
        fc.ValidateCertificate += fc_ValidateCertificate;
        fc.Connect();
        fc.SetWorkingDirectory("/uploaddir");

        using (var ftpStream = fc.OpenWrite(filename, FtpDataType.Binary))
        {
            if (ftpStream != null)
            {
                xDoc.Save(ftpStream);
                ftpStream.Close();
            }
        }
    }
}

static void fc_ValidateCertificate(FtpClient control, FtpSslValidationEventArgs e)
{
    e.Accept = true;
}

Self signed certificates will otherwise always cause an exception in the ftp libraries because the certificates are not really valid. So you manually have to “validate” it.


Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments
  •   Posted in: 
  • C#

Dymo has some great label printing hardware and has a nice .Net SDK. It seems that they are moving away from the .Net SDK and more towards the JavaScript SDK. In my situation I wanted to host a small Rest service on a Windows 2012 server so that I can make an Ajax call with jQuery to it to print a label from my web application. So it is a kind of printing proxy with REST. We had previously only Internet explorer support in our web application, so using the Active X component was easy for us. Though it required a client installation and the client to configure the network shared printer.

The strange thing is that this code looks good at first sight. Also if you compare it with the Dymo label code. It even works great on my machine, but when I put it live on our production server it failed. No exception or whatsoever, but it simply said that there is no label writer.

StringBuilder sb = new StringBuilder
var label = DYMO.Label.Framework.Label.Open(System.Web.HttpContext.Current.Server.MapPath(@"~/Content/thelabel.label")); // mvc webapplication
label.SetObjectText("Address", sb.ToString());
label.Print(ConfigurationManager.AppSettings.Get("printername"));
// printername = \\pcname\dymo
  

Even when I list the label printer, it does not show up in the list. Printing with the default label printing software from dymo (version 8.x) works great on my machine and the server, but not by code.

StringBuilder sbPrinters = new StringBuilder();
DYMO.Label.Framework.Printers p = new DYMO.Label.Framework.Printers();
foreach(var printer in p.ToList())
{
    sbPrinters.AppendLine(printer.Name);
    sbPrinters.AppendLine(printer.ModelName);
    sbPrinters.AppendLine(printer.IsConnected.ToString());
    sbPrinters.AppendLine(printer.IsLocal.ToString());
    sbPrinters.AppendLine("-==========-");
}
return sbPrinters.ToString(); // empty...

I have tried an Asp.Net web application 4.5 with WebApi on Windows 2012 and a 4.0 Webforms application on Windows 2003. Both did not work. So what is wrong? I used the build in Visual Studio development server and on both windows servers (2003 and 2012) I used IIS. So that might be the problem. I have heard about Katana and OWIN and started a small test project with this guide. Somehow it works when you are not using IIS! I also tried Asp.Net impersonation in the web.config. Maybe running in full trust works, but I prefer a tiny Windows Service with Owin. So go check out the project page of Katana on codeplex or simply pull down this Nuget package of Microsoft Owin. This Channel9 video of 43 minutes is also a great online source to learn more about the Katana project, OWIN for Asp.Net.

Good luck with the Dymo .Net SDK!

Pin on pinterest Plus on Googleplus Post on LinkedIn