Pages

Friday, December 27, 2013

Windows 8 is Costing You Money

Your Money is Being Sucked Down a Hole
I have a client that I have designed a desktop app for.  It's a Winforms app designed in Visual Studio 2010 and uses the barely functional DataGridView object to edit data.  It's been working forever in Windows XP, Vista, and 7.  Though as stated, the DataGridView gives constant headaches.  Now the boss has upgraded his tablet to Win 8 and the app has stopped functioning once again, crashing while trying to automatically calculate column widths during a paint event.

This function of the DataGridView has been spotty at best even on the other versions of Windows, and I have had to code around problems with it before.  Basically, the thing is just a nightmare to configure and use. I constantly find myself writing 200 lines of code to get the damn thing to stop crashing, when in my humble opinion, the damned thing ought to just work out of the box.  Now, I can see there might be problems if I were trying to shoehorn grids into grids, or streaming media into 20,000 rows of data... but really I am doing nothing out of the ordinary here.

Now I have to stop ongoing development, and retrofit this whole app (at the client's expense) to an operating system that - say it with me - ought to just work out of the box!

Meanwhile the client is wondering if I am really all that sharp, because Microsoft muffed up their OS with backward compatibility problems.  Thanks, Microsoft!
...

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Monday, December 16, 2013

Refresh All Views on SQL Server

Whenever you make a change to a table - say, to modify a column - you need to update all the views that depend on that column.  This script will find and update all your views. 

-- Back Up All Databases
-- by Bryan Valencia

--create temp table
declare @temp table(commands varchar(500), completed bit)

--load it with backup commands
insert into @temp (commands, completed)
(
    SELECT DISTINCT 'EXEC sp_refreshview ''' + name + '''',0
    FROM sys.objects AS so
    INNER JOIN sys.sql_expression_dependencies AS sed
        ON so.object_id = sed.referencing_id
    WHERE so.type = 'V'
    and is_schema_bound_reference = 0
)

--variable for the current command
declare @thisCommand varchar(500);

--loop through the table
while (select count(1) from @temp where completed=0)>0
begin
    --find the first row that has not already been executed
    select top 1 @thisCommand = commands from @temp where completed=0

    --show the command in the "mesage" output window.
    print @thisCommand

    --execute the command
    EXEC (@thisCommand);

    --flag this row as completed.
    update @temp set completed=1 where commands=@thisCommand
end

--show the user the rows that have been found.
select * from @temp


Of course if a view is now no longer correct, you'll see on your messages tab in SQL Server Management Studio.

...

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Friday, December 13, 2013

Integratederatederatedratedrated

This is not about Visual Studio.  In fact it's from 1982 when I was in tech school in Phoenix, Arizona.   We were using a Radio Shack Model 100 computer with 4K of RAM and a blazing fast cassette drive to store our programs.  Marc was using BASIC to create a simple keyboard-to-printer program so that he could type a page of text, edit, and print the text on the Epson dot matrix printer.

Basically he created an array to hold all the characters typed, and used that array to draw the text onscreen AND to print it when he was done. 

There was one small thing though.

The backspace character was a character like any other in the array, it (correctly) backed the cursor up on the screen, but did absolutely nothing to the printer. So Marc was typing a paper about integrated circuits, and his edits - which looked fine onscreen, printed out as Integratederatederatedratedrated Circuits.

The moral of this story is:  There's always more to a program than you thought up front.


...

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Thursday, December 5, 2013

Selecting scalar values from a stored procedure

The easy and obvious answer is to use a function, not a procedure.   Then you can use it like any built-in T-SQL function.

select 1, dbo.Function() But alas, functions do not allow us to store (insert, update, delete) any data to the database and my stored procedure needs to do exactly that. 

In a moment of brilliant engineering, SQL Server does not allow the return value of a stored procedure to appear as a column in a query (select, insert, update, where clause),  so you can't just say...

select orderID, dbo.StoredProcedure() from... In my task, I had to insert the results of a stored procedure into a column in a table.  The code example I show below creates a temporary lookup table, and uses it later for a list of inserts.  try to follow this...

--creates a temp table for later use in joining (not shown)
declare @PickListNo table(OrderID varchar(20), PickListNo int, done bit);

--loads the temp table, except the column from the stored procedure
insert into @PickListNo (OrderID, done)
(
    Select distinct AOO.[order number], 0 from uploads.open_orders AOO
)

--vars the stored procedure needs
declare @A int
declare @NextVal int   

--while there are unprocessed rows...
while exists (select 1 from @PickListNo where PickListNo is null)
BEGIN
    --execute the procedure and capture the return value
    exec @A=dbo.NextPickListNo @NextVal OUTPUT
   
    --update one row in the table
    --I used Max(ID) to find a single row, but I might have used MIN, or Select top 1 as well.
    update @PickListNo set PicklistNo = @A where OrderID=(select MAX(orderID) from @PickListNo where PickListNo is null)
END
This use of the while loop is my way of cheating and not using cursors.  Cursors are nicely powerful but demand a lot of babysitting and resources (so I am told).

...

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Tuesday, December 3, 2013

Conditional Output with the ? Operator in C#

I needed to ensure that column data was trimmed to the correct lengths in a SQL insert.  Normally I would expect that this code...

string toobig="This string is exactly 37 characters.";
string nottoobig = "13 characters";


//attempt to trim both to 20

string s;
s = toobig.Substring(0, 20);
Console.WriteLine(s);

s = nottoobig.Substring(0, 20);
Console.WriteLine(s);

 ...would do the trick.

 Sadly, if you use substring with a length greater than your string, you don't get the whole string as you might expect, you get this.

System.ArgumentOutOfRangeException was unhandled
  Message=Index and length must refer to a location within the string.
Parameter name: length
  Source=mscorlib
  ParamName=length
So we need a way to trim a string only if it's needed, i.e. a conditional trim.  Normally this is written like this:

if (nottoobig.Length > 20) { s = nottoobig.Substring(0, 20); } else { s = nottoobig; };
This works, and so does this...

s = nottoobig.Substring(0, Math.Min(20, nottoobig.Length)); where we substring to the min of the actual length and the desired length.

But there is also this:

s = nottoobig.Length > 20 ? nottoobig.Substring(0, 20) : nottoobig; This works just like excel iif():
condition ? first_expression : second_expression;
Condition must be true or false. If true, the first_expression is used, if false, the second_expression is.


 

So I have written a clip() function that acts the way I expected substring() to:

        static void Main(string[] args)
        {
            string toobig = "This string is exactly 37 characters.";
            string nottoobig = "13 characters";
           
            //attempt to trim both to 20
            string s;
            s = clip(toobig,20);
            Console.WriteLine(s);

            s = clip(nottoobig, 20);
            Console.WriteLine(s);
        }

        private static string clip(string s, int max)
        {
            if (max < 0) max = 0;
            return s.Length > max ? s.Substring(0, max) : s;
        }



...

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Share This!

Contact Us

Name

Email *

Message *