November 2021 – Page 4 – My Point of View

Dataset is empty, no tables in Dataset

This is how we can test if dataset is empty;

if(ds != null)
if(ds.Tables.Count > 0 )
if(ds.Tables[0].Rows.Count > 0)

We can loop through all tables in a method like this;

bool IsEmpty(DataSet dataSet)
{
    foreach(DataTable table in dataSet.Tables)
        if (table.Rows.Count != 0) return false;

    return true;
}

Since a DataTable could contain deleted rows RowState = Deleted, depending on what you want to achive, it could be a good idea to check the DefaultView instead (which does not contain deleted rows).

bool IsEmpty(DataSet dataSet)
{
    return !dataSet.Tables.Cast<DataTable>().Any(x => x.DefaultView.Count > 0);
}

Resource

https://stackoverflow.com/questions/2976473/how-to-test-if-a-dataset-is-empty

Query DataTable using LINQ in C#

Dynamic type and Expando Object class

The dynamic type indicates that use of the variable and references to its members bypass compile-time type checking. Instead, these operations are resolved at run time. The dynamic type simplifies access to COM APIs such as the Office Automation APIs, to dynamic APIs such as IronPython libraries, and to the HTML Document Object Model (DOM).

Here is an example;

var list = new List<dynamic>();
dynamic row = new ExpandoObject();
row.AccountNumber = "XYZ-331";
row.FYQ = "Q3 FY 2021";
row.DateAdded = "2021-07-28 19:38:00.000";
row.RowStatus = "processed";
list.Add(row);

Here is the output;

list.ForEach(x =>
{
   Console.WriteLine($"AccountNumber = { x.AccountNumber} \nFYQ =  {x.FYQ}\nDateAdded = {x.DateAdded}\nRowStatus = {row.RowStatus}");
});

Resources

https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/builtin-types/reference-types

https://docs.microsoft.com/en-us/dotnet/api/system.dynamic.expandoobject?view=net-5.0

Creating dynamic arrays and lists using Dynamic and ExpandoObject in C#

https://www.codegrepper.com/code-examples/csharp/add+static+data+to+list+in+c%23

Appending two string builders

The most performant way of appending two string builders in .NET Framework 4, use this;

frontStringBuilder.Append(backStringBuilder);

For .NET Framework 2.0/3.5, use this;

frontStringBuilder.Append(backStringBuilder.ToString(0, backStringBuilder.Length));

This wouldn’t hurt performance in .NET FW 4.0.

Resources

https://stackoverflow.com/questions/6454392/how-to-append-two-stringbuilders

The transaction log for database ‘SampleDb’ is full due to ‘LOG_BACKUP’.

The ETL process worked fine for the last 3 days. Today it started failing. The reason, Log_Backup. The database was in Full recovery mode and transaction log got full.

As a matter of fact, the staging database has to be in Simple recovery mode.

First to view disk space occupied by database, run this;

sp_helpdb SampleDb

To change database recovery model, run this;

USE SampleDb
GO
SELECT * FROM sys.database_files

--Truncate the log by chaning the database recovery model to SIMPLE
ALTER DATABASE SampleDb
SET RECOVERY SIMPLE
GO

--Shrink the truncated log file to 1MB
DBCC SHRINKFILE (SampleDb, 1)
GO

--Reset the database recovery model, if required
/*
ALTER DATABASE SampleDb
SET RECOVERY FULL
GO
*/

If DBCC SHRINKFILE takes longer, we can use following command to see the progress;

select * from sys.dm_exec_requests

There is a Percentage_Completed and Estimated_Completion_time columns. These columns are not populate for every operation, but they are for shrink. You can find the row for your connection during the shrinking, and inspect the values to get an estimate of completion time. If the values are not changing, you’ll need to investigate whether the process is blocking something.

Upon checking the database’s Log file growth setting, the log file was limited growth of 1GB. So what happened is when the job ran and it asked SQL server to allocate more log space, but the growth limit of the log declined caused the job to failed. I modified the log growth and set it to grow by 50MB and Unlimited Growth and the error went away.

Resource

https://stackoverflow.com/questions/21228688/the-transaction-log-for-database-is-full-due-to-log-backup-in-a-shared-host/21235001