So slowly but surely some of our databases are making it from SQL Server 2000 to SQL Server 2005. In that process, some of our databases are moved using the quick 'copy' method. Simply right click on the DB in SQL Management Studio, go to tasks, select Copy Database.
However, on my setup:
Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.832
Operating System 5.1.2600
When moving from a SQL 2000 -> 2005 copy, the new database fails to maintain the 'identity specification' as YES on the primary keys of my tables. Subsequently, I had to manually add the identity spec. back on about 10 tables. Lame!
I wanted to report this bug to Microsoft, but figuring out how to report a bug is like pulling teeth! There's no general public bug submission form; you can go through their 'connect' program but its a huge pain in the ass. If they want to write better software, Microsoft should at least give me a bug report form.
Right now, for our most quick-and-dirty reporting needs from Relational and OLAP databases, we are toying with the idea of using SQL Server 2005 Reporting Services to generate our web reports. Using this server means we get ubiquitous access to our reports in an easy-to-use, consistent manner.
A major hurdle to using Reporting Services is that by default, Parameters cannot be flagged as 'optional'. After much delay, I found there is a way to 'Hack' a report to have an optional Parameter.
First, place your parameter in the Query as such:
SELECT * FROM Table
WHERE
(AssetID = @AssetID OR @AssetID IS NULL)
(Coincidentially, this is the same method I used to make parameters optional in .NET DataSets for web applications).
Second, in the dataset to populate the Parameters, make sure at least one row returns NULL. You'll need this to have the null row appear in the dropdown that the parameter creates. It might look like this:
SELECT DISTINCT AssetID, AssetName FROM Table
UNION
SELECT NULL, 'All'
ORDER BY 1,2
Third, configure the parameter as such; with the default value as null, to allow you to select 'All'
Hope this helps someone!
Our current software development lifecycle involves developers working on code locally, then publishing to a test server location for Q/A. Using the 'Publish' command in Visual Studio 2005 is a lifesaver. But sometimes you want to only publish part of the project, excluding specific files.
Unlike building a windows application or DLL, there is no verbose build language screen when you go to the properties of a web project. Instead, a mysterious vwd.webinfo file is generated that contains instructions for Visual Studio. You can manually edit this file. Opening it in VS will reveal that it's just an XML file. And reading for a while on the web, I found that you can add an ItemGroup node within the VisualWebDeveloper tag.
1
2
3
4
5
6
7
8
9
10
11
12
| <?xml version="1.0" encoding="UTF-8"?>
<VisualWebDeveloper>
<!--
Visual Studio global web project settings.
-->
<ItemGroup>
<ExcludeFromBuild Include="$(SourceWebPhysicalPath)\**\*.config" />
</ItemGroup>
<StartupServices>
<Service ID="{967B4E0D-AD0C-4609-AB67-0FA40C0206D8}"/>
</StartupServices>
</VisualWebDeveloper> |
this command excludes .config files from being updated on a publish. hope this helps!
Sometimes you can't trust the users of your web application to be savvy enough to navigate away from your application. Most likely, what happens is that a user must do a third party process in order to pass data to your web application, such as scanning in an image before uploading it. Having the user need to minimize the browser window, then open a scanning application, then save the file to a remember-able place, then restore the browser window and 'Browse' to that location can be very overwhelming to people not necessarily technical.
Eliminating the steps above and encompassing as much of the functionality within the browser can be very convienient. As a primer, see this article:
http://msdn.microsoft.com/msdnmag/issues/02/06/rich/default.aspxThere are a few interesting hiccups I ran into trying to get this to work, and I wanted to share these with you:
- Getting the control to load can be difficult. To make sure the control itself is loading, add a simple button on the user control. If the control loads with the 'image' type square in the top left, and you don't see your button, check the path to the DLL. make sure the DLL is NOT in the Bin folder (IIS will not serve files from the Bin folder).
- If you get an 'Object does not support this property or method' error client side in your javascript when trying to access public methods of your class, be sure you have ComVisible set to true above your definition (see below). This most often happens with .NET 2.0
The ComVisible attribute was the hardest nut to crack. Above your class definition, do as such:
1
2
| [ComVisible(true)]
public class MyClass : UserControl{...} |
This will get rid of the accesibility problems for javascript and the pesky 'object does not support' messages.
What did I use this all for? Well, we support a large base of users who are not technically savvy. As part of their compliance operations they need to have a document management solution that keeps a digital copy of their documents and letters. Our web application has a form to fill out document information, but instead of a 'browse' button for the file itself, we have a 'scan' button in the Windows forms control inside the webpage. When the user clicks that button, the Twain interface takes over, letting them crop the document. When they finish the scanning, Twain passes the image back to our control, which uploads it to the Web Server along with the other form information.
Well, lesson learned. the GridView is not infalliable.
So I am building another nightmare report that will sift through reams of data. This one has two Gridviews, one nested inside of the other. The point of the Gridview will be to display an asset and have the child gridview's rows display the locations where the asset exists. Aside from the entire table being a Pivot/Crosstab, and that I have to expect, on minimum, 100 assets to be returned for any search, I am also working with a custom data layer written for the project. So binding to these grids is, well, challenging.
To keep the amount of database traffic down, I only make two queries: One to get the asset list, and one to get all locations for those assets. Storing the locations in a datatable, I can use the datatable's 'Select' method to populate the subgrid. One problem we have is that the key field we 'Select' on, the ID of the asset, is already listed in the parent table, so I want to hide it.
<asp:TemplateField>
<ItemTemplate>
<td> <td colspan="5">
</asp:GridView runat="server" ID="SubViewContainer" CssClass="Grid"/>
</ItemTemplate>
</asp:TemplateField>
</Columns>Here is the nested grid, inside a template field in the parent grid. On the RowDataBound event of the parent grid, I bind the child grid by extracting the control from the parent grid's columns. With the grid extracted, I can bind it individually.
GridView SubView = (GridView)e.Row.Cells[6].Controls[1];
DataRow[] SubData = WeeklySubData.Tables[0].Select("ID = '" + ID + "'");
DataTable Tbl = WeeklySubData.Tables[0].Clone();
foreach (DataRow D in SubData)
{
Tbl.ImportRow(D);
}
SubView.DataSource = Tbl;
SubView.DataBind();So this gets us our sub-data bound to the sub-grid. Great! Now let's hide the first column. That first column is only there so we can use the Select method, we don't need to display it. So let's just do
SubView.Columns[0].visible = false; , right?
Actually, no. For some reason, I don't have access to the Columns and that line would throw a runtime exception. The columns will have a Count of 0 and I can't do anything. So what's the solution?
Eventually I broke down and added an event handler to handle the 'OnRowCreated' event of the sub-grid. When this event fires, we're able to see the row we're in and hide the first cell of that row. I'm sure this is probably more expensive performance-wise; but it also works, which is the primary feature.
The event handler attachment code looks like
SubView.RowCreated += new GridViewRowEventHandler(SubView_RowCreated);
And the event handler looks like:
void SubView_RowCreated(object sender, GridViewRowEventArgs e)
{
e.Row.Cells[0].Visible = false;
}Problem solved. now to explain to my boss what I've been doing all morning.
If you're like me, you love the TableAdapters feature of Visual Studio 2005. Simply right-click on the App_Code folder, add a new item, and choose DataSet. Then pick a database to model it against, and BAM! A Strongly typed data object is built for you on the fly. This model enhances portability for the code we write at my company, since we have a core set of tools that look at different models depending on where you are in my company. (Texas has a different application running their operations versus us here in Boston. Therefore, their data model is different. I could write two datasets and one presentation layer to cover both.)
Anyways, So I went a little DataSet-crazy by making a TableAdapter for every View we use (essentially, every business object has its own Table Adapter). Then I wrote accessor-methods to get the data out of the TableAdapter depending on the Report (some reports filtered by X, others by Y, etc).
However, one of the methods I was calling was failing pretty badly.
1
| ReportDataIndex.DataSource = T.GetDataGroupByWeek(ReportDate.SelectedDates[0]); |
Seems pretty simple, right? ReportDataIndex is a GridView, and T is an instance of my TableAdapter. But every time I ran it, I got an exception:
Failed to enable constraints. One or more rows contain
values violating non-null, unique, or foreign-key constraints.I read and read and read, and couldn't figure it out. A few forums recognized the problem, such as
this article about DataSets, but none specifically mentioned the problem as it could have related to TableAdapters. I couldn't turn off 'EnforceConstraints' on my GridView, and I really struggled. I tried wrapping the Query Columns with 'ISNULL()' but that didnt' work either. I restricted my data results from the DB to only include non-nulls, and it was still failing.
After much soul-searching, I found the issue: If you create additional accessor-methods to your DataSet, their column count has to match the TableAdapter column count. Any columns not mentioned in your addtional method is passed back into the DataSet object as NULL. That's why the Fill was failing. Now, when I build my accessor-method and Query in the designer, it should throw an error and not let me save the DataSet / Compile. However, no pre-compilation is done with this regard on the XML document generated by the DataSet designer.
This is kind of limiting and if you have a TableAdapter that has two methods, one to get rows and one to get DISTINCT subset of rows, it can really cause problems. However, this small flaw is no reason to abandon DataSets and TableAdapters, as they are still great for rapidly prototyping data structures in your application
One of the tasks I'm often given is to take a simple data model, like a SQL Server 2000 View, and render the data transposed in a certain way (rows made into columns, weird grouping operations, etc). Most of the data sets I work with contain a LARGE amount of rows to sift through.
Early in my development career, before the .NET platform was widely used, I was taught to do data transformation as close to the 'metal' (aka the SQL server) as possible. Make your SQL queries give you as close to the display data as possible, and don't loop through results after-the-fact. This model serves .NET well because of the binding model on most controls. Binding an end-result right to a control makes life sweet!
But some of these transpositions and groupings are complicated. They require re-organizing data that, if done in SQL server, would involve large amounts of sub-queries, or esoteric matching/grouping. Furthermore, some functions, like date and string formatting, seem out-of-element on the data store. In a recent project, I was given this tiered excel file, and I was to make the data match the format. I tried it in SQL at first; but soon I decided that since the whole .NET philosophy is Rapid Application Development, I decided to do it the 'quick' way. I busted out Crystal Reports, bound the report to the SQL View, and built the display in Crystal.
Part of me doesn't mind using tools like Crystal. It doesn't affect the data model at all; and I consider reports part of the presentation layer of an application anyways. But part of me definitely likes the 'grab and bind' philosophy. So I pose it to the BloggingAbout.Net readers: Which do you prefer? What are your pros and cons?
Hey there! I wanted to introduce myself to the BloggingAbout.Net Community. My Name is Ed Giardina, and I'm a .NET software developer on both the Windows and Web forms sides of things. Hopefully I can provide some unique insight and share some tricks of the trade.
More Posts
« Previous page