0 Comments

We have a lot of customer data in our system and I wanted to have a shared contact list with all the customer data for my co-workers.

I looking into some Office365 docs and found this walkthrough to create the folder: https://www.cogmotive.com/blog/office-365-tips/create-a-company-shared-contacts-folder-in-office-365

snip_20160322100016

So afterwards you have an empty but shared contact folder. I thought that I needed the Microsoft Graph to access these contacts. Which would require my app to be in azure and it cannot have a command line only because of the authentication. More about that approach can be found here http://dev.office.com/getting-started/office365apis

But it seems that this is not needed for my simple one time import https://msdn.microsoft.com/en-us/office/office365/api/contacts-rest-operations

I thought that it might be a job for a PowerShell script and found this http://www.infinitconsulting.com/2015/01/bulk-import-contacts-to-office-365/

But there is an other option. Use Interop, because I as an Office365 user have Outlook 2016 on my Windows 10 machine and Visual Studio 2015. So create a new (console) application and add a reference to Outlook Interop:

I used this code to get to the “customers” address book:

using System;
using Outlook = Microsoft.Office.Interop.Outlook;

namespace UploadContactsToOffice365
{
    class Program
    {
        static void Main(string[] args)
        {
            var ap = new Outlook.Application();
            
            foreach(Outlook.Folder f in ap.Session.Folders)
            {
                if (f.Name.ToLower().Contains("openbare")) // jp openbare mappen (public folders)
                {
                    Console.WriteLine(f.FullFolderPath);
                    foreach (Outlook.Folder f2 in f.Folders)
                    {
                        if (f2.Name.ToLower().Contains("alle")) // alle openbare mappen (all public folders)
                        {
                            Console.WriteLine(f2.FullFolderPath);
                            foreach (Outlook.Folder f3 in f2.Folders)
                            {
                                if (f3.Name.ToLower().Contains("klanten")) // customers (folder name)
                                {
                                    Console.WriteLine(f3.FullFolderPath);
                                    foreach (Outlook.Folder f4 in f3.Folders)
                                    {
                                        if (f4.AddressBookName.ToLower().Contains("customers"))
                                        {
                                            Console.WriteLine(f4.FullFolderPath);
                                            Console.WriteLine("----------");

                                            /// display current items:
                                            //Outlook.Items oItems = f4.Items;
                                            //for (int i = 1; i <= oItems.Count; i++)
                                            //{
                                            //    Outlook._ContactItem oContact = (Outlook._ContactItem)oItems[i];
                                            //    Console.WriteLine(oContact.FullName);
                                            //    oContact = null;
                                            //}
                                            
                                            // add test item:

                                            Outlook.ContactItem newContact = (Outlook.ContactItem)ap.CreateItem(Outlook.OlItemType.olContactItem);
                                            try
                                            {
                                                newContact.FirstName = "Jo";
                                                newContact.LastName = "Berry";
                                                newContact.Email1Address = "somebody@example.com";
                                                newContact.CustomerID = "123456";
                                                newContact.PrimaryTelephoneNumber = "(425)555-0111";
                                                newContact.MailingAddressStreet = "123 Main St.";
                                                newContact.MailingAddressCity = "Redmond";
                                                newContact.MailingAddressState = "WA";
                                                newContact.Move(f4);
                                                newContact.Save();                                                
                                            }
                                            catch (Exception ex)
                                            {
                                                Console.WriteLine("The new contact was not saved. " + ex.Message);
                                            }
                                            
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }

            Console.ReadKey();
        }
    }
}

This is not the prettiest code I have written. So there must be a better way. But this is just a one time application to loop through some database records and add contacts. So I will just leave it like this. Please let me know in the comments if you have suggestions to improve readability. The code snippet will give this contact in your (and the rest of the companies) outlook:

If you are looking how to import a folder full with vcf cards you should take a look at this MSDN article.

This should give you enough pointers to bulk add contacts to a shared folder in Office365 (Outlook 2016)

Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn
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