Log in to web service programatically

Web service calls that are exposed to the public but which you don't want open to public calls can be secured using directory security on the directory IIS uses. This is only a valid method if the call is made over a controlled network since basic authentication password can be snooped.

Microsoft has a great page on how to use it, so I will only summarize

               NavCustomer.CustomerService navCustService = new NavCustomer.CustomerService();
               CredentialCache credentialCache = new CredentialCache();
               NetworkCredential credentials = new NetworkCredential(
                   ConfigurationSettings.AppSettings["WebServiceUserName"],
                   ConfigurationSettings.AppSettings["WebServicePassword"],
                   ConfigurationSettings.AppSettings["WebServiceDomain"]);
               credentialCache.Add(new Uri(navCustService.Url), "Basic", credentials);
               navCustService.Credentials = credentialCache;

IIS 7.0 Application Pools

IIS 7.0 has a new model for application pools.  If you are migrating from IIS 6.0, you need to read this and do what it says to get your application to work in Integrated mode.  There is a classic mode that is supported but not recommended.

Another good article on the topic is here.

Once I understand what is going on better, I will update this post.

Setting up a Windows 2008 server for FTP

Windows 2008 Server Web Edition has some extremely restrictive security settings, and some IIS setup problems at present.

If you install IIS, and enable FTP in the IIS manager, you will see the following in the Windows Firewall inbound rules:  

You may think that you are all set.  You will find though that you can connect to the server, but not browse the directory or modify files (although you can create files for some reason, but they end up as zero bytes).  This is because the TCP port is changed after connection is made and the port it is changed to is blocked by the firewall.

I found this post which instructs you to enter the following commands via and command prompt.  Note that you must select Run as Administrator or you will get a permissions error:

netsh advfirewall set global StatefulFtp enable

Table Wrapper Classes

We use Subsonic to generate DAL classes for our main ASP.NET project, and we are transitioning to LINQ where possible.  One thing we haven't figured out yet is how to return a collection of LINQ "rows" from a web method, and Subsonic is apparently having problems with complex data too, so that means we need to create our own classes to return complex data or collections of data from a web method.  I have written collection classes before as well as DAL classes but it is SUCH a pain to create all the public variables, public properties and all their get/set methods.  I found this on SqlServerCentral today and will try it out next time I have to create a wrapper class for a table:

CREATE PROCEDURE usp_TableToClass
/*
Created by Cade Bryant.
Generates C# class code for a table
and fields/properties for each column.

Run as "Results to Text" or "Results to File" (not Grid)

Example: EXEC usp_TableToClass 'MyTable'
*/

@table_name SYSNAME

AS

SET NOCOUNT ON

DECLARE @temp TABLE
(
sort INT,
code TEXT
)

INSERT INTO @temp
SELECT 1, 'public class ' + @table_name + CHAR(13) + CHAR(10) + '{'

INSERT INTO @temp
SELECT 2, CHAR(13) + CHAR(10) + '#region Constructors' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 3, CHAR(9) + 'public ' + @table_name + '()'
+ CHAR(13) + CHAR(10) + CHAR(9) + '{'
+ CHAR(13) + CHAR(10) + CHAR(9) + '}'

INSERT INTO @temp
SELECT 4, '#endregion' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 5, '#region Private Fields' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 6, CHAR(9) + 'private ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
ELSE 'object '
END + '_' + COLUMN_NAME + ';' + CHAR(9)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 7, '#endregion' +
CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 8, '#region Public Properties' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 9, CHAR(9) + 'public ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
ELSE 'object '
END + COLUMN_NAME +
CHAR(13) + CHAR(10) + CHAR(9) + '{' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'get { return _' + COLUMN_NAME + '; }' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'set { _' + COLUMN_NAME + ' = value; }' +
CHAR(13) + CHAR(10) + CHAR(9) + '}'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 10, '#endregion' +
CHAR(13) + CHAR(10) + '}'

SELECT code FROM @temp
ORDER BY sort

Using Telerik RadGrid - Codebehind - Subsonic

The following code is required in to populate the grid using subsonic:

        using Telerik.Web.UI;
...
        protected void rgCatalogs_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
        {
            Query qry = SalesCatalog.CreateQuery();
            rgCatalogs.DataSource = qry.ExecuteDataSet();
        }

Using Telerik's RadGrid - aspx code

The following html code is needed to create a table with 4 visible columns:

    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
    <telerik:RadGrid ID="rgCatalogs" runat="server" GridLines="None"  
      AutoGenerateDeleteColumn="false" AutoGenerateEditColumn="false" AutoGenerateColumns="false"
      OnNeedDataSource="rgCatalogs_NeedDataSource" >
        <MasterTableView DataKeyNames="rid">
        <Columns>
            <telerik:GridBoundColumn HeaderText="rid" UniqueName="rid" DataField="rid" Visible = "false" ReadOnly ="true"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn HeaderText="Description" UniqueName="Description" DataField="Description" ReadOnly ="false"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn HeaderText="StartDate" UniqueName="StartDate" DataField="StartDate" ReadOnly ="false"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn HeaderText="EndDate" UniqueName="EndDate" DataField="EndDate" ReadOnly ="false"></telerik:GridBoundColumn>
            <telerik:GridHyperLinkColumn HeaderText="ATS"  Text="ATS" UniqueName="ATS" DataNavigateUrlFields="rid" DataNavigateUrlFormatString="~/ATS/CatalogATS.aspx?catid={0}" /> 
        </Columns>
        <RowIndicatorColumn Visible="False">
        <HeaderStyle Width="20px"></HeaderStyle>
        </RowIndicatorColumn>

        <ExpandCollapseColumn Visible="False" Resizable="False">
        <HeaderStyle Width="20px"></HeaderStyle>
        </ExpandCollapseColumn>

        <EditFormSettings>
        <PopUpSettings ScrollBars="None"></PopUpSettings>
        </EditFormSettings>
        </MasterTableView>
    </telerik:RadGrid>

Setting up SQL server 2008 (to host Dynamics NAV)

I installed SQL 2008 Developer edition because the SQL 2005 Express edition I had been using isn't able to be accessed from web applications running outside the VS IIS instance and I needed to test an IIS hosted web service application.

The install took nearly 2 hours!  Becuase I was installing SSIS, SSRS, and the other tools.  It required an upgrade to .Net 3.5 SP1, VS 2008 SP1, in addition to 2.5 gb of HD space.

After the install, SQL 2005 Express could no longer access the databases so I had a momentary freak-out until I could mount them into SQL 2008, at which point I uninstalled SQL 2005 Express.

Upon attempting to connect from Dynamics NAV, I was reminded of the SQL setup you have to do for Dynamics NAV.  I always seem to forget that, and I am not the only one as I have been onsite to implement behind another analyst a few times and found that the setup hadn't been completed.  Here are the SQL modifications required for Dynamics NAV:

You have to set a trace flag by adding "-T 4616" to the startup parameter string:

http://tricksnavision.blogspot.com/2007/10/error-traceflag-4616-at-start-nav-in.html 

You have to add two extended stored procedures (xp_ndo_enumusersids and xp_ndo_enumusergrous) to the master database and grant execute permissions to the public role  as explained here:

http://blogs.msdn.com/nav_developer/archive/2008/11/05/basic-sql-creating-extended-stored-procedure-xp-ndo-dll.aspx

As a side note, all clients from 4.0 SP3 and later are compatible with SQL 2008, so there is no reason to use SQL 2005 with new installs.

Inserting into SQL table with Identity field

Sometimes you want to insert records into a table that has an Identity field but you need to insert a value into that field (normally the system assigns identity values).

The following code allows you to do that:

SET IDENTITY_INSERT BasicAttribute ON

insert into BasicAttribute (rid, typeid, code, description) values (0,0,'DEFAULT','Default')

SET IDENTITY_INSERT BasicAttribute OFF

How to add a foreign key constraint in t-sql

The WITH NOCHECK clause is not recommended since future table updates could fail as the contstraint is re-validated, but I needed it to get it to work on some tables and will have to deal with the missing data later.

ALTER TABLE dbo.SKU_Price WITH NOCHECK ADD CONSTRAINT
    FK_SKU_Price_PriceGroupID FOREIGN KEY
     (
        PriceGroupID
     ) 
     REFERENCES dbo.SKU
     (
         rid
     ) 
    ON UPDATE  NO ACTION 
    ON DELETE  NO ACTION 

Thinking in Layers

When creating a web application, it is very important to think in Layers.  Your web application may only have 1 layer because it is so simple, but if you plan to have more than one developer, or permit web-service access to the applicaiton, or just plan on allowing the application to grow, you should have 3-5 layers:

1. Client - Scripts that enhance the usability of the site and run on the client interface
   
2. Presentation - Objects that display the data in HTML or in a Winform
   
3. Communication - Objects that expose or consume services so the application can be spread across multiple physical or virtual servers.
   
4. Business Logic - Objects that contain the working data and enforce the business rules.
   
5. Data Access - Objects that interact with the database
   
6. Data - The database server
   

The manditory layers for a web application are Presentation, and Data but if you neglect to include layers in between them you will probably regret it.  

If you have a single object that interacts with the web page, enforces the business rules and interacts with the database:

• What are you going to do when you need to expose that information via a web service?
  
• What will you do if you need to optimize for performance and cache the information in a session variable?
  
• Or what if you need to add another web server to handle the volume of user demand?
  

Formatting Blogger posts with c# code in them

Very cool tool right here: http://www.manoli.net/csharpformat/format.aspx but you have to change the page header to reference a CSS file but I am not sure how to upload the CSS file so I entered it in to the HTML template manually.

Wordpress has a cool plugin that makes this even easier.

How to serialize an object to/from xml

I copied the below almost entirely from this page but didn't want to risk it going away before I had it committed to memory, so read that page not mine! I am not sure why you just wouldn't create a method on the object to do that but all the examples I have seen use a separate void class.
[csharp]

//Serialize:
static public void SerializeToXML(Movie movie)
{
  XmlSerializer serializer = new XmlSerializer(typeof(Movie));
  TextWriter textWriter = new StreamWriter(@"C:\movie.xml");
  serializer.Serialize(textWriter, movie);
  textWriter.Close();
}

//Deserialize:
static List<Movie> DeserializeFromXML()
{
   XmlSerializer deserializer = 
      new XmlSerializer(typeof(List<Movie>));
   TextReader textReader = new StreamReader(@"C:\movie.xml");
   List<Movie> movies; 
   movies = (List<Movie>)deserializer.Deserialize(textReader);
   textReader.Close();

   return movies;
}

How to create a collection of classes in .net

To create a collection, capable of:

being used in a foreach loop,
being returned from a webmethod

etc, you need to create a class that inherits CollectionBase and iEnumerable.

Both of these classes require you implement several methods, and you also need to create an Enumerator class, see the code below and put your class in instead.

Note the default following: public OrderLine this[int i]{...} is required to automatically return each element of the collection when used as a return value for a webmethod.

using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;

namespace OfficeOps.SalesOrdering.SalesOrder
{
    public class OrderLines : CollectionBase, IEnumerable
    {

        public void Add(OrderLine ordLine)
        {
            List.Add(ordLine);
        }
        public void Remove(int index)
        {
            if (index > Count - 1 || index < 0)
            {
                throw new Exception("Index out of bounds");
            }
            List.RemoveAt(index);
        }

        public OrderLine this[int i]
        {
            get { return (OrderLine)List[i]; }
        }
        public class OrderLineEnumerator : IEnumerator
        {
            int nIndex;
            OrderLines collection;
            public OrderLineEnumerator(OrderLines coll)
            {
                collection = coll;
                nIndex = -1;
            }
            public void Reset()
            {
                nIndex = -1;
            }
            public bool MoveNext()
            {
                nIndex++;
                return (nIndex < collection.Count);
            }
            public OrderLine Current
            {
                get
                {
                    return ((OrderLine)collection[nIndex]);
                }
            }
            object IEnumerator.Current
            {
                get
                {
                    return (Current);
                }
            }
        }
        public new OrderLineEnumerator GetEnumerator()
        {
            return new OrderLineEnumerator(this);
        }
        IEnumerator IEnumerable.GetEnumerator()
        {
            return GetEnumerator();
        }

    }

}

SSIS is Buggy

I spent 2 days creating a package to move data from a Dynamics NAV SQL database to our ActionPak database and got it working.

2 weeks later I decided to break the single package into multiple smaller packages since the execution frequency was not the same on all of them only to find that one of the objects would crash Visual Studio if I tried to delete it.

I had to copy and paste all the variables and other objects out into new packages, then re-assign the data sources on all of them. Then I had to re-write the object that was crashing visual studio by hand. That cost 8 hours.

The offending object had a data viewer on one of the connectors that was causing the crash so from now on I am not leaving any viewers in my projects, I will delete them before saving and exiting, and I am going to make my packages as small as possible so I don't loose 100% of my work if one glitch like that happens again.

SSIS is also very slow to refresh the screen, and the properties are very painful to edit. I hope the next release is a lot smoother.

SSIS Scripts

I have been working with SSIS for a couple weeks, and while I understand a few of the common tools I am really a novice in many areas.

Today I needed to prevent the Available Quantity on inventory from going negative since we don't want to expose that to users of the site I am working on. There are no build in commands to do that in the "Derived Column" object, so I had to use a script object.

For some reason VB is the only language supported, but it was really easy. After identifying which columns were available and whether they were read-only or not, the only code I had to write was:Row.AvailableQty = Max(Row.AvailableQty, 0).

The entire script follows, all but one line was generated by the system.

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Row.AvailableQty = Max(Row.AvailableQty, 0)

End Sub

End Class

SQL Case

Select
CASE [World Wide Service]
WHEN 1 then [Shipping Agent Code] + '-W'
ELSE [Shipping Agent Code]
end,
Description, [World Wide Service] from [COMPANYNAME$E-Ship Agent Service]

LINQ:Update record

DCMS.net.LINQClasses.DCMSDataClassesDataContext db = new DCMS.net.LINQClasses.DCMSDataClassesDataContext();
var content =
(from cont in db.Contents
where cont.contentID == Convert.ToInt32(editedItem.GetDataKeyValue("contentID").ToString())
select cont).First();

//Custom class to load values into class and apply business logic
ContentMgr.LoadContentFromHash(content, newValues);

//To assign the values directly:
//
// content.contentName = "Content Name";
//
try
{
db.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}

LINQ: Delete single record

DCMS.net.LINQClasses.DCMSDataClassesDataContext db = new DCMS.net.LINQClasses.DCMSDataClassesDataContext();
var query = from content in db.Contents
where content.contentID == Convert.ToInt32(dataItem["contentID"].Text)
select new
{
content.contentID,
};
if (query.Any())
{
var content =
(from cont in db.Contents
where cont.contentID == Convert.ToInt32(dataItem["contentID"].Text)
select cont).First();
db.Contents.DeleteOnSubmit(content);
try
{
db.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
}

LINQ:Query to data source on grid

Here is an example LINQ query that populates a gridview:

DCMS.net.LINQClasses.DCMSDataClassesDataContext db = new DCMS.net.LINQClasses.DCMSDataClassesDataContext();
var query = from content in db.Contents
where content.resourceID == Convert.ToInt32(HttpContext.Current.Cache.Get("FAMILY_RESOURCEID"))
select new
{
content.contentID,
content.resourceID,
content.contentName,
content.contentLastName,
content.contentFileName,
content.contentDetails,
content.contentModDate,
content.contentImage1
};
rgFamily.DataSource = query;

What I am listening to

I love to listen to music when coding. I can't do that in the office because if I play the music over the speakers it is very distracting to others, and if I wear headphones I am always having to remove them to respond to the constant minor comments and questions (which I don't really mind, but it is a hassle when headphones are on).

Fortunately I can work from home whenever I want so on the couple days a week that I do I listen to a really great sound system that was bumped from the living room when I upgraded the home theater system.

Today I am listing to One Republic "Dreaming Out Loud".

Backslashes in SQL Queries

I keep forgetting this one so I better write it down:

Brackets ([]) work on string constants (forgive the non sql terminology) as well as fieldnames.

When passing a fully qualified domain name into a sql query 'domain\userid', the backslash will cause errors and, at lease in the SQL Management studio, it can not be escaped via double-backslash 'domain\\userid'.

To pass a backslash in, surround the string in brackets inside the ticks: '[domain\userid]'