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)

Leave a reply

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

Leave a reply

.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!

ASP.NET Localization – switch content based on language ID

Leave a reply

//...
if (System.Globalization.CultureInfo.CurrentCulture.LCID == 3084)  // French
{
    // French content/logic here
}
else
{
    // Default content/logic here
}
//...

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

Leave a reply

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.

Leave a reply

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

Leave a reply

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

Leave a reply

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");
// ...

MVC 4 WCF Add Service Reference Generates Blank Proxy

Leave a reply

Solution:

Right click on Service Reference
Select Configure Service Reference
Select Reuse types in specified referenced assemblies
Just select everything except “Newtonsoft.json”

wcfblankproxy

Source: Stack Overlow

ASP.NET C# Send email in background thread

Leave a reply

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();
    }
}

Properties of SQL Task:

Switch to Parameter Mapping of SQL Task.

Now we need to set our FileName variable value

To test it, create Script Task

Now we need to set our `FileName` variable value