Tagged “c#”

1. Fast Inserts to Oracle with C#

2022 Jul 01 c# oracle

Recently I found myself having to do bulk record inserts to an Oracle table on an API I created with .Net 6. I was using EntityFrameworkCore for this and read that even though I was using AddRange, the inserts actually happen one record at a time and would end up taking a very long time to complete.

After some research and experimentation, I found that OracleBulkCopy was able to do the job for me. Actually, there was a the Z.EntityFramework.Extensions.EFCore NuGet package that would have done the work for me very, very easily, but the license price was a bit too steep for me at the moment. For a large company with the budget, though, I would recommend it especially to keep your code simpler.

For my solution, you need to first make sure you have Oracle.ManagedDataAccess installed from NuGet. You can use the Core version instead, if you are using a .Net core project.

Make sure to include the using statement:

using Oracle.ManagedDataAccess.Client;

This method does require your records to be in a DataTable, though, but otherwise here's some example code on how to use OracleBulkCopy:

// Your records need to be in a DataTable.
var records = <your_records>;

using OracleConnection connection = new (<your_connection_string>);
connection.Open();

using OracleBulkCopy bulk = new(connection);
bulk.DestinationTableName = "<destination_table>";
bulk.ColumnMappings.Add(new OracleBulkCopyColumnMapping { DestinationColumn = "<destination_column>", SourceColumn = "<source_column>" }; // You can add more columns as needed
bulk.WriteToServer(records);

You'll probably also want to wrap the code from the connection to the WriteToServer in a try/catch block.

---

2. Mixing Generics in C#

2022 Jun 07 c#

Recently I've come across a situation where I wanted to keep a list of validation errors for fields with different data types. Upon writing this article, however, I realized that I could have simplified the entire process by keeping the expected and actual values all as strings. Since I went through the trouble of going through this, though, I will share what I learned in case it might help in a different situation.

First, we have to create a validation error type. In order to be able to create a list of them, though, you will need to create an interface so that they don't all need to have the same data type.

public interface IValidationError
{
   string FieldName { get; set; }
   Type Type { get; }
   object ExpectedValue { get; set; }
   object ActualValue { get; set; }
}

public class ValidationError<T> : IValidationError
{
   public string FieldName { get; set; } = string.Empty;
   Type IValidationError.Type => typeof(T);
   public T ExpectedValue { get; set; }
   public T ActualValue { get; set; }
   object IValidationError.ExpectedValue 
   { 
      get => ExpectedValue.ToString(); 
      set => ExpectedValue = (T)value; 
   }
   object IValidationError.ActualValue 
   { 
      get => ActualValue.ToString(); 
      set => ActualValue = (T)value; 
   }
}

Now you should be able to use the new class to create a list like so:

List<IValidationError> validationErrors = new();

validationErrors.Add(new ValidationError<string>()
{
      FieldName = "Reference",
      ExpectedValue = "A26",
      ActualValue = "A33"
});

validationErrors.Add(new ValidationError<decimal>()
{
      FieldName = "Cost",
      ExpectedValue = 9.99m,
      ActualValue = 10.99m
});

To get the values and use them:

string errorMessageFormat = "{0} was supposed to be {1} but is instead {2}. ";
string errorMessage = "";

if (validationErrors.Count > 0)
{
      foreach (var error in validationErrors)
      {
         errorMessage += string.Format(errorMessageFormat, error.FieldName, error.ExpectedValue, error.ActualValue);
      }

      Console.WriteLine(errorMessage.Trim());
}

Here's the whole example:

using System;

namespace MixingGenerics
{
    internal class Program
    {
        static void Main()
        {
            List<IValidationError> validationErrors = new();

            validationErrors.Add(new ValidationError<string>()
            {
                FieldName = "Reference",
                ExpectedValue = "A26",
                ActualValue = "A33"
            });

            validationErrors.Add(new ValidationError<decimal>()
            {
                FieldName = "Cost",
                ExpectedValue = 9.99m,
                ActualValue = 10.99m
            });

            string errorMessageFormat = "{0} was supposed to be {1} but is instead {2}. ";
            string errorMessage = "";

            if (validationErrors.Count > 0)
            {
                foreach (var error in validationErrors)
                {
                    errorMessage += string.Format(errorMessageFormat, error.FieldName, error.ExpectedValue, error.ActualValue);
                }

                Console.WriteLine(errorMessage.Trim());
            }
        }
    }

    public interface IValidationError
    {
        string FieldName { get; set; }
        Type Type { get; }
        object ExpectedValue { get; set; }
        object ActualValue { get; set; }
    }

    public class ValidationError<T> : IValidationError
    {
        public string FieldName { get; set; } = string.Empty;
        Type IValidationError.Type => typeof(T);
        public T ExpectedValue { get; set; }
        public T ActualValue { get; set; }
        object IValidationError.ExpectedValue 
        { 
           get => ExpectedValue.ToString(); 
           set => ExpectedValue = (T)value; 
        }
        object IValidationError.ActualValue 
        { 
           get => ActualValue.ToString(); 
           set => ActualValue = (T)value; 
        }
    }
}

---

3. Simple Regex Example in C#

2022 Mar 08 c#

Sometimes you just need a quick example of something you know how to do, but you can't remember the syntax for since you only need to use it from time to time.

using System;
using System.Text.RegularExpressions;

class Program 
{
   static void Main()
   {
      Regex rx = new(@":(\d+)-");

      string text = "File:24524-abc.txt";

      Match match = rx.Match(text);

      if (match.Success)
      {
         Console.WriteLine(match.Groups[1].Value);
      }
   }
}

---

4. 405 Method Not Allowed on PUT Requests in IIS

2022 Mar 03 c# .net core

If you publish your .NET Core API to IIS and you are having trouble with PUT requests, but your GET and POST ones work just fine, you might be having trouble with the WebDAV module. One way to get around this issue is update your web.config to remove the WebDAV packages like below.

<system.webServer>
   <modules>
      <remove name="WebDAVModule" />
   </modules>
   <handlers>
      <remove name="WebDAV" />
   </handlers>
</system.webServer>

There are also a few more methods to remove the WebDAV modules. You can take a look at this Stack Overflow article.

---

5. Using C# 9.0 with Any Project on Visual Studio 2019

2022 Feb 01 c#

Even though Visual Studio 2019 comes with C# 9.0, not all new projects will start with C# 9.0. For example, if you start a .NET Core 3.1 project, it will default to C# 8.0. However, it is possible to change it to use C# 9.0 having to install anything else.

Framework	Version	C# Default Version
.NET	5.x	9.0
.NET Core	3.x	8.0
.NET Core	2.x	7.3
.NET Framework	any	7.3

Reference: MS Docs

If you start a .NET 5.0 project, you don't need to change anything, otherwise, if you want to change the version, follow these steps:

1. Right-click on your Project in the Solution Explorer, click Unload Project.
2. In the .csproj file that shows up, add this line to the <PropertyGroup>:

<LangVersion>9</LangVersion>

3. Right-click on your Project in the Solution and click Reload Project

---

6. Simple Pattern Matching with a Switch Expression in C#

2022 Jan 06 c#

Recently having learned about pattern matching in C#, I was thinking of ways to incorporate it into my code. I came across a function that seemed like a good candidate to try it on. The function takes in a string and then returns a "cleaned" version of it so that we don’t have all sorts of variants.

public static string GetCleanedName(string fruit)
{
    if (fruit.Contains("Apple"))
    {
        return "Apple";
    }
 
    if (fruit.Contains("Pear"))
    {
        return "Pear";
    }
 
    if (fruit == "Peech")
    {
        return "Peach";
    }
 
    return fruit;
}

Changing it to a switch expression made it much more concise.

public static string GetCleanedName(string fruit)
{
    fruit = fruit switch
    {
        string s when s.Contains("Apple") => "Apple",
        string s when s.Contains("Pear") => "Pear",
        "Peech" => "Peach",
        _ => fruit
    };
 
    return fruit;
}

To make it even more concise, you can turn the function into an expression statement.

public static string GetCleanedName(string fruit) =>
    fruit switch
    {
        string s when s.Contains("Apple") => "Apple",
        string s when s.Contains("Pear") => "Pear",
        "Peech" => "Peach",
        _ => fruit
    };

In action, you would have something like this:

using System;
using System.Collections.Generic;

class Program {
  public static void Main (string[] args) {
    
    List<string> fruits = new List<string> {"Green Apple", "Asian Pear", "Peech", "Kiwi"};

    foreach(var fruit in fruits)
    {
      Console.WriteLine($"'{fruit}' becomes '{GetCleanedName(fruit)}'.");
    }
  }

  public static string GetCleanedName(string fruit) =>
  fruit switch
    {
      string s when s.Contains("Apple") => "Apple",
      string s when s.Contains("Pear") => "Pear",
      "Peech" => "Peach",
      _ => fruit
    };

  public static string GetCleanedNameOldVersion(string fruit)
  {
    if (fruit.Contains("Apple"))
    {
      return "Apple";
    }

    if (fruit.Contains("Pear"))
    {
      return "Pear";
    }

    if (fruit == "Peech")
    {
      return "Peach";
    }

    return fruit;
  }
}

---

7. Getting All Fields and Values from a SharePoint List with C# and CSOM

2021 Sep 20 c#

Go straight to the source code here.

First, you will need to use NuGet to download the CSOM package. Once installed, you will also want to add this using statement to be able to use the CSOM.



using Microsoft.SharePoint.Client;

Then create a ClientContext object with the address of the SharePoint site you are trying to access.

ClientContext context = new ClientContext("https:--thewebsite.com-sites-TheSharePointSite");

Next, we get the specific list from the context by its title on the SharePoint site.

List list = context.Web.Lists.GetByTitle("The List I Want");

Now we create a CamlQuery to filter what we want to get back.

CamlQuery query = new CamlQuery
{
    ViewXml = @"<View>
                    <RowLimit>10<-RowLimit>
                <-View>"
};

You can also include a <Query> statement to filter it even further like in the example below.

CamlQuery query = new CamlQuery
{
    ViewXml = @"<View>
                    <Query>
                        <Where>
                            <FieldRef Name='ID'->
                            <Value Type='Integer'>1<-Value>
                        <-Where>
                    <-Query>
                    <RowLimit>10<-RowLimit>
                <-View>"
};

For more info on the using CamlQuery, check out the Microsoft docs here:

Query Schema in CAML

Next, we will create a variable to hold all of the list items.

ListItemCollection items = list.GetItems(query);

Now we have to load the items by executing the CAML query.

context.Load(items);
context.ExecuteQuery();

The field names in the front-end of the SharePoint list don’t always coincide with the internal names through the back, so I always iterate through a few list items to see what the field names are.

foreach (ListItem item in items)
{
    foreach (KeyValuePair<string, object> kvp in item.FieldValues)
    {
        Console.WriteLine($"[{kvp.Key}]: {kvp.Value}");
    }
}

If you already know the names and internal names, you can go ahead and show the data however you want. As you will see, they don’t always return text fields, so you might need to debug to see what kind of value you are getting back and convert it accordingly.

foreach (ListItem item in items)
{
    Console.WriteLine($"ID: {item["ID"}]}");
    Console.WriteLine($"Title: {item["Title"}]}");
    Console.WriteLine($"Description: {item["field2"}]}");
    Console.WriteLine($"Created By: {((FieldUserValue)item["Author"}).LookupValue]}");
    Console.WriteLine();
}

---

8. ORA-01722 invalid number in Parameterized Query

2020 May 15 c# oracle

If you are receiving the ORA-01722 (invalid number) error in an Oracle query even though you are using named parameters, you may want to check to make sure that BindByName has been set to true.

For example, the code below will fail because the parameters aren’t truly bound by name so the parameters should be added in the same order that they appear in the query unless you use the BindByName option.

string oradb = "your connection string";
 
var connection = new OracleConnection(oradb);
 
connection.Open();
 
string query = @"select *
                  from table
                  where numeric_field = :numeric_field
                  and character_field :character_field";
 
var command = new OracleCommand(query, connection);
command.CommandType = CommandType.Text;
 
command.Parameters.Add(new OracleParameter("character_field", "some value"));
command.Parameters.Add(new OracleParameter("numeric_field", 123));
 
/* If you uncomment the line below, the code will work even though the parameters aren't added in the same order
  as they appear in the query because they will be truly bound by name. */
// command.BindByName = true;
 
var dataAdapter = new OracleDataAdapter(command);
 
var dataTable = new DataTable();
 
try
{
  dataAdapter.Fill(dataTable);
}
catch (OracleException ex)
{
  Console.WriteLine($"Exception: {ex.Message}");
}
 
connection.Close();
connection.Dispose();

---

9. Inserting Multiple Records into Oracle with OleDB and Binding in C#

2019 Dec 13 c# oracle

While there is a way to do this with an Oracle driver, but in case you have highly restricted access to your PC and can’t look at or configure the LDAP settings, you can get by using OleDB.

First, be sure to include this statement up top:

using System.Data.OleDb;

In the example below, we are adding 10 new usernames to a table that are named BOT01 – BOT10. It’s a pretty basic example that you can edit to suit your needs.

string connectionString = "Provider=OraOLEDB.Oracle;Data Source=<your_data_source>;User ID=<user_id>;Password=<password>;";
string insertStatement = $@"INSERT INTO <your_table>
                            (username, effective_date)
                            VALUES
                            (:username, to_date('1-JAN-2019', 'DD-MON-YYYY')";
 
try
{
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        connection.Open();
        using (OleDbCommand command = new OleDbCommand(insertStatement, connection))
        {
            // For single inserts, you can just do this:
            // command.Parameters.Add(new OleDbParameters("username", "<your_username>"));
            // command.ExecuteNonQuery();
 
            // Otherwise for multiple inserts
            command.Parameters.Add(new OleDbParameters("username", OleDbType.VarChar));
            for (int i = 1; i <= 10; i++)
            {
                string botUsername = $"BOT{i.ToString("D2")}";
                Console.WriteLine($"Inserting {botUsername} record...");
                command.Parameters["username"].Value = botUsername;
                command.ExecuteNonQuery();
            }
        }
    }
}
catch (Exception ex)
{
    Console.WriteLine($"Exception: {ex.Message}");
}
Console.WriteLine("Done");
Console.ReadLine();

---

10. How to Add Microsoft.Office.Interop.Outlook.dll to Your C# Project

2019 Oct 07 c#

For this tutorial, I am working with Visual Studio 2017 and Outlook 2016, but it should be more or less the same if you have different versions. Your project must be .NET and not Core (at least as of this writing).

If you have Microsoft Office installed, then you should be able to have this DLL already installed by default unless during the install the .NET Programmability Support feature was unselected.

To add the reference to your project, in the Solution Explorer, right-click on References, then click "Add Reference...". When the Reference Manager window appears, click on the COM tab. In there, look for "Microsoft Outlook 16.0 Object Library". The number in yours might be different depending on the version of Outlook you have installed. Make sure you click on the checkbox to the left and then hit OK. From there you should be able to follow any tutorial that uses the Microsoft.Office.Interop.Outlook.dll to manipulate email with Outlook.

---

11. Buttons Extension for DataTables using MVC 5 with Bootstrap

2017 Mar 01 c# mvc

If you get errors saying that certain functions are undefined, you might be missing some scripts or have them in the wrong order.

using System.Web;
using System.Web.Optimization;

namespace YourNamespace
{
    public class BundleConfig
    {
        // For more information on bundling, visit http://go.microsoft.com/fwlink/?LinkId=301862
        public static void RegisterBundles(BundleCollection bundles)
        {
            bundles.Add(new ScriptBundle("~/bundles/lib").Include(
                        "~/Scripts/jquery-{version}.js",
                        "~/Scripts/bootstrap.js",
                        "~/Scripts/bootbox.js",
                        "~/Scripts/respond.js",
                        "~/Scripts/jquery-ui.js"));
  
            bundles.Add(new ScriptBundle("~/bundles/datatables").Include(
                        "~/Scripts/DataTables/jquery.dataTables.js",
                        "~/Scripts/DataTables/dataTables.bootstrap.js",
                        "~/Scripts/DataTables/dataTables.buttons.js",
                        "~/Scripts/DataTables/buttons.html5.js",
                        "~/Scripts/DataTables/buttons.bootstrap.js"));
  
            // Use the development version of Modernizr to develop with and learn from. Then, when you're
            // ready for production, use the build tool at http://modernizr.com to pick only the tests you need.
            bundles.Add(new ScriptBundle("~/bundles/modernizr").Include(
                        "~/Scripts/modernizr-*"));
  
            bundles.Add(new StyleBundle("~/Content/css").Include(
                      "~/Content/bootstrap.css",
                      "~/Content/site.css",
                      "~/Content/DataTables/css/dataTables.bootstrap.css",
                      "~/Content/DataTables/css/buttons.bootstrap.css",
                      "~/Content/jquery-ui.css"));
        }
    }
}

And to get the buttons to show up for your table:

var table = $("#table").DataTable({
   "dom": 'Bfrltip',
   "buttons": ['copy', 'csv']
});

---

See all tags.