Category Archives: Code snippets

SSIS – concatenate variables with SQL query

In previous post, SSIS – dynamic file name I was generating dynamic file name.
Later in my project I had combine it with dynamic file path, stored in variable.
For this scenario I will reuse previous project. All I have to do is add existing variable which stores file location to Parameter Mapping.

ssis01

Does not matter how many parameters you have, just remember to number them correctly (0, 1, 2…) because we are going to use parameter mapping (?
) in our SQL query

All we have to do is modify our query like below. Notice how @Path gets value. ? represents FileLocation variable.

DECLARE @Path Varchar (256)
SET @Path = ?

SELECT @Path + 'SomeFile_' +
CAST(DATEPART(yyyy,GetDate()) AS NVARCHAR(4)) +
CAST(RIGHT('0' + DATEPART(mm,GetDate()) ,2) AS NVARCHAR(2)) +
CAST(RIGHT('0' + DATEPART(dd,GetDate()) ,2) AS NVARCHAR(2)) +
'.xls' as SQLFileName

--or

@[User::FileDestination] + @[User::FileNamePrefix] 
+ (DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() )) + (DT_STR,4,1252)MONTH( DATEADD( "dd", -1, getdate() )) + (DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() )) + ".csv"

SSIS – dynamic file name (date as file name)

We will be using Execute SQL Task for that, quick and easy.
First we need a variable, say FileName with type String

ssis01

Properties of SQL Task:

ssis02

  • Connection: create SQL Connection to any database, does not matter which one
  • SQLSourceType: Direct Input – we will create simple inline query
  • SQLStatement: click on 3 dots (…) and paste query below:
    SELECT 'SomeFile_' +
    CAST(DATEPART(yyyy,GetDate()) AS NVARCHAR(4)) +
    CAST(RIGHT('0' + DATEPART(mm,GetDate()) ,2) AS NVARCHAR(2)) +
    CAST(RIGHT('0' + DATEPART(dd,GetDate()) ,2) AS NVARCHAR(2)) +
    '.xls' as SQLFileName
    

    File will be called SomeFile_date.xls and saved with SQLFileName alias which will be accessing in next step

  • ResultSet: Single Row
  • Switch to Parameter Mapping of SQL Task.

    We will be mapping to previously created FileName variable
    Type VARCHAR, Parameter 0

    ssis03

    Now we need to set our FileName variable value

    • Switch to Result Set
    • click Add
    • type SQLFileName (our previously created alias in SQL Statement)
    • select User::FileName viariable

    ssis04

    To test it, create Script Task

    And connect with our SQL Task:

    ssis05

    Map ReadOnlyVariable property with our FileName variable and click Edit Script

    ssis06

    After code editor shows up, find line saying

    // TODO: Add your code here

    and paste line below

    MessageBox.Show(Dts.Variables["User::FileName"].Value.ToString());

    ssis07

    Now you can simply close script editor.
    Task execution will pop up window with file name

    ssis08

Multithreading in .NET 4.0

.NET 4.5 has way more multhreading and asynch features than 4.0.
Unfortunately I was stuck with 4.0 and had to create simple multithreading solution for one of project modules.
It appeared it was really simple to use:

int param1 = 10;
var task = Task.Factory.StartNew(() => CallSomeMethod(param1));

That’s it!

Entity Framework – Attaching an entity of type failed because another entity of the same type already has the same primary key value

This happened to me while updating model from viewmodel and changing its State to Modified.
Before save, I wanted to check model property values existing in the database.
The solution is to use AsNoTracking() with your query. This way newly retrieved object won’t be tracked by the context. That means context will not recognize that the object has the same Id as a primary key value.

if (entity.CustomerID > 0)
{
	Customer existing =  _context.Set<Customer>().AsNoTracking().Where(c => c.CustomerID == entity.CustomerID).FirstOrDefault();
	entity.PasswordHash = existing.PasswordHash;
	entity.PasswordSalt = existing.PasswordSalt;
	entity.rowguid = existing.rowguid;
}

Entity Framework – Validation failed for one or more entities. See ‘EntityValidationErrors’ property for more details.

This is how Visual Studio tells you that something went wrong while updating your entity.
Simple solution would be to catch System.Data.Entity.Validation.DbEntityValidationException and iterate through EntityValidationErrors collection.

try
{
	return _context.SaveChanges();
}
catch (System.Data.Entity.Validation.DbEntityValidationException dbvEx)
{
    List<string> errors = new List<string>();
    foreach (var validationErrors in dbvEx.EntityValidationErrors)
    {
        foreach (var validationError in validationErrors.ValidationErrors)
        {
            errors.Add(string.Format("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage));
        }
    }
    return 0;
}

errors

Unity – The type Interface does not have an accessible constructor

Had a constructor accepting string parameter

public CustomerRepo(string dbContext)
{
	if (dbContext == "AWContext")
	{
		_context = new AWContext();
	}
	else
	{
		throw new ArgumentException("Invalid dbContext name");
	}
}

registered following instructions from MSDN

container.RegisterType<ICustomerRepo, CustomerRepo>(
    "test", 
    new InjectionConstructor("AWContext")
);

The “test” is just a name of current mapping. But it didn’t work until I completely removed it(!):

container.RegisterType<ICustomerRepo, CustomerRepo>(
    new InjectionConstructor("AWContext")
);

C# String was not recognized as a valid DateTime

Sometimes while testing someones code, I am getting such exception. Last thing what I want to do is to is fix it 🙂
For example:

// ...
var p = new List<Person>()
{
    new Person() { FirstName="John", LastName="Koenig",
	StartDate = DateTime.Parse("10/17/1975"), Rating=6 },
// ...

This date format, looks like US one, would not work if your current Culture is, say, Canadian.
Just override system setting by adding following line before the code. (If you have different time
format, adjust CultureInfo setting accordingly of course)

// ...
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
// ...

ASP.NET C# Send email in background thread

System.Threading.Thread thread = new System.Threading.Thread(delegate()
{
    SMTPSend(message);
});

thread.IsBackground = true;
thread.Start();

private static void SMTPSend(MailMessage message)
{
    try
        {
            SmtpClient smtp = new SmtpClient();
            ...

On the presentation layer you do not need any AJAX or JavaScript calling code from the backend
Just plain ASP.NET button

<asp:Button ID="btnSend" runat="server" Text="Send Email" onclick="btnSend_Click" />

Code behind

protected void btnSend_Click(object sender, EventArgs e)
{
    try
    {
        Notifications.Send(email);
    }
    catch (Exception ex)
    {
        litResult.Text = ex.ToString();
    }
}