Bulk Update in C#

Bulk Update:

Step 1: put the data which you want to update and primary key in a list.

Step 2: pass this list and ConnectionString to BulkUpdate Method As shown below

Example:

         //Method for Bulk Update the Data
    public static void BulkUpdateData<T>(List<T> list, string connetionString)
    {

        DataTable dt = new DataTable("MyTable");
        dt = ConvertToDataTable(list);

        using (SqlConnection conn = new SqlConnection(connetionString))
        {
            using (SqlCommand command = new SqlCommand("CREATE TABLE 
                  #TmpTable([PrimaryKey],[ColumnToUpdate])", conn))
            {
                try
                {
                    conn.Open();
                    command.ExecuteNonQuery();

                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                    {
                        bulkcopy.BulkCopyTimeout = 6600;
                        bulkcopy.DestinationTableName = "#TmpTable";
                        bulkcopy.WriteToServer(dt);
                        bulkcopy.Close();
                    }


                    command.CommandTimeout = 3000;
                    command.CommandText = "UPDATE P SET P.[ColumnToUpdate]= T.[ColumnToUpdate] FROM [TableName Where you want to update ] AS P INNER JOIN #TmpTable AS T ON P.[PrimaryKey] = T.[PrimaryKey] ;DROP TABLE #TmpTable;";
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    // Handle exception properly
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }

Step 3: put The ConvertToDataTable Method as shown Below.

Example:

    public static DataTable ConvertToDataTable<T>(IList<T> data)
    {
        PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
        DataTable table = new DataTable();
        foreach (PropertyDescriptor prop in properties)
            table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
        foreach (T item in data)
        {
            DataRow row = table.NewRow();
            foreach (PropertyDescriptor prop in properties)
                row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
            table.Rows.Add(row);
        }
        return table;
    }

Notes: WhereEver SquareBracket[] is there, put your own value.

Complete answer, disclaimer: arrow code; this is mine built from research; Published in SqlRapper. It uses custom attributes over properties to determine whether a key is primary. Yes, super complicated. Yes super reusable. Yes, needs to be refactored. Yes, it is a nuget package. No, the documentation isn't great on github, but it exists. Will it work for everything? Probably not. Will it work for simple stuff? Oh yeah.

How easy is it to use after setup?

public class Log
{
    [PrimaryKey]
    public int? LogId { get; set; }
    public int ApplicationId { get; set; }
    [DefaultKey]
    public DateTime? Date { get; set; }
    public string Message { get; set; }
}


var logs = new List<Log>() { log1, log2 };
success = db.BulkUpdateData(logs);

Here's how it works:

public class PrimaryKeyAttribute : Attribute
{
}

    private static bool IsPrimaryKey(object[] attributes)
    {
        bool skip = false;
        foreach (var attr in attributes)
        {
            if (attr.GetType() == typeof(PrimaryKeyAttribute))
            {
                skip = true;
            }
        }

        return skip;
    }

    private string GetSqlDataType(Type type, bool isPrimary = false)
    {
        var sqlType = new StringBuilder();
        var isNullable = false;
        if (Nullable.GetUnderlyingType(type) != null)
        {
            isNullable = true;
            type = Nullable.GetUnderlyingType(type);
        }
        switch (Type.GetTypeCode(type))
        {
            case TypeCode.String:
                isNullable = true;
                sqlType.Append("nvarchar(MAX)");
                break;
            case TypeCode.Int32:
            case TypeCode.Int64:
            case TypeCode.Int16:
                sqlType.Append("int");
                break;
            case TypeCode.Boolean:
                sqlType.Append("bit");
                break;
            case TypeCode.DateTime:
                sqlType.Append("datetime");
                break;
            case TypeCode.Decimal:
            case TypeCode.Double:
                sqlType.Append("decimal");
                break;
        }
        if (!isNullable || isPrimary)
        {
            sqlType.Append(" NOT NULL");
        }
        return sqlType.ToString();
    }

    /// <summary>
    /// SqlBulkCopy is allegedly protected from Sql Injection.
    /// Updates a list of simple sql objects that mock tables.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="rows">A list of rows to insert</param>
    /// <param name="tableName">a Table name if your class isn't your table name minus s.</param>
    /// <returns>bool success</returns>
    public bool BulkUpdateData<T>(List<T> rows, string tableName = null)
    {
        var template = rows.FirstOrDefault();
        string tn = tableName ?? template.GetType().Name + "s";
        int updated = 0;
        using (SqlConnection con = new SqlConnection(ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("", con))
            {
                using (SqlBulkCopy sbc = new SqlBulkCopy(con))
                {
                    var dt = new DataTable();
                    var columns = template.GetType().GetProperties();;
                    var colNames = new List<string>();
                    string keyName = "";
                    var setStatement = new StringBuilder();
                    int rowNum = 0;
                    foreach (var row in rows)
                    {
                        dt.Rows.Add();
                        int colNum = 0;
                        foreach (var col in columns)
                        {
                            var attributes = row.GetType().GetProperty(col.Name).GetCustomAttributes(false);
                            bool isPrimary = IsPrimaryKey(attributes);
                            var value = row.GetType().GetProperty(col.Name).GetValue(row);

                            if (rowNum == 0)
                            {
                                colNames.Add($"{col.Name} {GetSqlDataType(col.PropertyType, isPrimary)}");
                                dt.Columns.Add(new DataColumn(col.Name, Nullable.GetUnderlyingType(col.PropertyType) ?? col.PropertyType));
                                if (!isPrimary)
                                {
                                    setStatement.Append($" ME.{col.Name} = T.{col.Name},");
                                }

                            }
                            if (isPrimary)
                            {
                                keyName = col.Name;
                                if (value == null)
                                {
                                    throw new Exception("Trying to update a row whose primary key is null; use insert instead.");
                                }
                            }
                            dt.Rows[rowNum][colNum] = value ?? DBNull.Value;
                            colNum++;
                        }
                        rowNum++;
                    }
                    setStatement.Length--;
                    try
                    {
                        con.Open();

                        command.CommandText = $"CREATE TABLE [dbo].[#TmpTable]({String.Join(",", colNames)})";
                        //command.CommandTimeout = CmdTimeOut;
                        command.ExecuteNonQuery();

                        sbc.DestinationTableName = "[dbo].[#TmpTable]";
                        sbc.BulkCopyTimeout = CmdTimeOut * 3;
                        sbc.WriteToServer(dt);
                        sbc.Close();

                        command.CommandTimeout = CmdTimeOut * 3;
                        command.CommandText = $"UPDATE ME SET {setStatement} FROM {tn} as ME INNER JOIN #TmpTable AS T on ME.{keyName} = T.{keyName}; DROP TABLE #TmpTable;";
                        updated = command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        if (con.State != ConnectionState.Closed)
                        {
                            sbc.Close();
                            con.Close();
                        }
                        //well logging to sql might not work... we could try... but no.
                        //So Lets write to a local file.
                        _logger.Log($"Failed to Bulk Update to Sql:  {rows.ToCSV()}", ex);
                        throw ex;
                    }
                }
            }
        }
        return (updated > 0) ? true : false;
    }