Quick T-SQL tip: Insert image into db using SQL statement

The goal of this quick post is to show how to insret image into database cell using sql statement. Here is the table that we insert the row into.

The cell is type of image, but the actual data stored inside of it is binary. Here is the insert statement that you can use to insert row into that table:

INSERT INTO MsMailTemplateImages(ContentID, Image, MsMailTemplateID) 
SELECT 'greentree' as ContentID, BulkColumn, 1 as MsMailTemplateID FROM 
OPENROWSET(BULK N'C:\Downloads\environmental.gif', SINGLE_BLOB) as DATA

Visual studio QuickReplace using Regular Exporession [Regex]

I don't have to persuade anybody that using regex saves a lot of time. Recently I was about to add 20 new parameters to the stored procedure updating record. The reason for that was the change in table definiton. I had to make following steps: 1) add new parameters to sp, 2) add this parameters to update statement 3) add this parameters to insert statement I could use copy and paste, because all three steps needed only some changes withing string with new columns. 1) The sample from input sql I had to write for sp Input params

@CategoryL3 nvarchar(50), @CategoryL4 nvarchar(50),

2) The sample from update statement

[CategoryL3]=@CategoryL3, [CategoryL4]=@CategoryL4,

3) The sample from insert statement

[CategoryL3], [CategoryL4] 

and then:

@CategoryL3, @CategoryL4 

Here is regex to find things from first sample:
[@]{.[^@ ]*} .[^@ ]*,
- in {} brackets we get the expression we want to extract from what has been found.
I'm looking for:
- @ at sing
- ".[^@ ]* "- all the signs (.*) until space (which is after star) that don't contain @ sign
- then there is space and anything thats between this space and comma, but again without @ sign (to avoid greedy results) .[^@ ]*


then in replace with text box I can put \1 which result with setting it to the values I enclosed with curly brackets.

Adding new parameter to WebReport (asp.net 2.0 sql server 2005)

The situation is as follows: we have existing WebReport which users stored procedure for getting data. We want to add new parameter. 1) Alter stored procedure to expect new paramter 2) In the layout tab in report designer go to menu Report => Report Parameters and add new parameter. Img. 1: CustomerIds - new parameter

Initially I thought it's enough, and you can imagine how supprised I was when trying to preview report I got an error: procedure expects parameter which was not supplied. I spend more than half an hour to figure out what caused my problem. There is third step you have to make when adding new param. 3) On dataset properties Parameter tab you have to enter the input parameter of the sp for 'name' and select report parameter under value. Img. 2: 3rd step.

That's it.

dropdownlist has a SelectedValue which is invalid because it does not exist in the list of items

Enviroment: You've got a FormView or some other data control which displays single item and allows edit it. In edit mode there's a dropdown list with available names that user can select value from. Then when user wants to edit a person the underlaying data for selecting name has changed and has no more an item with value for edited person. (This can happen when for example someone deletes a name record directly in the db) That situation will cause "dropdownlist has a SelectedValue which is invalid because it does not exist in the list of items" error being thrown, because we've binded dropdownList.SelectedValue to an item which isn't in db any more. The workoround is to delete SelectedValue binding in aspx code and and handle unexpected situation in FormView_PreRender event. Here's a code for that:

protected void FormView1_PreRender(object sender, EventArgs e)
    {
        if (FormView1.CurrentMode == FormViewMode.Edit)
        {
            DataRowView rowView = (DataRowView)(FormView1.DataItem);
            DropDownList ddlNames = FormView1.FindControl("ddlNames") as DropDownList;
            bool isItemStillThere = ddlNames.Items.FindByValue(rowView["NameID"].ToString()) != null;
            if ((rowView != null) && isItemStillThere)
            {
                ddlNames.SelectedValue = rowView["NameID"].ToString();
            }
        }
    }

The aspx code:

Test website:

Restoring Database in SQL Server 2005

This is quick tip explaining how to restore database in SQL Server 2005. The new database had different schema than the one I wanted to restore data to.
This is the sql Generated by Sql management studio:

RESTORE DATABASE [DatabaseName] 
FROM  DISK = N'C:\temp\db\DBBackup.bak' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

Unfortunately it running it couses an error: Error 3154: The backup set holds a backup of a database other than the existing database.
All you need to do is to add parameter REPLACE. this is how new query looks like:

RESTORE DATABASE [DatabaseName] 
FROM  DISK = N'C:\temp\db\DBBackup.bak' 
WITH  REPLACE, FILE = 1,  NOUNLOAD,  STATS = 10
GO

EntityFramework and many to many relation

Today I'm going to share my thoughts about many to many relationship, when working with EntityFramework. Let's assume that we have datatables like shown below:
As we can see in the picture we have many customers, that can have many projects, but when we make this association we would like to know save additional information about AmountSpent and PerComplete.
This kind of mapping in entity framework will result in 3 tables in entity datamodel, opposite to situattion when association table contains only references ids - then we have only two objects in entity datamodel. What's important you have to make primary key in mapping table as combination of referenced identifiers.