Pages

Thursday, March 22, 2012

Sorting a non-SQL Dataset

So I have an ASP.NET web site and the data is coming not from a SQL Server database, but from a web service.  I have executed the Web call and gathered the data into a dataset, and I wish to sort the data inversely by job order number, floating the newest job orders to the top.

As it turns out, there is no Dataset.Sort command, but there is this.

DataRow[] SortedRows = JobsDataTable.Select("isOpen = 1", "jobOrderID desc");

This gives back a sorted (and also filtered) array of DataRows based on the criteria you use. 

Now, you can't databind to a DataRow array, but if you are using databinding, the DataGrid or DataGridView can filter and sort for you, so you wouldn't need to use this technique.



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.

Saturday, March 17, 2012

Making an Image into a Usable Web Background.

 Ok, you have this great image and you want to make it the backdrop for your company web page.  Let's pick this one.
Photo of a building.
Beautiful.  But look what happens when you use it as a web backdrop.

OMG you can't read a thing!  So let's try playing with the contrast...

brightness+contrast
 Not this!  It's washed out all the details!  Maybe the levels...
 Closer, but there must be a better way.  And there is!  The problem is that we're thinking like engineers and not artists.  Try this.

Add a layer of White over the top of the image.  Then set the transparency of that layer to about 85%  This trick also works for dark backgrounds, just fill the new layer with black.



...

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, March 16, 2012

C#.NET Finding the First Day of the Week in One Line of Code

while (myDate.DayOfWeek != DayOfWeek.Sunday) { myDate = myDate.AddDays(-1); }


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, March 15, 2012

So, I deleted and recreated a dataset in my Visual Studio Project.

Now, the IDE is all banjaxed, and confused.

Note:

My Solution Explorer
 Here is the shiny new dataset in the Solution Explorer.  Now look in my Data Sources pane.
My Data Sources Pane
...and no amount of refreshing, reloading, or rebooting fixes it.

The Solution
This was an odd one to be sure.  but looking at the properties window in the LotTravellerDataset1.xsd, the name property was set to "Dataset1".  Changing this property fixed the issue.


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.

Using a Stored Procedure that Returns Multiple Results

Microsoft did a brilliant thing in SQL Server.  I applaud them for this.  You can actually pack more than one SQL Query into a stored procedure and have a single procedure return multiple result sets in one pass.  So I used this feature to write a stored procedure that gathers all the data I need for a certain .rdlc report in my app.  Now I can use this one stored procedure to gather all the data in one fell swoop.

I have done this kind of multiple result set stored procedures in the past and it's a simple matter in code to sort out the data tables, as they are returned in order. Here is some sample code for another instance of this method.

/// <summary>
/// gets the dataset from the database.
/// </summary>
public void Load_Data()
{
    SqlCommand myCommand = new SqlCommand("[dbo].[ExtractWebData]", myConnection);
    myCommand.CommandType = CommandType.StoredProcedure;

    System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(myCommand);
    ds = new DataSet();
    adapter.Fill(ds);
    ds.Tables[0].TableName = "Customers";
    ds.Tables[1].TableName = "Purchase_Orders";
    ds.Tables[2].TableName = "Purchase_Order_Details";
    ds.Tables[3].TableName = "Inventory";           
}

Now if there was just some way to use that in a report.  It would be great to use a stored procedure to twist all the data into shape before handing it off to a reporting tool!  So you create an xsd. You add a table adapter.
You tell it to use your existing stored procedure.

 You select your stored procedure, and there's the first dataset... But wait.  How do you tell it where the other result sets are?  I want to use all 3 datasets! 



Except that you can't do that.  You see, the .rdlc report requires that you have the data available at design time in order to design the report.  And there is no way to import multiple datasets at once into the .xsd at design time.

From this document: http://msdn.microsoft.com/en-us/library/dd239331.aspx

If multiple result sets are retrieved through a single query, only the first result set is processed, and all other result sets are ignored. For example, when you run the following query in the text-based query designer, only the result set for Production.Product appears in the result pane:
SELECT ProductID FROM Production.Product
GO
SELECT ContactID FROM Person.Contact
I have no idea what the text-based query designer is, but as we saw in SQL Server Management Studio...



In my opinion, this is an EPIC DESIGN FAIL on the part of Microsoft.

We know from the earlier code snippet that Visual Studio can access the data, it just - for some stupid reason - is designed in such a way as to disable this feature in certain cases.  This is completely unacceptable.  But until Microsoft fixes this glaring, stupid, boneheaded omission, we're stuck with it.

Now, I know this blog has no regular readers.  You didn't find this page because you're a fan of Visual Studio Journey.  You found it because you were googling for this problem, and there were no answers anywhere else.  I wish I had better news, but I don't.

Here is the only way I know to work around this issue.  Unravel your stored procedure and execute the whole thing in your client.  Convert each piece into individual queries, and add those to your .xsd.

Alternately, you could split your stored procedure into multiple pieces, like Proc1, Proc2, Proc3... but then you've kind of lost the convenience of the one-stop shopping the stored procedure offers.

Just to be clear: I think that the ability to return multiple result sets from a stored procedure is awesome!  Kudos to the SQL Server development team.  If only the Visual Studio guys would catch some of that brilliance, things would be great.

Addendum:
Just to make myself clear, I think that this feature has awesome potential and multi-table stored procedures are still incredibly useful in Visual Studio.  They just are not usable for rdlc reports (the one thing they would be most ideally suited for in an ideal world).

...

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, March 6, 2012

How to easily compare 2 SQL Server Databases.

NOTE: this is for SQL SERVER databases only.  It will not sync Oracle to Interbase or MySql to Sybase.  However, application of this technique may apply to any two databases of the same type. (i.e. oracle to oracle)

I had lost access to the  production database for a time, and wanted to ensure that I had propagated all my recent changes from Development to production - without spending $895 for a SQL management and database analysis system.

So here's how I proceeded to sync my tables from one database to the other.

Open the Production database (or a current backup of it) in SQL Server Management studio.
use this query:
SELECT
    TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA='dbo'
order by 1,2,4
Note: these columns were important to me, feel free to modify the columns as you see fit.  Also I only cared about the 'dbo' schema, so I filtered for that.  Your needs may vary.

This will give you the column info for all tables in the 'dbo' schema.

Now, save the data by right clicking the grid and selecting Save Results As.
Give it a name like Production_Schema and save as .txt.

Note: csv works too, but I find txt easier to compare.

Next do the same with the development database, naming it something like Development_Schema.txt.

Now you need a diff tool like WinMerge.  Compare these 2 files to see where they differ.  The text files cover all columns in all datatables and views in the schema.

Now go through the differences and see what changes have to be made in production so your software doesn't crash.  When I did this, I noted that there were some changes that were not ready for prime-time yet, so I left them unfixed in production.

The only thing left to compare once this is complete are the stored procedures and functions.  I just made a fresh, empty query and did a Script Stored Procedure As > Drop and Create to > Clipboard and pasted every one of them into the query. I saved it as AllFunctions.SQL and ran it in production to sync the functions.


 I hope this helps!

Comments are welcome!
...

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 *