Archive

Archive for the ‘Vs2010’ Category

Strange behaviors in Crystal

I found today a strange behavior with Crystal Reports .NET , using Visual Studio 2010.
In a recordsource there are 2 numeric (integer) columns that were displayed in the preview window, inside Visual Studio.
At runtime for these 2 colums, nothing: no values displayed, even the headers.
This in a subreport; this one in origin had the same SQL of the main report.
Then the datasource of the subreport was changed, some columns previously used in the report was deleted, substituted with other columns, with different names.
I tried even to change the original subreport SQL (a JOIN between 2 CTE, inside every CTE a PIVOT) with a plain SQL (SELECT ‘value’ AS columnname) but without success: still no data displayed at runtime inside the subreport.
The solution is very strange, a Crystal formula (Vb syntax):

If IsNull({Command.TotalTSG}) then
    formula = 0
else
    formula = {Command.TotalTSG}
end if

If the fields are placed directly in the Crystal editor, data visible in preview but nothing at runtime; instead it works also at runtime with Crystal formulas: data displayed.
Note: all of the remaining fields (also integers) are displayed if placed directly on the report (but are fields in common as name between the main and the subreport), even without a formula.
Mysteries of Crystal Reports.

Categories: .NET, Crystal Reports, Vs2010

Autocomplete extender with Id and Description

It was a very long time ago that i worked with the Ajax Toolkit, but sometimes you are requested to implement old projects..

And the language is VB.NET, that i was not using since 2003.

The request was to implement an Autocomplete extender from a customers list (from a Sql Server table), but in the database is requested to write the autoincrement ID, not the description.

In order to implement the ajax toolkit, in the .aspx markup must be added the header:

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>

With the obvious adding of a reference , for the ASP.NET project, to AjaxControlToolkit.dll

A simple autoextender can be written as

<asp:TextBox ID="txtAgency" runat="server" ></asp:TextBox>
<cc1:AutoCompleteExtender ID="aceAgency" FirstRowSelected="false" MinimumPrefixLength="1" TargetControlID="txtAgency" ServiceMethod="GetListAgencies" runat="server">
</cc1:AutoCompleteExtender>

in the aspx page; in the code behind for this page we can implement a page method (the “ServiceMethod”) as

<System.Web.Script.Services.ScriptMethod(), _
System.Web.Services.WebMethod()> _
Public Shared Function GetListAgencies(ByVal prefixText As String, ByVal count As Integer) As List(Of String)
    Dim conn As SqlConnection = New SqlConnection
    '
    conn.ConnectionString = <string from web.config>
    Dim cmd As SqlCommand = New SqlCommand
    cmd.CommandText = "SELECT AnagId, AnagName FROM [dbo].[SomeTableOrView] WHERE AnagName LIKE @SearchText + '%'"
    cmd.Parameters.AddWithValue("@SearchText", prefixText)
    cmd.Connection = conn
    conn.Open()
    Dim lstAgencies As List(Of String) = New List(Of String)
    Dim sdr As SqlDataReader = cmd.ExecuteReader
    While sdr.Read
        lstAgencies.Add(sdr("AnagName").ToString))
    End While
    conn.Close()
    Return lstAgencies
End Function

This works, we type one letter (MinimumPrefixLength=”1″) on the keyboard and we are proposed with the list of names beginning with the typed letter.

But from the database in our sample we have only the AnagName value , the AnagId is not connected in some manner with the selected item: if we need to save that Id instead of the name this approach is not valid.

In order to save that id we need some javascript code and another approach in the web method.

First, this is the new aspx markup:

<asp:TextBox ID="txtAgency" runat="server"></asp:TextBox>
<cc1:AutoCompleteExtender ID="aceAgency" FirstRowSelected="false" TargetControlID="txtAgency" ServiceMethod="GetListAgencies" OnClientHidden="onClientHiddenAgency" MinimumPrefixLength="1"  EnableCaching="true" OnClientItemSelected="onClientItemSelectedAgency" OnClientPopulating="onClientPopulatingAgency" runat="server">
</cc1:AutoCompleteExtender>
<input type="hidden" id="hdnAgencyId" name="hdnAgencyId" runat="server" value="0" />

The OnClient… properties are the names of javascript functions; the EnableCaching is true because the underlying data are in this case very static.

Note that there is an hidden field where to save the requested Id and this is runat=server because we need to refer this field when we will save the record.

The javascript functions (ok, not the best javascript code…):

var boolChosenAgency = false;

function onClientItemSelectedAgency(source, e) {
    window.document.getElementById("<%=hdnAgencyId.ClientID%>").value = e.get_value();
    boolChosenAgency = true;
}

function onClientHiddenAgency(source, e) {
    if (!boolChosenAgency) {
        window.document.getElementById("<%=hdnAgencyId.ClientID%>").value = "0";
        window.document.getElementById("<%=txtAgency.ClientID%>").value = "";
    }
}

function onClientPopulatingAgency(source, e) {
    window.document.getElementById("<%=hdnAgencyId.ClientID%>").value = "0";
    boolChosenAgency = false;
}

What happens with this code is:

  • When the user begins to write, the Id value is changed to 0 and boolChosenAgency is set to false (“the user has not stille chosen an valid value”)
  • Only when an item is chosen the Id (coming from e.get_value()) is written in the hidden field
  • OnClientHiddenAgency is always executed, if the user has opened the list but has not clicked on a value then the id value is changed to 0

On the code behind so we must verify : if the hidden id is 0 and/or the textbox is empty the the id must be null on the db, otherwise we can write the int value.

The scope of the code is also: if the user write a not valid text, the Id is invalidated.

But how we can have the id in e.get_value() ?

This is done with this code for the web method:

<System.Web.Script.Services.ScriptMethod(), _
System.Web.Services.WebMethod()> _
Public Shared Function GetListAgencies(ByVal prefixText As String, ByVal count As Integer) As List(Of String)
    Dim conn As SqlConnection = New SqlConnection
    '
    conn.ConnectionString = <string from web.config>
    Dim cmd As SqlCommand = New SqlCommand
    cmd.CommandText = "SELECT AnagId, AnagName FROM [dbo].[SomeTableOrView] WHERE AnagName LIKE @SearchText + '%'"
    cmd.Parameters.AddWithValue("@SearchText", prefixText)
    cmd.Connection = conn
    conn.Open()
    Dim lstAgencies As List(Of String) = New List(Of String)
    Dim sdr As SqlDataReader = cmd.ExecuteReader
    While sdr.Read
        lstAgencies.Add(AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(sdr("AnagName").ToString, sdr("AnagID").ToString))
    End While
    conn.Close()
    Return lstAgencies
End Function

The trick is done using the CreateAutoCompleteItem method as in the above code.

In the javascript code we can use e.get_value for the ID and e.get_text() for the name (“AnagName” in the sample).

Categories: .NET, Ajax, Javascript, VB.NET, Vs2010

Asp:ButtonColumn not working in Ie11

I was working to an old VB.NET web application with Visual Studio 2010 , .NET 4.0; the solution for tables uses asp:DataGrid

Until Internet Explorer 10 all ok, with Ie11 the asp:ButtonColumn inside an asp:DataGrid was not working, clicking the link nothing.

It seems that the .NET 4 framework does not work well with Ie11 , and the update to .NET 4.5 was not an option (Windows Server 2003).

With the latest updates Ie11 was still recognized as Mozilla 0.0

The solution is to add an App_Browser folder

And then inside the folder create an .browser file with this content:

<browsers>
  <browser id="IE11" parentID="Mozilla">
    <identification>
      <userAgent match="Trident\/7.0; rv:(?'version'(?'major'\d+)(\.(?'minor'\d+)?)(?'letters'\w*))(?'extra'[^)]*)" />
      <userAgent nonMatch="IEMobile" />
    </identification>
    <capture>
      <userAgent match="Trident/(?'layoutVersion'\d+)" />
    </capture>
    <capabilities>
      <capability name="browser" value="IE" />
      <capability name="layoutEngine" value="Trident" />
      <capability name="layoutEngineVersion" value="${layoutVersion}" />
      <capability name="extra" value="${extra}" />
      <capability name="isColor" value="true" />
      <capability name="letters" value="${letters}" />
      <capability name="majorversion" value="${major}" />
      <capability name="minorversion" value="${minor}" />
      <capability name="screenBitDepth" value="8" />
      <capability name="type" value="IE${major}" />
      <capability name="version" value="${version}" />
    </capabilities>
  </browser>

  <!-- Mozilla/5.0 (Windows NT 6.3; Trident/7.0; rv:11,0) like Gecko -->
  <browser id="IE110" parentID="IE11">
    <identification>
      <capability name="majorversion" match="11" />
    </identification>

    <capabilities>
      <capability name="ecmascriptversion" value="3.0" />
      <capability name="jscriptversion" value="5.6" />
      <capability name="javascript" value="true" />
      <capability name="javascriptversion" value="1.5" />
      <capability name="msdomversion" value="${majorversion}.${minorversion}" />
      <capability name="w3cdomversion" value="1.0" />
      <capability name="ExchangeOmaSupported" value="true" />
      <capability name="activexcontrols" value="true" />
      <capability name="backgroundsounds" value="true" />
      <capability name="cookies" value="true" />
      <capability name="frames" value="true" />
      <capability name="javaapplets" value="true" />
      <capability name="supportsCallback" value="true" />
      <capability name="supportsFileUpload" value="true" />
      <capability name="supportsMultilineTextBoxDisplay" value="true" />
      <capability name="supportsMaintainScrollPositionOnPostback" value="true" />
      <capability name="supportsVCard" value="true" />
      <capability name="supportsXmlHttp" value="true" />
      <capability name="tables" value="true" />
      <capability name="supportsAccessKeyAttribute" value="true" />
      <capability name="tagwriter" value="System.Web.UI.HtmlTextWriter" />
      <capability name="vbscript" value="true" />
    </capabilities>
  </browser>
</browsers>
Categories: .NET, Ajax, Vs2010

Microsoft SQL Server 2012 Integration Services: An Expert Cookbook


I worked already with DTS, then with the previous SSIS versions (2005, 2008) but it was not my main activity, only occasional work items.

Now SSIS 2012 is becomed an huge , very complete product and i’m more involved in this argument, so i buyed some books on the argument.

The typical problem of a newcomer to this argument is that after opening SSDT you are staring at the screen observing the icons and wondering what are these items and how to correctly use them , when and where.

For example many icons are intuitive, a “Send Mail Task” is clear what it do, a “Data Flow Task” is intuitive that is something related to data transformations, but inside a “Data Flow Task” begins to appear strange things: no more the intuitive “Script Task” where i code immediately something in VB or C# but an “Script Component” that asks if should a be a Data Source or Destination (a script..as Data Destination?) , the “DQS Cleansing”, “Slowly Changing Dimension”…help…

This book is a valid starting point, even if is not updated to the latest SSIS version: for the examples is used the Visual Studio 2010 shell, but is valid also for the SSDT version working inside Visual Studio 2012.

I began to follow all of the chapters in June in the spare time, and in the meantime i migrated from the Visual Studio 2010 shell to 2012 , without encounter big differences.

This Expert Cookbook is developer oriented , so is not writing about items as “Message Queue Task” , “Maintenance Cleanup Task” , “Maintenance Cleanup Task”, etc. and inside a Data Flow Task arguments as the “Data Mining Query” are not mentioned in the book.

Anyway the book is a very valid introduction to the core tasks that a developer typically does in the everyday work, with some arguments as the DQS Cleansing (the Data Quality functionality of SQL Server 2012) well explained.

Sometimes the book is verbose (some SQL repeated instead of send the reader to a previous page) and sometimes following the samples you can encounter errors (for example a field that cannot be null) , but are minor issues that a developer can easily solve.

Some samples are showed as easily working when instead there are little problems, for example the fact that does not exists an 64 bit driver for Excel (see this previous post) and the connection to mySQL is not simple as showed in the book (origin of another post).

There also other minimal logical errors in the explanation flow that a reader which follows with attention can easily fix.

Arguments as Merge Join Transform. Text Analysis, Fuzzy Transformations , ForEach loops, Asynchronous Script, the entire Deployment chapter, Data Tap, the Restartability and Robustness chapter are very valuable and i learned some very useful techniques for my job.

A very useful chapter is the 14, “Programming SSIS”, in which is explained how to create custom components.

Thw downloadable code is very valuable but i recommend to develop the solutions step by step , so it will be very clear how the things are working.

Categories: Books, SQL Server, SSIS, Vs2010, Vs2012

WMI Event Watcher Task tips

I had a problem with a “inherited” (aka: no one knows something , i must discover what is running and what it does…) IIS 7.5 server with an SQL 2008 instance running Reporting services, SSIS jobs (that i should transfer to another server) where sometimes the processor was at 100% for minutes.

Obviously the users are complaining about the lack of service : is a mission critical server; so a first step was to know how many times the processor was over 80% along the work day.

On the same server was needed to copy files in a directory , where a “watchdog” should read and process when a files was uploaded.

Giving the fact that during the night on this server are running some heavy jobs and the directory watchdog is not needed in the day hours, it was requested that the job surveilling the processor was running only during the day and the file watchdog only the night, in order to avoid unuseful workloads.

I don’t know server management software (as Nagios for example) and similar so i thinked an quick&dirty solution for the processor running at 100% .

The WMI Event Watcher seems perfect for the job , but the SSIS world is very complex and i have discovered some things about the WMI Event Watcher.

The first step is to create from the SQL Server Data Tools a new project , it creates automatically a new .dtsx package , here in the Control Flow we can drap&drop from the SSIS toolbox an WMI Event Watcher:


Double clicking the new object we define the properties, where for the first thing we define a new WMI connection:


The WqlQuerySource is the earth of the WMI procedure, a Wql query is a SQL-like language; for example the query

SELECT * from __InstanceModificationEvent WITHIN 20 WHERE TargetInstance ISA 'Win32_Processor' and TargetInstance.LoadPercentage > 80

gives to us a notification when the CPU use is over 80%

In order to help us to the construction of these queries is better to use some software for help.

The first is the WMI Code Creator from Microsoft.

This software is like a wizard with 4 main tabs where to test the queries, that can be constructed browsing the very huge amount of namespaces and WMI classes .

Naturally is requested an understanding of the WMI structure, this page can be of help.

In our case we use the third tab , “Receive an event”:


Using our example for the CPU use , we use the wizard for the code dinamic construction:


Selecting items in the dropdowns and inserting values (as in figure) we generate the code, in this case our final generated code is :

strComputer = "." 
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2") 
Set objEvents = objWMIService.ExecNotificationQuery _
("SELECT * FROM __InstanceModificationEvent WITHIN 60 WHERE " & _
                    "PreviousInstance ISA 'Win32_Processor'" & _
                    " AND TargetInstance.LoadPercentage > 80")
Wscript.Echo "Waiting for events ..."
Do While(True)
    Set objReceivedEvent = objEvents.NextEvent
    'report an event
    Wscript.Echo "__InstanceModificationEvent event has occurred."
Loop

Clicking on the “Execute Code” button is started an command line :


Is the condition is reached (we drop a file in that directory), is launched an MessageBox with text “__InstanceModificationEvent event has occurred.”

This is a simple case , with the case of the directory watchdog the things goes complicated.

The Event Class in this case is __InstanceOperationEvent, the Target Instance CIM_DirectoryContainsFile, for which we should insert the directory name with the syntax “Win32_Directory.Name='<foldername>'”.

The problem is that the wizard is creating string delimited from ” , and we have the slash char \ (directory separator in ms-dos paths) that must be escaped, in SSIS we escape a single \ with \\\\ (4 characters).

The problem is complex because we must insert string with a mix of delimiters (‘ and “”) , for example my first attempt was:

(“SELECT * FROM __InstanceOperationEvent WITHIN 60 WHERE TargetInstance.GroupComponent = “Win32_Directory.Name=’g:\\\\WMIFileWatcher'””)

But this causes an error launching the code (lacking of a parentesis); note that after we must insert the working code in the WqlQuerySource property of the WMI Event Watcher .

In order to be not driven mad we could correct the generated code (is not readonly) as

Dim strComputer
Dim objWMIService 
Dim varSrc 
Dim objEvents 
' better do declare the variables , even if in VBScript are all  of Variant type
strComputer = "."  ' the local pc name , otherwise a NETBIOS name
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2") 
' below we specify the directory to be watched
varSrc = "g:\WMIFileWatcher"
varSrc = Replace(varSrc, "\", "\\\\")
Set objEvents = objWMIService.ExecNotificationQuery _
    ("SELECT * FROM __InstanceOperationEvent WITHIN 10 WHERE " _
        & "Targetinstance ISA 'CIM_DirectoryContainsFile' and " _
            & "TargetInstance.GroupComponent= " _
                & "'Win32_Directory.Name=""" & varSrc & """'")
Wscript.Echo "Waiting for events ..."
Do While(True)
    Set objReceivedEvent = objEvents.NextEvent
    Wscript.Echo "A file operation has occurred in the directory."
Loop

This works , so we can follow the same approach for our SSIS package.

Note for WITHIN : is the number of seconds between each WMI polling of a directory, a low value causes an high CPU load, an high value could lead to problems if the file in the meantime is changed or deleted: it is necessary to find an adequate value for the specific case.

But the problem with the WMI Event Watcher is related to the event : when it fires our WMI Event Watcher “dies” and the dtsx is terminated.

A first approach could be to insert the Watcher inside a loop:


The loop espression could be


And our dtsx is running forever.

Now we can construct the Wql query using first Script Task linked via a Precedence Constraint to the WMI Event Watcher:


In the Script Task the code is (Vb2010):

Public Sub Main()
    Dim strPathWatch As String
    strPathWatch = "g:\WMIFileWatcher"
    strPathWatch = Replace(strPathWatch, "\", "\\\\")
    Dim strCode As String = "SELECT * FROM __InstanceOperationEvent WITHIN 10 WHERE " _
    & "Targetinstance ISA 'CIM_DirectoryContainsFile' and " _
        & "TargetInstance.GroupComponent= " _
            & "'Win32_Directory.Name=""" & strPathWatch & """'"
    Dts.Variables("User::varScriptSrc").Value = strCode
    Dts.TaskResult = ScriptResults.Success
End Sub

And using the variable varScriptSrc as source of the WMI Event Watcher:


The variable must be , obvious, specified as a read write variable:


But our problem is that we need to stop the procedure at a certain hour, so we change the EvalEspression in

GETDATE() < DATEADD(“Mi”,30,DATEADD(“Hh”,18,(DT_DBDATE)GETDATE()))

What is this , that seems TSQL but is not TSQL? Is an example of the Wql syntax , a SQL dialect that supports the WMI infrastructure.

If this case the condition is true when the system date is over 18:30

How to construct these queries ? In MSDN there is an reference , but we need something to test the Wql queries , we can’t debug the EvalExpression…

So is very useful a software as the Expression Tester Tool , that can be downloaded from Codeplex.

Here we can create our Wql with the treeview wizard or write directly a Wql string and try what happens :


Now that we have a tested Wql query we can use it in our Loop:


But there is a problem : until does not happens that a file is copied in our guarded directory, the dtsx is running forever despite the EvalExpression, which is tested only at the first launch !

We can be resolve the problem using the Timeout property :


Note that is specified an timeout (20 seconds) that must be >= of the WITHIN time in the Wql query (in our sample 10 seconds).

What happens at this point? If a file is dropped at the end of the polling (WITHIN 10) interval the dtsx flows outside the WMI Event Watcher , we can do something with the event via a Script Task, for example, after the WMI Event Watcher:


otherwise is nothing happens (no file dropped in that directory) after 20 seconds the code goes in every case to the Script Task as when a file is dropped .

Here there is the problem : apparently with this approach there is nothing that we can test in order to understand if the Script Task is running because was happened an Timeout or a file was dropped in the surveilled directory.

And beware of the Expressions, you can create something as:


Using some test variable as the varResult of the example , the sample expression is compiled without errors but is absolutely wrong this thing (should be provided an warning…)! the expression serves to calculate the value of the property instead of a fixed value chosen drom the dropdown : in this case the AfterTimeout dropdown


 
 

is no more working (is not possible to change the value) because its value is calculated from the Expression; its result should be one of the enumerated values so the sample expression is a no sense (even if it is correct, from the syntax point of view).

In practice the correct approach is to use the Event Handlers, not a Script Task or other after the WMI Event Watcher:


where we can correctly manage if the WMI Watcher is exited because a timeout or because (with the WMIEventWatcherEventOccurred event) a file was dropped.

In any case (timeout or file dropped) our loop condition is tested at least every 20 seconds (the defined timeout) so the Loop condition is evaluate ; at least 20 seconds after 18:30 our final package:


is terminated; the same approach can be taken for the CPU surveillance.

For SSIS expressions some useful references here and from Microsoft, which has a dedicated blog.

Categories: SQL Server, SSIS, Vs2010

Quick & dirty deploy of a .NET 4 workflow

In a ASP.NET 4 Web Application in Visual Studio 20120 i have added a Workflow in a separate Web Application , with a Web Reference in the main .NET 4 web site to the workflow .

There is an interesting article about to create and deploy Workflows with .NET 4 with the Windows Server App Fabric, but my WF is simple (no persistence, for example) and in my WF is provided a throwing of the errors that can be logged in the main site.

I will use the App Fabric for complex projects, but at the moment the quick & dirty deploy can be done with these steps:

  • Publish the main site
  • Go to the web app of the workflow , righ clic on the project and Publish on the File system
  • Copy the published projects on the separate Web Applications on the production server, remember to change the compilation debug=”true” in “false” in the two web.config
  • In the main web application adding a web reference is generated this piece of configuration under <system.serviceModel>
          <bindings>
          <basicHttpBinding>
            <binding name="BasicHttpBinding_IService" closeTimeout="00:01:00"
              openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"
              allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
              maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536"
              messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
              useDefaultWebProxy="true">
              <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
                maxBytesPerRead="4096" maxNameTableCharCount="16384" />
              <security mode="None">
                <transport clientCredentialType="None" proxyCredentialType="None"
                  realm="" />
                <message clientCredentialType="UserName" algorithmSuite="Default" />
              </security>
            </binding>
          </basicHttpBinding>
        </bindings>
        <client>
          <endpoint address="http://<address>/<site>/<service>.xamlx" binding="basicHttpBinding"
            bindingConfiguration="BasicHttpBinding_IService" contract="wfServiceOperator.IService"
            name="BasicHttpBinding_IService" />
        </client>
    

change the endpoint address for the .xamlx in order to to reach the .xamlx in the other web app, and all is working .

Categories: .NET, Vs2010

Report object Subreports (minitutorial for seasoned “Crystallers”)

After a bad experience with ASP.NET Crystal Reports, i’m returned to use the Report Viewer 2010 for the printing needs of a .NET 4 web site that i’m developing.

Curiously in 12 years of web development is the first time that i have a request of to print something of complex from a web page, so there is a new range of problems.

The first lesson learned on the field is that the better thing to do for the data source is to provide an Object data source: if you try to provide directly a DataSet to the report at design time you can encounter various problems; the worst case is a stored procedure using a SQL Server temp table (the ones that begins with “#”, or “##” if you want that the temp table is recognized from another stored) , in my case with the Report object provided in Visual Studio 2010 i got an syntax error on the name of the temp table.

So i tried Crystal Reports because in my long experience (since 1998) with Crystal used from Visual basic 5/6 i never had problems with stored procedures (sometimes very complex) as data source, but this time with no luck.

In this case the stored is using DTE (Data Table Expressions) on temp tables, and returns a table (relative to Questions, “Domande” in Italian) ; in a class the datatable returned from the stored can be expressed as

public class PrintDomande
{
    public int IdDomanda { get; set; }
    public string TestoDomanda { get; set; }
    public bool RispostaInversa { get; set; }
    public string Dettagli { get; set; }
    public int NumOp { get; set; }
    public double MediaPerDomanda { get; set; }
    public double R1 { get; set; }
    public double R2 { get; set; }
    public double R3 { get; set; }
    public double R4 { get; set; }
    public double R5 { get; set; }
}

The Object data source is another class that reads from the stored and fills a List of the previous class:

public class PrintDomandeList
{
    public List<PrintDomande> GetData(string xmlDoc)
    {
        DataIfAdvices objData = new DataIfAdvices();
        DataTable rstPrintMain = objData.ExecuteStoredDomande(xmlDoc);
        List<PrintDomande> objLisT = new List<PrintDomande>();
        foreach (DataRow objRow in rstPrintMain.Rows)
        {
            objLisT.Add(
                new PrintDomande()
                {
                    IdDomanda = Convert.ToInt32(objRow["IdDomanda"].ToString()),
                    TestoDomanda = Convert.IsDBNull(objRow["TestoDomanda"]) ? String.Empty : objRow["TestoDomanda"].ToString(),
                    RispostaInversa = Convert.ToBoolean(objRow["RispostaInversa"]),
                    Dettagli = Convert.IsDBNull(objRow["Dettagli"]) ? String.Empty : objRow["Dettagli"].ToString(),
                    NumOp = Convert.ToInt32(objRow["NumOp"].ToString()),
                    MediaPerDomanda = Convert.IsDBNull(objRow["MediaPerDomanda"]) ? 0 : Convert.ToDouble(objRow["TestoDomanda"].ToString()),
                    R1 = Convert.IsDBNull(objRow["R1"]) ? 0 : Convert.ToDouble(objRow["R1"].ToString()),
                    R2 = Convert.IsDBNull(objRow["R2"]) ? 0 : Convert.ToDouble(objRow["R2"].ToString()),
                    R3 = Convert.IsDBNull(objRow["R3"]) ? 0 : Convert.ToDouble(objRow["R3"].ToString()),
                    R4 = Convert.IsDBNull(objRow["R4"]) ? 0 : Convert.ToDouble(objRow["R4"].ToString()),
                    R5 = Convert.IsDBNull(objRow["R5"]) ? 0 : Convert.ToDouble(objRow["R5"].ToString())
                }
            );
        }
        return objLisT;
    }
}

Adding a report with the Report wizard


(prepare yourself to a long wait) appears the window of the available sources (another wait time when you change Data source…and when you click Next)


The thing that i hate is that in the next screen i MUST choose a field to be used as grouping expression (but why ?..)

Otherwise you can’t click Next.


At a first sight i’m not understanding the utility of column groups, in every case by clicking next you see a preview


With subtotals


In my case i don’t need subtotals.

After is requested to choose a style


Our report after the last “click Next ” is ready


The columsn can de deleted , added, resized; is not as in Crystal Reports where you drop the field on the Details and eventually you use a Line object as separator: here you must create columns for every field: 2 fields in a column seems a problem.

As in Crystal Reports here you can add a new Detail row, this is done not from a Section Expert as in Crystal but you add a new row


While in Crystal Reports you can create a Formula that can be used more than once, here you apply an Expression to every field by right clicking the field


An expression must be written with the VB.NET syntax, for example we have a boolean field and we want to see a “X” for true values and nothing for false values is applied an expression as

=Iif(Fields!<boolfield>.Value, “X”, ” “):


Could be that there is a manner to create formulas valid for all the report (perhaps the Custom Code property of the Report object?)

The fields can be formatted as in Crystal Reports (as numeric with 2 decimals, for example).

In my case i have a stored procedure that gives me a summary DataTable for the main report , if in the same stored i pass a certain parameter value then is given an detailed dataset; the main ID is the link between the two identical dataset.

For the reporting with no details no problems , instead with a subreport the things went to complex.

I made a copy of the primary report and i have created the subreport:


Note that i have created a Header but in the final report this does not appear, but for now is not a problem.

Another at a first sight shocking discover was that apparently there isn’t a windows as in Crystal Reports where you can see all the report objects , in the toolbox ..nothing


The answer : from the View menu ofVisual Studio 2010 there is the item “Report Data”


By right clicking the datasource (in figure “DataSetDomandeSubRepo”) is possible to redefine the datasource.

The first thing to do for a subreport is to define a Parameter, in our case is the IdDomanda , the autoincrement Primary Key, so i defined


For Available Values and Default Values i leaved “No Default Value”; i have read in some blog that defining a Text data type is better to check “Allow blank value” and “Allow null value”.

In the main report i created a second detail row and here i inserted a subreport from the Toolbox.

The main problem is the free placement, by selecting cells is possible to join them with Merge cells in order to create a space


But is very difficult the objects positioning, while in Crystal Reports is more simple.

Anyway my final report is this :


After inserting the subreport i have linked the subreport (right clic on subreport) created before by simply writing his name … from this point of view Crystal Reports is very better.


The linking of the subreport parameter is done by create a parameter and writing the same name of the parameter defined in the subreport , the value is chosen from the current dataset (discover this simple thing was very difficult…):


At this point the big problem is that Crystal Reports has a preview, here …no preview , you must code a test page.

By placing a ReportViewer control in a page is automatically registered the assembly in the markup, and at runtime is requested an Microsoft Ajax toolkit ScriptManager ; in the page must be enabled the ViewState (sigh…)

<%@ Page Language=”C#” AutoEventWireup=”true” EnableViewState=”true”..

So a simple minimal page is this:

<%@ Page Title="" Language="C#" MasterPageFile="~/TemplateMaster.master" AutoEventWireup="true" CodeFile="TestStampa.aspx.cs" Inherits="Users_TestStampa" %>
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<asp:Content ID="Content1" ContentPlaceHolderID="cphMain" Runat="Server">
    <asp:ScriptManager ID="ScriptManager1" runat="server" EnablePartialRendering="true" EnablePageMethods="true" EnableScriptGlobalization="true" EnableScriptLocalization="true">
    </asp:ScriptManager>
    <br />
    <asp:UpdatePanel ID="UpdatePanel2" runat="server" UpdateMode="Conditional">
        <ContentTemplate>
            <asp:Button ID="cmdStampa" runat="server" Text="Stampa" onclick="cmdStampa_Click" />
            <br />
            <rsweb:ReportViewer ID="rvDomande" runat="server" Font-Names="Verdana" Font-Size="8pt" InteractiveDeviceInfos="(Collection)" WaitMessageFont-Names="Verdana" 
                WaitMessageFont-Size="14pt" Width="100%" Height="100%">
            </rsweb:ReportViewer>
        </ContentTemplate>
    </asp:UpdatePanel>
</asp:Content>

In this case we are enclosed in an asp:Content because the site is using a Master page.

The UpdatePanel is requested otherwise is done a postback at every page change/print/export with a complete page refresh , a possible problem for your page (that should do not make postback, is evil..)

If you use primarily JQuery (i use 1.7.2 version) and BlockUI the Report does not work : is much better a window.open and in this page use only , if possible, the Microsoft Ajax toolkit and no JQuery libraries; in every case no problems with the Dynatree JQuery plugin.

Ok , but how to pass a datasource a runtime ?

I have a button for to launch the print , with some textbox for the parameters, so my code is

protected void cmdStampa_Click(object sender, EventArgs e)
{
    DataIfAdvices objData = new DataIfAdvices();
    string strXmlMain = GetXmlFromParms(true);
    string strXmlPrint = GetXmlFromParms(false);
    rstPrintMain = objData.ExecuteStoredDomande(strXmlMain);
    rstPrint = objData.ExecuteStoredDomande(strXmlPrint);
    rvDomande.Visible = true;
    ReportDataSource rds = new ReportDataSource();
    rvDomande.Reset();
    rvDomande.ProcessingMode = ProcessingMode.Local;
    LocalReport rep = rvDomande.LocalReport;
    rep.Refresh();
    if(txtTIPODETTAGLIO.Text == "ND")
        rep.ReportPath = Server.MapPath("StampaDomandeND.rdlc");
    else
        rep.ReportPath = Server.MapPath("StampaDomande.rdlc");
    rep.DataSources.Clear();
    rds.Name = "DataSetDomande";
    rds.Value = rstPrintMain;
    rep.DataSources.Add(rds);
    if (txtTIPODETTAGLIO.Text != "ND")
        rep.SubreportProcessing += new SubreportProcessingEventHandler(rep_SubreportProcessing);
}

Note that if you don’t have technical problems or really complex data you can use the class as template for the report datasource , at runtime you can use a DataTable that has the same structure of the class (more fast).

I obtain two datatables rstPrintMain and rstPrint (for the subreport) by executing the stored with different XML parameters.

Note that i must supply the exact name for the datasource as defined in the report (“DataSetDomande”).

If the report is requesting details in a subreport then i must define an handler for the SubreportProcessing event.

Obviously i don’t want call the stored for every row of my report , but i called the stored for details once and in the subreport event i filter the dataset provided to the subreport.

The code:

void rep_SubreportProcessing(object sender, SubreportProcessingEventArgs e)
{
    if (rstPrint == null)
        return;
    DataRow[] objRows = rstPrint.Select("IdDomanda = " + e.Parameters[0].Values[0]);
    DataTable rstTemp = rstPrint.Clone();
    foreach (DataRow obJRow in objRows)
    {
        rstTemp.Rows.Add(obJRow.ItemArray);
    }
    e.DataSources.Add(new ReportDataSource("DataSetDomandeSubRepo", rstTemp));
}

Note the syntax for acquiring the parameter value (“.Parameters[0].Values[0]”).

An very interesting behavior that i discovered: in a first attempt i have written a fixed value ( i was not still discovering how to read the parameter) in order to verify the report , obviously the subreports were all equals, then i have tried

((System.Data.DataTable)(((Microsoft.Reporting.WebForms.LocalReport)(sender)).DataSources[0].Value)).DefaultView[intCounter][“IdDomanda”].ToString())

In order to read the primary value.

DefaultView[intCounter] : i have used a counter, starting from 0, and in this routine i wrote a intCounter++ ; in the debugger i have verified that the subreports records were ok for every subreport.

But in the report preview, though in the debugger the datasource for every report was correct, i was seeing for every subreport the first subreport dataset ! (i began to pull my hair…)

Instead using the correct syntax the report is ok: it seems that the reading of the parameter value causes something…


Charting : i inserted a simple diagram for every row without any problem; this feature in the Report Viewer 2010 is complete, there is a lot of possibilities.


In this dialog there is the section Legend where to change the legend text and many other things.

A note : since i’m using the Report object in my .NET 4 project i have random compilation errors as:

Object reference not set to an instance of an object.

And sometimes the compiler is not able to rewrite some dll :you must retry the compilation and then is ok.

Another little trick is requested for the Report Viewer nationalization : at the address http://www.microsoft.com/it-it/download/details.aspx?id=20884 select your language in the dropdown, the page is changed and you can download a file named ReportViewerLP.Exe , installing this on your pc and obvious on the production server the ReportViewer language is changed depending from the browser language (or the language you set from code); on the production server (tipically an Windows 2008 r2) is required to download and install the Microsoft ReportViewer 2010 Redistributable (you can easily find the page with Google).

Passing an DataTable as source the refresh button must be disabled, otherwise there are errors.

<rsweb:ReportViewer ID="rvDomande" runat="server" ... " ShowRefreshButton="false Width="100%" Height="100%">
</rsweb:ReportViewer>

Normally the Report Viewer 2010 works very well , i have tried with Internet Explorer 9, 10, Firefox (Windows, Mac, Ubuntu) , Chrome (Window, Ubuntu) , Safari (Windows, Mac), Opera , Camino (Mac).

Bad surprises instead with Internet Explorer 7 and 8: it seems all ok by previewing the first page , but going to another page of the report the report body is reduced to a thin band.

I have pulled my hair another time… discovering also that in a localized site with Ie 7 in some windows the labels are showed in the base language, in my case English, even if the operating system and the browser are in Italian: you must force by code the localization (the classic flag image..).

In the Webkit based browsers (Chrome, Safari) there were 2 scrollbars (one for the window, one for the report).

Finally after 2 days of experimentations and analisys of the produced html code in the final page i have discovered that with these lines of code in the Page_Load you address the problems of WebKit and Ie7/8:

HttpBrowserCapabilities brObject = Request.Browser;
string strBrowser = ((System.Web.Configuration.HttpCapabilitiesBase)(brObject)).Browser;
string strType = ((System.Web.Configuration.HttpCapabilitiesBase)(brObject)).Type;
if (String.Compare(strBrowser, "chrome", StringComparison.OrdinalIgnoreCase) == 0 || String.Compare(strBrowser, "safari", StringComparison.OrdinalIgnoreCase) == 0)
{
    rvDomande.AsyncRendering = false;
    rvDomande.SizeToReportContent = true;
}
if (String.Compare(strType, "IE7", StringComparison.OrdinalIgnoreCase) == 0 || String.Compare(strType, "IE8", StringComparison.OrdinalIgnoreCase) == 0)
{
    rvDomande.AsyncRendering = false;
    rvDomande.SizeToReportContent = true;
}

But it is needed a final touch : in Ie7/8 the Report was now working in every page but reduced as width also in the first page, even if in the Report markup is specified Width=”100%”; examining with F12 the Html i have noticed that there is a table named ctl00_cphMain_<your report id>_fixedTable wrapping the report: trying to add a style width:100% et voilà , fixed, so i added a css as this:

#ctl00_cphMain_rvDomande_fixedTable{
    width:100%;
}

Only in Internet Explorer browsers are automatically generated buttons for Zoom and Print; but curiously the only browser where the Zoom above 100 is working ok (In ie9 the page is cutted at the top, In ie8 to the bottom) is Ie7! : so in my Report control markup i have added ShowZoomControl=”false” (in every case the customer can export to pdf, Word,Excel: here you can zoom at your will) .

I’m not the best html&css expert and i believe that the things can be done better, but now all is working in every browser i tried.

Final considerations : the Report object has interesting features , for example the Tablix concept , Variables, or Custom code that can be inserted in the report :


But is very hard to find well written documentation with examples about how to use these things.

I hope that in Visual Studio 2012 there is more documentation and the Report object has improvements.

Categories: .NET, Crystal Reports, Vs2010

IIS 7.5 difference between Windows 7 and Server 2008 r2

I think that is not a bad idea to develop directly with a Windows server 2008 R2 as development pc OS, because sometimes you can encounter subtle differences as the one explained in this post.

I’m developing with a HP Notebook (HP quality is a bit degraded from the glorious ’80: my screen after 2 years has some very little scratches caused from the keyboard keys that touches the screen when the notebook is closed …) , with Windows 7 ultimate 64 bit as OS.

IIS is the latest release:


I’m working to an ASP.NET 4 project , where i’m heavily using JQuery, so i have a lot of page methods (no postback! is evil !) called by passing JSON strings , the page method does something and returns to a javascript callback.

Tipically in a db (in this case SQL Server 2012) there a a lot of constraints and checks, that causes an error for example in a update record method.

The catch code in my page methods is always something as (but there are many other magic numbers…):

catch (SqlException ex)
{
    switch (ex.Number)
    {
        case 577:
        case 547:
        case 2627:
        case 2601:
            //Violation of PRIMARY KEY constraints
            objDB = new DbViolations();
            if (objDB.ManagePKFKConstraintsMsgs("MyDbTable", ex.Message) == true)
            {
                ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), objDB.MsgToUser);
                Response.StatusCode = (int)HttpStatusCode.Conflict;
            }
            else
            {
                // case already managed, not useful that the user knows the details
                ... Some logging
                Response.StatusCode = (int)HttpStatusCode.BadRequest;
            }
            break;

There is a routine (ManagePKFKConstraintsMsgs) that verify if the error string contains the name of a check (for example) , if so the function returns a custom string for the user , that tipically is showed in a window.alert; otherwise the better thing to do is a error log and then redirect the user to a custom “Yellow Death Page”.
In my example objDB.MsgToUser can returns something as “The age must be between 0 and 99”, the javascript callback is, for example

function cmdSave_onComplete(xData, status) {
    if (status == "success") {
        $("#frmEditAddCateg").dialog("close");
        $("div#divGridViewCateg").html(xData.responseText);
    } else {
        if (xData.status == 409) {
            //HttpStatusCode.Conflict , was provided a injection of a alert to the user
            $("div#divGridViewCateg").html(xData.responseText);
            return;
        }
        window.location = "../Users/GenericErrorPage.aspx";
    }
}

Note that i’m returning from C# page method code a Response.StatusCode = (int)HttpStatusCode.Conflict , which has value = 409

So in the callback i test the status : if 409 then the responseText is my custom message , to be showed to user with a window.alert which is included in the xData.responseText , among other markup.

On my development pc all ok (this is the first time i have tried this new approach), instead when i have published the site on the production Windows Server 2008 r2 , surprise : in xData.responseText there was the string “The page was not displayed because there was a conflict”, so this was the result:


I have verifyed the Windows Server 2008 r2 IIS version:


Ops… same version of my Win 7… so ?

The answer is that IIS in Windows 2008 R2 overrides by default the HTTP response with its default error management (pages and other) , this default behavior can be fortunately overriden by using the TrySkipIisCustomErrors property of the Response object.

So my C# code now is:

catch (SqlException ex)
{
    switch (ex.Number)
    {
        case 577:
        case 547:
        case 2627:
        case 2601:
            //Violation of PRIMARY KEY constraints
            objDB = new DbViolations();
            Response.TrySkipIisCustomErrors = true;   //  <------------------- new line
            if (objDB.ManagePKFKConstraintsMsgs("MyDbTable", ex.Message) == true)
            {
                ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), objDB.MsgToUser);
                Response.StatusCode = (int)HttpStatusCode.Conflict;
            }
            else
            {
                // case already managed, not useful that the user knows the details
                ... Some logging
                Response.StatusCode = (int)HttpStatusCode.BadRequest;
            }
            break;

And using the sitehosted in Windows Server the result is as expected:


Unfortunately, there are problems also between different versions of Windows server: for example some years ago i had serious problems with date fields with Latvian as browser language ( a website used also from an hospital in Riga) with the pages hosted in a Windows 2003 server; the hosting migrated to Windows 2008 and et voilà , problem disappeared.

Categories: .NET, Vs2010

Rounded corners in Ie9

Ie9 has the support for CSS3 so a div can be rendered with the rounded corners.

A typical definition could be:

.curved {
     border: 1px solid #e1e16d;
    -moz-border-radius: 5px;
    -webkit-border-radius: 5px;
    -khtml-border-radius: 5px;
    border-radius: 5px;
}

This was working in my pages lanched from Visual Studio 2010 which is running as administrator, and from FireFox the same.

The surprise came when i have tried from Ie9 lanched normally , as standard user (my login is not administrator in my pc, even if this measure does not prevents malware…) : no rounded corners !

Launching Ie9 (not from Visual Studio , hitting F5) as administrator ok, the roundes corners are back.

This with Windows 7 64 bit , i have not tried with another Os.

I’m not understanding why in Fixefox launched normally as standard (not admin) user and Ie9 launched as administrator the rounded corners works and not in Ie9 launched as standard user , anyway the trick is to insert the declaration

<meta http-equiv=”X-UA-Compatible” content=”IE=9″ />

In the <head> section

And the rounded corners are rendered in Ie9 launched as standard user.

Content type in my site is

<meta http-equiv=”content-type” content=”text/html; charset=ISO-8859-1″>

And DOCTYPE

<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01//EN” “http://www.w3.org/TR/html4/strict.dtd“>

Categories: .NET, Vs2010