Entity Framework Code First Seeding with Related Data

A quick tip I stumbled upon while working on an MVC application using Entity Framework Code First.

In order to Seed Related Data when using Entity Framework Code First, it’s important to give items temporary ID’s before calling the seed method. Otherwise, when the database is seeded you will end up with Conflicted Inserts

Unable to open Database .SQL Files in Visual Studio 2012 Update 1 and onwards

This morning I attempted to open a .SQL file in my Visual Studio 2012 Update 2 installation, and was faced with the following Message Box;

Clicking the “Learn more…” link took me to a “Page Not Found” page, which wasn’t very handy.

A quick search on StackOverflow and I found;

http://stackoverflow.com/questions/15798422/what-about-sql-server-data-tools-for-vs2012-being-incompatible-with-sql-server-2

With a helpful answer by JorgenH directing me to update the SQL Server Data Tools found at this page;

http://msdn.microsoft.com/en-us/jj650015

I downloaded and installed the SQL Server Data Tools, shown at Step 2, from;

http://go.microsoft.com/fwlink/?LinkID=274984

Hey presto, problem sorted!

Exporting MS SQL Table Data using PHP

I regularly export table data from MySQL using PHP, however a question on StackOverflow.com came up where the user wanted to export data from MS SQL rather than MySQL.

Handily, PHP has support for many of the same functions for MS SQL as it does for MySQL.

So with a bit of reworking I was able to come up with the following script;

<?php

    $servername = '.\SQLSERVER';

    $username = 'databaseuser';

    $password = 'database';

    $database = 'CriticalPath';

    $table = 'Users';

    if (!function_exists('mssql_fetch_row'))

    {

        $output .= "MSSQL functions are not available.<br />\n";

        exit;

    }

    // Connect database

    if(!$dbconnect = mssql_connect($servername, $username, $password))

    {

        $output .= "Connection failed to the host 'localhost'.";

        exit;

    } // if

    if (!mssql_select_db($database))

    {

        $output .= "Cannot connect to database '$database'";

        exit;

    } // if

    $result=mssql_query("select * from $table");

    $out = '';

    $columns = mssql_num_fields($result);    

    for ($i = 0; $i < $columns; ++$i) {

        // Fetch the field information

        $field = mssql_fetch_field($result, $i);

        $out .= '"'.$field->name.'",';

    }

    $out .= "\r";

    // Add all values in the table to $out.

    while ($l = mssql_fetch_array($result)) 

    {

       for ($i = 0; $i < $columns; $i++) 

       {

          $out .='"'.$l["$i"].'",';

       }

       $out .= "\r";

    }

    // Open file export.csv.

    $f = fopen ('export.csv','w');

    // Put all values from $out to export.csv.

    fputs($f, $out);

    fclose($f);

    header('Content-type: application/csv');

    header('Content-Disposition: attachment; filename="export.csv"');

    readfile('export.csv');

    mssql_free_result($result);

?>

SQL Server 2008 - Retrieving a Users’ Privileges for all tables in a databse

I recently answered a question on StackOverflow.com by a user who needed to retrieve a list of a given users Privileges for each Table in a given Database.

After some trial an error I ended up with the following T-SQL Script;

declare @Proc nvarchar(50)

declare @RowCnt int

declare @MaxRows int

declare @ExecSql nvarchar(255)

select @RowCnt = 1

select @Proc = 'SELECT * from fn_my_permissions'

declare @Import table (rownum int IDENTITY (1, 1) Primary key NOT NULL , TableName varchar(50))

insert into @Import (TableName) select name from sys.Tables

declare @Output table (entity_name varchar(50), subentity_name varchar(50), permission_name varchar(50))

select @MaxRows=count(*) from @Import

while @RowCnt <= @MaxRows

begin

    select @ExecSql = @Proc + '(N''' + TableName + ''', N''OBJECT'') where subentity_name = ''''' from @Import where rownum = @RowCnt 

    insert into @Output exec sp_executesql @ExecSql

    Select @RowCnt = @RowCnt + 1

end

select * from @Output

This will give the Permissions for the current user. If you want to find the permissions for a given user then use the following script;

EXECUTE AS LOGIN = N'username'

GO

declare @Proc nvarchar(50)

declare @RowCnt int

declare @MaxRows int

declare @ExecSql nvarchar(255)

select @RowCnt = 1

select @Proc = 'SELECT * from fn_my_permissions'

declare @Import table (rownum int IDENTITY (1, 1) Primary key NOT NULL , TableName varchar(50))

insert into @Import (TableName) select name from sys.Tables

declare @Output table (entity_name varchar(50), subentity_name varchar(50), permission_name varchar(50))

select @MaxRows=count(*) from @Import

while @RowCnt <= @MaxRows

begin

    select @ExecSql = @Proc + '(N''' + TableName + ''', N''OBJECT'') where subentity_name = ''''' from @Import where rownum = @RowCnt 

    insert into @Output exec sp_executesql @ExecSql

    Select @RowCnt = @RowCnt + 1

end

select * from @Output

GO

REVERT

GO

These scripts are based on;

fn_my_permissions procedure: 

This returns a list of permissions for the current user, for the given table in a database. Here we are executing the following code for each table in the database;

SELECT * from fn_my_permissions(N'tablename', N'OBJECT') where subentity_name = ''

More information can be found at http://msdn.microsoft.com/en-us/library/ms176097.aspx and http://books.google.co.uk/books?id=5_AEiJXbyiEC&pg=PA452&lpg=PA452&dq=tsql+HAS_perms_by_name+for+each+table&source=bl&ots=tSArrzcfa4&sig=HmPsF9vEFQC4g2hC8lWvCmJ8qao&hl=en&sa=X&ei=Nrr5UKvkCKLB0gXhtYD4Ag&ved=0CEEQ6AEwAg#v=onepage&q=tsql%20HAS_perms_by_name%20for%20each%20table&f=false and http://stackoverflow.com/a/497368/1305169

The sys.tables collection: 

This contains a list of tables for the given database. More information can be found at http://blog.sqlauthority.com/2007/06/26/sql-server-2005-list-all-tables-of-database/

A Simple Loop:

In order to loop through all of the tables, I modified a handy script I found here; http://weblogs.aspnet05.orcsweb.com/jgalloway/archive/2006/04/12/442618.aspx

Obtaining the Maximum value in a list or Collection using Linq

In order to retrieve the highest value of a column in a list of collection, Linq offers a handy method to achieve this;

Dim maxUnitsInStock = Aggregate prod In db.Products _

                      Into Max(prod.UnitsInStock)

Simple!

Using Reflection to retrieve Entity Framework Data using a Column Name String

If you ever need to retrieve data from EF using the name of a column, rather than the built in Intellisense, then the following vb.net code snippet may help.

GetType(TypeOfEntity).GetProperty(NameOfColumn).GetValue(ActualEntity, Nothing)

• TypeOfEntity is the name of the Entity from which you wish to retrieve data.
  
• NameOfColumn is obviously the Name of the Column from which you wish to retrieve data.
  
• ActualEntity is a reference to the Entity itself, from which the data is to be retrieved.

The above can help if you need to retrieve data based on a column name which is defined by another function. Such as in the following example;

You have a table called Users with columns called Screen1Edit and Screen2Edit.

You can then retrieve the values in Screen1Edit or Screen2Edit depending upon which you require, supplied in a variable called strScreenName;

GetType(Users).GetProperty(strScreenName).GetValue(Users, Nothing)

Obviously you must be careful to supply the exact name of the column into the Column Name!

Note: For c# replace GetType with TypeOf.

MVVM: Binding a Linq Query result to a DataGrids’ ItemSource doesn’t update the UI

While creating Desktop Applications using WPF, I always employ the MVVM model if I can. This model is not only great at developing testable n-tier applications, but of course is also highly recommended by Microsoft.

However, as with most software development, there are nearly always specific methods to employ for certain processes and functions.

One of these is while Binding Linq Queries, through a bound property in your ViewModel.

Linq queries as standard return an IEnumerable type. This is fine for the most part, and fits nicely with MVVM, allowing Where statements etc.

However, if you then bind this IEnumerable or List type to a DataGrid, through a property in your ViewModel, and try adding and removing items using the .Add and .Remove extension methods, it becomes apparent that the UI isn’t updating.

This is because the Dot Net Framework has performed certain optimisations, which unfortunately cause it to miss the Add and Remove changes when dealing with IEnumerable and List type objects.

One way around this is to completely break the MVVM model and call the Items.Refresh method on the UI DataGrid. However, this is very very bad practise, and circumnavigates the entire purpose for using MVVM in the first place.

The correct solution is to replace the IEnumerable(of T) or List(of T) with an ObservableCollection(of T).

The Observable collection will automatically notify the UI through the ViewModel, whenever an Add or Remove is called on the collection.

Do remember to import the System.Collections.ObjectModel namespace of course!

SQL Server - Formatting Numbers as Strings with Leading Zero’s

Sometimes it’s necessary to format numbers as Strings with leading zero’s.

One such example is when we sort a list in Excel, Excel will sort the numbers as 0,1,10,2,20,3,30,…..

So, to get round this, we simply append a leading 0 to the number.

This is achieved in T-SQL by using the RIGHT instruction with following syntax.

RIGHT('00'+ CONVERT(VARCHAR,YourNumber),2)

The first Parameter - 00, is the default format for your formatted number, and should contain the number of zero’s corresponding to the total length of your formatted string.

The second parameter -‘YourNumber’, is the name of the field you wish to convert.

The third parameter - 2, is the total length of your formatted field.

Retrieving data from a Stored Procedure (with parameters) in Microsoft Excel 2007 / 2010

I have a method here which allows you to add Parameters to the Stored Procedure from Excel.

There are quite a few steps, but I’ve broken it down quite alot.

The process is actually really easy once you know how... as with anything.

1. Open Excel
2. Select the Data Tab (If you’re using Excel 2007 onwards!)
3. Select the “From Other Sources” drop down box
4. Select “From SQL Server”
5. It will open up the following box
   
   
   
6. Enter “.\SQLExpress” (That’s Dot Backslash SQLExpress and it’s without the quotes) in the “Server name:” box
7. Depending on your Server Configuration, either leave the Log on credentials as “Use Windows Authentication”, or select the “Use the following User Name and Password” radio button and enter your SQL Server credentials
8. Hit “Next >”
9. You should then see the following screen:
   
   
   
10. In the “Select the database that contains the data you want:” Dropdown, select the database you wish to use.
11. In the list at the bottom, select a table, it doesn’t matter which you choose.
12. Press “Next >”
13. You should then be presented with the following screen:
    
    
    
14. In the “Filename” Textbox, set the name you would like to save the connection as, remembering to leave the “.odc” on the end.
15. If you want the Password to be stored with this connection, then check the “Save password in file” Checkbox, say “Yes” to the warning box.
16. In the “Description” enter something meaningful for this connection
17. In the “Friendly Name:” Textbox, set the name you would like to appear in the Recent Connections dialog. There’s no need for a “.odc” extension here.
18. Hit “Finish”
19. You should then be shown the following screen:
    
    
    
20. Click “Properties...”
21. The “Connection Properties” window will be shown:
    
    
    
22. Click the “Definition” tab, which will show:
    
    
    
23. If required click the “Save password” Checkbox, and press “Yes” to the warning box
24. Change the “Command Type” Dropdown to be “SQL”
25. Set the “Command text to “exec ” + the name of your Stored Procedure. If you stored procedure requires parameters then simply tack them on the end seperated by commas.
    
    e.g. exec GetUsersByAreaandName Eastern, Smith
    
    If you wish to pass in a Null, then simply type “NULL” as a parameter.
    
    e.g. exec GetUsersByAreaandName Eastern, NULL
    
26. Press “OK”, and Press “Yes” to the warning box
27. You should be shown the Import Data screen again, Press “OK”

If everything went according to plan, you should have all your data on screen!

.EndEdit causes row movement of a sorted BindingSource

When applying a sort to a Database BindingSource, executing the BindingSource.EndEdit method causes the currently selected row to change.

This has the effect of reloading any bound controls when tee edited row is saved, which of course can have unwanted effects if you need to operating on data related to the currently selected row after the EndEdit has been performed.

This issue can also be observed to a degree by selecting a row in a bound DataGrid, then choose the sorted column. It can be seen that the physical location of the selected row doesn’t change, however the previously selected row has now moved.

There are a number of ways to deal with the EndEdit issue, one of which  I found here…

http://social.msdn.microsoft.com/Forums/en/winformsdatacontrols/thread/0878567a-ccb1-441b-a51d-3e014372e61b

Where we check the ListChanged event and the e.ListChangeType;

Private Sub bsBindingSource_ListChanged(ByVal sender As Object, ByVal e _

    As System.ComponentModel.ListChangedEventArgs) Handles _

    bsBindingSource.ListChanged

    If e.ListChangedType = _

        System.ComponentModel.ListChangedType.ItemMoved Then

        bsBindingSource.Position = e.NewIndex

    End If

End Sub

Another method is to store the current state of the form, i.e. editing, adding, duplicating, normal etc in an enum. then use the following code;

Private Sub bsBindingSource_ListChanged(ByVal sender As Object, ByVal e _

    As System.ComponentModel.ListChangedEventArgs) Handles _

    bsBindingSource.ListChanged

    If sttCurrentFormState = Normal Then

        bsBindingSource.Position = e.NewIndex

    End If

End Sub