I also first want to thank people who helped me to get where I am now with this project: Viv Phillips and Paul Mrozowski from UniversalThread, Darin Dimitrov from stackoverflow, Alex Ullrich and Sergey Kosik and also Jazzen Chen from MS. I also want to thank authors of many blogs and articles I read while working on this project.

I started my project from defining my models using Entity Framework Code first pattern since I already had the database defined. I am going to discuss only one model "Client" for the Clients table.

This is how the table looks in the database:

Last 2 constraints in the above script refer to the Operators table I am not showing here.

Let's add some data to insert:

It took me many iterations and consultations with various blogs about Entity Framework to come up with the following model:

The interesting thing in this model is 2 Complex Types. Originally this model didn't have Complex Types, but I found later that I need them in order to create one Editor for each Contact information (as we see, the table is not properly normalized and contains 2 contacts columns).

I created all my models in one project called CardNumbers.Objects. I later was criticized for such project name, but I used the same naming convention that was used by our 3 days course teacher and therefore left it as is. In my next project I am going to be more thoughtful in regards to project names.

I also created another project called CardNumbers.Data where I placed repository objects. I also originally built them the same way we were taught by the instructor, but as I found later it was not the best way, so I re-designed based on some blog posts and StackOverflow questions this way:

IClientRepository.cs:

and the actual implementation of the above interface:

and I show CardNumbersContext.cs for completion:

So, the above two projects describe my models and means to work with them.

Now I am going to describe the CardNumbers.Web project.

I started from designing the view for working with the Clients. Here I didn't want to go with the standard shown in all tutorials ways of displaying all clients information in a one page grid with inline Edit/Delete buttons. I didn't like this interface much. So, I started my research as what existing grids I may try to implement. I looked up a few different grid implementations and at some point someone suggested me to look into flexigrid CodePlex project. I really liked its look and decided to go with it. Also, I believe that same person suggested me his site which was enough to get me started. I got the Clients view working relatively quickly based on that information.

However, I didn't want to start from displaying all Clients. I wanted to simulate my current VFP application where I started from the empty grid, typed some letters in the client name search textbox and based on that show only small subset of rows initially.

This took me a while to figure out. I was almost ready to give up, but finally found addFormData method (which was there all the time) and figured out how to use it for my purpose. It may seem silly to most of the experienced jquery developers, but for me it was my first mini-success.

So, I will show you how my Client View now looks and the relevant portions of Clients.js script file and the ClientController.cs file:

As you can see, this main view has just few controls and lines of code. At the top I display two search controls and at the bottom the flexigrid placeholder and the dialog (invisible initially) for my modal Add/Edit dialogs.

Now I want to show the Clients.js part that is responsible for the flexigrid and passing Search form data to the controller through form's collection:

So, the first part defines flexigrid (you can see that it used Client controller Client method to return its data). Then the next piece of code binds grid's double click with the same method that is called by the Edit button (in other words, I want to either click on the Edit button or double click on the selected row in a grid to get the same behavior) and the last piece of code (addFormData) is used to pass both Search form and add-edit-form data back to my controller.

And this is how Client method of the controller looks like (I separated this method into 2 based on the idea I took from looking at the following solution)

So far so good. Now I want to describe the modal dialogs implementation for Add and Edit buttons. This really took me a very long time to get working correctly and I was lucky to finally got this resolved with Jazzen Chen help.

First I want to show the partial view _ClientForm. There is nothing too interesting in that view besides using special EditorForm I implemented based on this blog post by Sergey Barskiy:

This is the Clients.js relevant code which drives that Add, Edit and Delete (for completeness) buttons:

The most complicated part in the above script was to figure out how to make a server trip to get data for the Edit button and return that page back for editing.
This is done with these two lines of code:

And to make the whole picture complete I'll show the Edit method of my controller that returns the above data:

As you can see, just a few lines of code to return the model data and the partial view.

This all may seem very easy now, but I can tell you it was not easy to figure all the pieces out.

I am not showing certain functionality here such as my view model or EditorFor custom editors. I also don't show the RemoteValidator used to provide uniqueness of the client number and client name. If there will be an interest, I may discuss these details in the separate blog post.

Hope this blog post can help people struggling with the modal dialogs implementation in ASP.NET MVC application.

*** Remember, if you have a Web application related question, try our Web forum.

I was not getting the same error, so it was puzzling indeed. After some research online I found the following thread and suspected the problem was in the image size. And yes, after I chose a file about 2MB in size I was able to reproduce the same error. I haven't been able to determine the exact image size when this error occurs, but in my tests the files less than 400Kb worked and bigger files produced the error.

This is how our VFP application is structured. We use SPT for data retrieval, then using an approach similar to described in this FAQ we make this cursor updateable and then send updates using TABLEUPDATE function. We're also using DSN and old SQL Server driver. In the table that was giving this error the field for storing images is varbinary(max).

I tested a similar form where the table used an image column. The same error didn't occur and I was able to import 2MB files without a problem.

So, we now determine an internal SQL Server bug (most likely in the old SQL Server driver) that can not work with big varbinary(max) columns by using remote views form Visual FoxPro.
I also found a thread on a similar topic by Cetin Basoz in foxite.com

Luckily there was a relatively simple solution for this problem: use direct updates/inserts with parameters instead of remote views. So, in my forms I was checking for big size images and used a different approach when saving data.

Here is a test program that can demonstrate the problem:

The first direct update command succeeds, but the second remote view update fails.

Hopefully this blog post will be helpful.

Ok, we all know the classical and very comprehensive article on this topic written by Rick Strahl Using Unicode in Visual FoxPro Web and Desktop Applications. So, I started from this article. Our application uses SQL Server to hold data and uses sql pass thru technique to send information from and to SQL Server.

Now, since I am describing the solution I used for the particular application, I will start with the SQL Server table I was using. That table was named Prefs_sl and it was a flat table with 1 record containing all preferences for a sales point. Because of its flat nature it was badly normalized. Say, it had 11 language fields language00 - language10 (nvarchar(100)) to hold name of the language the Sales Point can display and 11 image lngImage00-lngImage10 (varbinary(max)) fields to hold the image of the country flag. So, this was the table I was working with on the SQL Server side.

Now, I wanted to display these 11 languages with their corresponding flags on the form, so the logical choice was to create a class with the textbox for the unicode text and image for the image flag and then instantiate this class on the form.

The first question was what to chose for displaying unicode data? Rick mentioned MS Edit Box control ActiveX, but I didn't find it in the list of my installed ActiveX. But I found MS Forms2 TextBox ActiveX control and since I also remembered this thread on foxite forum, I decided to try this ActiveX control.
The first problem I encountered with this ActiveX as that it didn't show up until I clicked on it when I was running the form. Luckily I found the following solution - invoke SetFocus of this control (I called it txtLanguage in my class) and then it was displayed correctly. I am not sure if my solution is the best to handle this ActiveX behavior quirks, but hey, it works.

Ok, so far so good. Now, following Rick's article I used the following in my form to get the data in the binary format:

mysqlexec here is a wrapper for the sqlexec function.

Now I found the first discrepancy with Rick's article - I found that I don't need any conversion after that, I can assign the field's content to the text property of this ActiveX (or ControlSource property) and the control correctly displays the unicode data!

I was happy - the first part of displaying data turned out to be quite easy!

Here is the code I used to display the data:

And in the container itself the following code in the LanguageField assign method:

Everything was good so far. However, I haven't expected how hard (for me) it will be the 'saving' data part. I was spending days trying various ideas from Rick's article and was still unable to achieve the desired result. I was about to throw all I had away and try to switch to ADO for data retrieval, but luckily Gregory Adam helped me here and published a working sample.

First of all, we need to make sure there is no UT8 translation to the current code page. For this we need to do the following

In the txtLanguage Init method.

We also need the following functions:

and then in the Change event of the ActiveX textbox I added the following code

and finally in the Save method of the form

As we can see, we need to convert data back from binary to nvarchar.
With all this code in place, we display the unicode data and save them back to SQL Server.

A big thanks to Gregory for helping me with the code - I don't know where I would be without his help.

Olaf Doschke showed another way which is even simpler than implemented solution and in accordance to the original Rick's article.

In the form's Load we need the following:

Then, after getting binary data from SQL Server the same way as I show in this blog, we still need to use createbinary, e.g.

this.txtLanguage.text = createbinary(prefs_sl.Language00)

We don't want to use COMPROP now for the ActiveX.

and then, saving data, we need to follow Rick's steps:

and then convert this value back to nvarchar(max) when saving with sqlexec.

This is how the form with many languages looked like

---------------------------
I need to also tell, that after I finished the form and was happy to show it to my colleagues, I was up to a big disappointment. It turned out we are not going to support unicode (our main application is not up to support it yet) and so I will be re-doing this form after we will agree on the tables' structures (they also are going to change). So, what I showed in this blog post we're not going to use at present in production.

UPDATE. My colleague also re-designed prefs_sl table to one EAV Settings table. We know that EAV design has its own problems, but for saving various application level settings it can be used. I already re-designed the forms we're going to use.

But in any case, it was a very important exercise and I hope it will help other people who need to display and save Unicode data in their Visual FoxPro applications.

Some notes for the script:

LEN function can be used with many SQL Server types excluding text and new types such as Geography or HierarchyID. I added exclusion of these types directly in the query, there may be some other types which need to be also excluded.

I am using one of my favorite ideas of generating script to run using available meta-data.

Here is another version of the same script which attempts to list every column in a table but uses DATALENGTH function instead of LEN for the types where we can not use LEN function:

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

The idea occurred to me last night that while we do need a dynamic SQL to solve this problem, we don't really need a loop unless we need a second loop involving looping through all tables.

Table of Contents:

Indexes in all databases with their usage
Indexes in all databases with their physical stats
Count of all objects in all databases
Record Count in every table in a database
Quick Record Count in All Tables in All Databases
Quick Record Count in All Tables in All Databases (2)
Sizes of All Tables in a Database
Database Files Sizes in All Databases
Database Files Sizes in All Databases and used space
Backup All Databases with Compression (SQL 2008)
All Schema Names in All Databases
List of All Tables in All Databases
List of All Tables in All Databases (2)
List of all Stored Procedures in All Databases

Here is a script demonstrating this idea - it lists indexes in all databases with their usage. This script was an answer to Help in T-SQL Query thread in MSDN T-SQL forum.

Indexes in all databases with their usage

Indexes in all databases with their physical stats

Another example of the same idea you can find in Finding Record with Last Modified date in all tables

Using this same idea you can get a count of all objects in all your databases using this PIVOT query:

Count of all objects in all databases

You can only list types you're interested in, of course.

The script below will give you a count of records in every table in a database:

Record Count in every table in a database

Quick row count in all tables in all databases in the server instance (you can exclude system databases from that loop, obviously)

Quick Record Count in All Tables in All Databases

Another way with less info:

Quick Record Count in All Tables in All Databases

Here is a script showing sizes from all tables in a database.

Sizes of All Tables in a Database

Here is a script showing database files sizes for all databases

Before I show the T-SQL code I'd like to point to this very interesting blog as how to get database sizes in all SQL Server instances using PowerShell

Database Files Sizes in All Databases

You can also find the script to show all database sizes using sp_MsForEachDB here. See also this relevant thread at MSDN.

Database Files Sizes in All Databases and used space

Note, that this script assumes that database files have the same name as the database itself. If this is not true, this script will not return correct result.

This script will backup all databases (using compression):

Backup All Databases with Compression (SQL 2008)

Get all schema names in all databases:

All Schema Names in All Databases

List of All Tables in All Databases

Alternative way to get all tables in all databases:

List of All Tables in All Databases

List of all Stored Procedures in All Databases

Database Files Growth:

The possibilities are endless.

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum.

For the discussion let's consider AdventureWorks database SalesOrderHeader and SalesOrderDetail tables. To illustrate the problem, let's assume that returns are handled in a different table with a structure very similar to SalesOrderDetail. And let's assume there are about 20 percent of returns (the business must not be doing too well!).

Ok, to create the returns table in the Sales schema let's use the following code:

Now, suppose we need to get quantity ordered and quantity returned as well as line total for both quantity and returned grouped by Customer ID. Let's write the most intuitive query:

Which gives us these results

Now, let's verify these results by only running Ordered amounts.

Let's run this query (I re-wrote using CTE for simplicity):

which produces the following results:

We see that numbers differ dramatically. So, what did happen in our first query? Why did it produce such high numbers that have nothing to do with the real numbers?

The reason is that for each row of Ordered results we get N Returned rows and so the total number of rows per one customer became M*N, where M is number of Ordered rows and N number of Returned rows. Therefore our results get much higher numbers.

So, the rule I always employ when I need to do aggregates that will involve more than one table to JOIN is to produce the necessary results separately as derived tables or CTE and only then join them together to form final result.

Here is how our query works when I re-write it using this idea:

with these correct now results:

I used the FULL JOIN just in case we may have Ordered items and no returns or vs. versa for the customer.

I received a question of how to write this code for SQL 2000. In SQL 2000 we can not use CTE (common table expressions), but we can use derived tables, so the code above will be re-written in this manner:

So, hopefully you can now easily recognize the problem and apply the same idea when dealing with a similar situation.

Say, few days ago I looked into report. The number of joins used in the query made my head dizzy - I told right away that because of the JOINs the total results are not going to be correct.

So, how did I solve the problem? I included the totals using WINDOW AGGREGATE function in the CTE first and then added the rest of the JOINs to get the data needed for report.

Unfortunately, I am not sure if an easier method exists for reports in SSRS when we need to present information for customer orders, for example, but also display customer addresses (and there may be many addresses per customer). Obviously, we will not get correct totals if we will just sum the records - we need to apply some tricks, simplest of them will be to pre-calculate totals and include these fields as part of the returned dataset. I will be interested to know your solutions for this problem.

I should also mention another way to solve this query (assuming we also want to return the Customer name) by using OUTER APPLY query.

Here I am showing two different approaches for comparison. The first approach performs better.

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

I want to share my solution of an interesting problem I saw today at tek-tips forum SQL 2005: Find longest sequence of events within a table

Given the following table:

find the biggest number of consecutive wins or loses regardless of the team.

Here is the code that creates our team table:

This problem is known as 'finding islands of data', and I always refer to this blog by Plamen Ratchev Refactoring Ranges when I am facing such scenario.

The idea of a solution is to first identify the blocks of consecutive wins or loses by each team. Applying the idea from Plamen's blog first step will be

By assigning GroupID we identified blocks of winning or losing streaks.

The last two steps are simple enough:

So, correctly identifying a known pattern helps to solve such problems very quickly.

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

Here is the data table we will be working with:

Our test table:

This problem is different from the typical find the duplicates problem and at first does not even seem solvable.

The first idea that comes to mind is to UNPIVOT values from these 5 columns into rows. This is simple enough with this code:

The second step is also more or less clear - find possible duplicates by counting IDs partitioned by ColValue

Now, what can we do next? The next step was a road block for me. But then, an Eureka moment - we can use CROSS APPLY and find all records that have more than 4 values matching the current record values

Ok, but do we want to delete all records identified in DupID column? Turned out, that it's not even that simple.

Say, in our sample rows 1 & 2 match. So, do we want to delete the row 2? Well, row 2 matches with the row 5. Which rows of 2/5 we delete and which to keep? That's not easy at all.

So, my final select is

This select will only produce real duplicates.

Now, this is the whole solution again as one statement:

Well, even the above solution is not quite right as now it doesn't delete all duplicates.

After some more discussion in the mentioned thread and with the help of Peter Larsson, here is the solution that seems to work for the problem:

Well, hopefully this was interesting to read as for me trying to solve such problem. Thanks for listening!

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

With the introduction of PIVOT operator in SQL 2005, it became very easy to write queries that transform rows into columns. There are numerous articles on the Web as how to perform PIVOT and even dynamic PIVOT. However, most of these articles explain how to perform such queries for just one column. I want to expand the transformation for multiple columns.

The idea of a solution is quite simple - you need to generate the SQL dynamically. Since we want to use pivot on multiple columns, we need to use CASE based pivot.

You need to keep in mind, that resulting query should have less than 1024 columns. You may build the check for number of columns into the query.

Now, every time I need to create a dynamic query, I need to know what is the final query I am going to arrive to. Having the idea in mind and printing the SQL command until I got it right helps to create the working query.

Let's consider the first example based on the AdventureWorks SalesOrderHeader table.
This code creates the table we're going to transform - it gives us summary of orders and total due per each quarter:

The #SalesSummary table lists count of orders and total due for each quarter and year.

Now, suppose we want to see these results horizontally. The following dynamic SQL produces the desired output:

It produces the transformed result (I show only few first columns):

You may want to un-comment PRINT @SQL statement if you want to see the generated command. I used XML PATH to concatenate rows into a string based on this blog post by Brad Schulz Making a list and checking it twice.

Another interesting problem was presented in this MSDN Transact-SQL forum thread:

For the claims table that has many integer columns, transform rows into columns. I demonstrate just the solution with the table creation script:

The output is:

And the following code transforms the result (as you see, I am using a loop and ROW_NUMBER() approach):