In this post, I’d like to illustrate the power of LINQ with code sample. I query the web service published by eBay. Refer to this article about eBay API concepts.
EbayItem.cs
Program.cs
Done! in-memory objects are built. You can also follow ScottGu’s blog to wire up this code with Silverlight UI.
From time to time, we run into the scenarios of adding new columns to existing table with millions of records to meet emerging business needs. And these new columns often need initialized with default value. In this post, I’d like to illustrate my solution for such problems. Let us assume,
Database: TestDB
Existing table: TestTable
Existing columns: ID, C1, C2
And need to add,
Column name: “NewColumn”
Default value=”0”
SQLType=”INT”
Most simple solution would be:
|
USE TestDB; GO
– Add ‘NewColumn’ column and poplulate this column as 0 ALTER TABLE TestTable ADD NewColumn INT; GO UPDATE TestTable SET NewColumn = 0; GO |
Often things are not that simple. We often get other questions regarding this problem. Hereby I list them as FAQs.
Q1: My existing table is very complicated with lots of foreign keys and indexes. Does this solution work for that?
A1: Maybe not. I strongly recommend you solve this by creating a new table with the correct order, and copy all the records from the existing one to the new one.
Q2: Your solution will put newly-inserted columns to the last. Can we put them to certain specific places? I am asking because our business rule needs keep audit column in the end.
A2: Unfortunately, there is no quick way to add them in a specific place in the column order in T-SQL. One thing worth mentioning is that column order does not matter because you can select the columns in any order you want.
Q3: Your solution looks fine for me, but I get hundreds of tables to do the same thing. Can I put this into a stored procedure?
A3: Yes and No. Alter table can’t take dynamic parameters. So it is impossible to create SP such as:
|
– INCORRECT SAMPLE. DO NOT TAKE IT. CREATE PROCEDURE sp_addcolumn @tablename varchar(50) = 0, @columnname varchar(50) = 0, @datatype varchar (50) = 0 AS ALTER TABLE @tablename ADD COLUMN @columnname @datatype END |
However we can do it in another way like this:
|
– Correct sample CREATE PROCEDURE sp_addcolumn @tableName VARCHAR(50) = 0, @colName VARCHAR(50) = 0, @dataType VARCHAR (50) = 0 AS DECLARE @tsql VARCHAR (200) SET @tsql = ‘ALTER TABLE ‘ + @tableName + ‘ ADD ‘ + @colName + ‘ ‘ + @dataType EXEC(@tsql) SET @tsql = ‘UPDATE ‘ + @tableName + ‘ SET ‘ + @colName + ‘= 0′ EXEC(@tsql) GO
– The way to use the SP is as followings: – EXEC sp_addcolumn ‘TestTable’, ‘NewColumn’, ‘INT’ |
In this post, I’d like to discuss one interesting algorithm problem which took me quite a while to find an ideal solution. The problem is as followings:
Array A and B are sorted with length of m and n respectively. Try to select median of the two arrays within O(log(m+n)) time. For example,
| A = { 1, 4, 6, 10, 18 }
B = { 1, 7, 13, 45, 58, 69, 100, 180, 300 } Answer: 13 |
Step1: The lengths are equal. Let us start with a special scenario of the problem. The solution is as following.
Examine the middle element of each array, and throw out the lower half of the array with the smaller element (since all those must be less than ½ the numbers) and throw out the upper half of the array with the larger element (since all those must be greater than ½ the numbers). Now both arrays are still the same size. Repeat until you have two elements left. This is your median. Each step, you eliminate half of the numbers, so it should have a runtime of O(logn).
Step2: What if the lengths are not equal?
The smartest solution I’ve found is padding. It is best to think of median as a special case of the select problem, where given a set and an index k, you need to find the kth smallest element. Median is simply Select(n/2) when n is even, and fully defined by Select(\floor(n/2)) and Select (\Ceiling(n/2)) when n is odd. Now there is a simple O(log n) algorithm for the Select problem on the union of two equal sorted arrays, that throws out the appropriate halves of the two lists recursively.
If you want a simpler reduction to equal sized lists, pad the shorter list with an equal number of positive and negative infinities. If you need an odd number of pads (ie: when the source lists have an odd total), do it with positive infinity and (since the set size was odd) return the lesser of the two retuned values.
The major benefit of double check locking is to reduce the overhead of acquiring a lock by first testing the locking in an unsafe manner under a multi-threaded environment. It is widely used in the scenarios such as lazy initialization. Typical pseudo code would be like followings:
| IF (VAR_X meets certain conditions)
{ ACQUIRE_LOCK(); IF (VAR_X meets certain conditions) { VAR_X = NEW_VAR_X; DO_SOMETHING_ELSE(); } RELEASE_LOCK(); } USE(VAR_X); |
Unfortunately, due to the aggressive optimization of modern compiler, thread A might possibly access partial result of VAR_X before thread B actually completes updating it. As a result, the app will probably crash.
In C#, to ensure the correctness, we should declare VAR_X as volatile to ensure the assignment to the VAR_X completes before it can be accessed. I am working on a sample to demonstrate this; Can anyone share working code before I post my version out?
Singleton implementation in msdn: http://msdn.microsoft.com/en-us/library/ms998558.aspx
[Hosted by bluehost.com]
