SQL Server Example of Updatable and Insertable View from Multiple Tables

DROP TABLE HostStatus
GO
DROP TABLE HostStatusListName
GO

CREATE TABLE HostStatusListName (
id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Name nvarchar(50) UNIQUE NOT NULL
)
GO

CREATE TABLE HostStatus (
id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
HostStatusListNameID int NOT NULL REFERENCES HostStatusListName(id),
/* Hostname nvarchar(50) REFERENCES Host,
ProductName nvarchar(50) REFERENCES ProductFamily,
Environment nvarchar(50) REFERENCES Environment, */
HostID int unique REFERENCES Host,
ProductID int REFERENCES ProductFamily,
EnvironmentID int REFERENCES Environment,
IsInActiveDirectory bit NOT NULL,
IsInInventoryDB bit NOT NULL,
ManagementIP nvarchar(50),
ProductionIP nvarchar(50),
Reachable bit NOT NULL,
ReachableDate datetime NOT NULL DEFAULT getdate(),
ServerModel nvarchar(50)
)

DROP VIEW vHostStatus
GO
CREATE VIEW vHostStatus AS

SELECT hs.id, Name, Hostname, ProductName, Environment, IsInActiveDirectory, IsInInventoryDB,
ManagementIP, ProductionIP, Reachable, ReachableDate, ServerModel FROM HostStatus hs
LEFT OUTER JOIN HostStatusListName hsln ON hs.HostStatusListNameId = hsln.id
LEFT OUTER JOIN Host h ON hs.HostId = h.Machineid
LEFT OUTER JOIN ProductFamily pf ON hs.ProductID = pf.ProductID
LEFT OUTER JOIN Environment e ON hs.EnvironmentID = e.EnvironmentID

GO

DROP TRIGGER tr_vHostStatus_IO_U
GO
CREATE TRIGGER tr_vHostStatus_IO_U ON vHostStatus INSTEAD OF UPDATE AS
UPDATE HostStatus SET
HostStatusListNameid=(SELECT TOP 1 id FROM HostStatusListName hsln WHERE hsln.Name=i.Name),
Productid=(SELECT top 1 productid FROM ProductFamily pf where pf.ProductName=i.ProductName),
Hostid = ( SELECT TOP 1 Machineid FROM Host h WHERE h.Hostname=i.Hostname ),
Environmentid = ( SELECT TOP 1 Environmentid FROM Environment e WHERE e.Environment=i.Environment)
FROM inserted i
INNER JOIN HostStatus h on h.id=i.id
GO

DROP TRIGGER tr_vHostStatus_IO_I
GO
CREATE TRIGGER tr_vHostStatus_IO_I ON vHostStatus INSTEAD OF INSERT AS
DECLARE @nameid as int
DECLARE @productid as int
DECLARE @machineid as int
DECLARE @environmentid as int
DECLARE @IsInActiveDirectory as bit
DECLARE @IsInInventoryDB as bit
DECLARE @ManagementIP as nvarchar(50)
DECLARE @ProductionIP as nvarchar(50)
DECLARE @Reachable as bit
DECLARE @ReachableDate as datetime
DECLARE @ServerModel as nvarchar(50)

select top 1 @nameid=hsln.id FROM HostStatusListName hsln INNER JOIN inserted i ON hsln.Name=i.Name
select top 1 @productid = productid FROM ProductFamily pf INNER JOIN inserted i on pf.ProductName=i.ProductName
select top 1 @machineid= machineid FROM Host h INNER JOIN inserted i on h.Hostname=i.Hostname
select top 1 @environmentid = @environmentid FROM Environment e INNER JOIN inserted i on e.Environment=i.Environment
select @IsInActiveDirectory=IsInActiveDirectory, @IsInInventoryDB =IsInInventoryDB ,
@ManagementIP = ManagementIP, @ProductionIP = ProductionIP, @Reachable=Reachable,
@ReachableDate = ReachableDate, @ServerModel=ServerModel FROM inserted

INSERT INTO HostStatus (
HostStatusListNameid, Productid,
Hostid, Environmentid,
IsInActiveDirectory, IsInInventoryDB, ManagementIP, ProductionIP,
Reachable, ReachableDate, ServerModel )
VALUES ( @nameid, @productid,
@machineid, @environmentid,
@IsInActiveDirectory, @IsInInventoryDB, @ManagementIP, @ProductionIP,
@Reachable, @ReachableDate, @ServerModel
)
GO

INSERT INTO HostStatusListName (Name) VALUES (‘Etime’)
INSERT INTO HostStatusListName (Name) VALUES (‘Ehrms-unix’)
INSERT INTO HostStatusListName (Name) VALUES (‘Ehrms-windows’)
Insert INTO vHostStatus (id, Name,Hostname,ProductName, Environment, IsInActiveDirectory, IsInInventoryDB, ManagementIP,ProductionIP,Reachable,ReachableDate, ServerModel)
VALUES (1,’Etime’,’test’,’Etime’,’P’,0,0,’172.30.1.1′,’172.30.1.1′,1,getdate(),’some server’ )

SELECT * from vHostStatus

SELECT * from HostStatusListName

Mono ASP.NET2.0 TODO and NEEDS

Trying to do some ASP.NET 2.0 in Mono and immediately here are things with which the Visual Studio 2005 environment helped.

  1. I had a missing quote. I typed if ( c.ID == “form) … oops there is a trailing quote.
    XSP2’s error message was less than obvious. I’m sure much of it was because I was tired. Unfortunately I do most of my Mono development after working a full time job all day.

    Ok so it does say newline in constant and expecting ‘;’… I guess I was REALLY tired.

  2. Not critical but I’m using xhtml and my html tag didn’t have the xmlns attribute. Studio 2005 not only caught this, but it let me autocomplete this. Now this is really more of a roll for tidy than for another mono tool, but maybe xsp2 could have a debug mode which would run output through tidy!
  3. My web.config file had a < pages styleSheetTheme="SmokeAndGlass" / > tag, but the style didn’t exist.
  4. My asp:Label tag was missing runat server.
  5. My web.config defined a roleManager with a provider using SqlRoleProvider and a connectionStringName which was not defined.
  6. ASP.NET 2.0 couldn’t/wouldn’t load the .dll from the bin directory which has my custom provider. I’m not sure if this is because my dll isn’t strongly signed, is off a network share, or some other reason….HOLY FSCKING TYPO… I typed Provder instead of Provider. I really suck.
  7. Now I know that Mono is missing these things from browsing the classes. Seeing Studio 2005 handle the uncaught exceptions for a Web Application is AMAZING! Excellent debugging, especially since I’m debugging not a Web Application directly, but my custom MembershipProvider. Truely amazing! I’ve never debugged a web application like this.

A Kid at Christmas : Visual Studio 2005

I feel like a child, tearing into presents at Christmas time.

I’m installing Visual Studio 2005.

Thank Microsoft for MSDN downloads. Thank my employer for MSDN subscriptions. Thank Sweden for Bo Branten, maker of filedisk.sys so I could install this dvd image without burning it.

I think I’m coming down with an illness. I should be getting lots of rest this weekend and wouldn’t you know it, this happens. I’ll be building ASP.NET 2.0 websites. I’ll be seeing what is new in Windows Forms. I’ll be making custom collection classes in one line of code rather than 100 thanks to Generics. I’ll be wondering why they changed some things, and wondering what important things they changed that I don’t yet know about. I’ll be a total nerd.

All of this shell be done this weekend after I attend the co-op party my wife is dragging me to in the heart of frat houses in Ann Arbor. I’d complain, but it really was fun last year.

So much to do, So little time. PgsqlMembershipProvider

Started working on a postgresql membership provider for ASP.NET 2.0 that I hope to use with Mono.

Two great resources: http://msdn2.microsoft.com/en-us/library/6tc47t75 and http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/ASPNETProvMod_Prt1.asp

Haven’t got to testing it yet, but I at least see the big picture for the MembershipProvider base class. Hopefully I’ll get to RoleProvider and some others.

Proper Data Abstraction in .NET and Mono Applications

If you are new to .NET development, or especially Mono development you are probably wondering how to translate all of those MSDN SqlClient examples to the database that you use. Maybe you are just testing things out and using Access files, or maybe you are an SQL Lite junking on Linux and dabbling with Mono, or maybe you are a PostgreSQL fan and want to access it from Windows and Linux. For all of these cases, the rules are the same. IT DOES NOT MATTER.

I suppose I have a big gripe with the MSDN Documentation Library and the .NET Framework SDK Documentation, and even the documentation in Monodoc to a lesser extent (Lesser because there is rarely any examples there). These documentation sources don’t have good examples on how to use the Framework Data classes they way they were intended.

For Example…


public void ReadMyData(string myConnString) 
{
   string mySelectQuery = "SELECT OrderID, Customer FROM Orders";
   SqlConnection myConnection = new SqlConnection(myConnString);
   SqlCommand myCommand = new SqlCommand(mySelectQuery,myConnection);
   myConnection.Open();
   SqlDataReader myReader = myCommand.ExecuteReader();
   try 
   {
     while (myReader.Read()) 
     {
       Console.WriteLine(myReader.GetInt32(0) +
 ", " + myReader.GetString(1));
     }
    }
    finally 
    {
    // always call Close when done reading.
    myReader.Close();
    // always call Close when done reading.
    myConnection.Close();
    }
 }

Here, each instance of a type is tied directly to the library which talks to the database, but glancing over the Documentation it is easy to notice that SqlConnection is implemented from a more general interface called IDbConnection. SImilarly SqlCommand from IDbCommand and SqlDataReader from IDataReader.

Using these Interfaces, the instantiation looks more like this:


   IDbConnection myConnection = new SqlConnection(myConnString);
   IDbCommand myCommand = 
                  new SqlCommand(mySelectQuery,myConnection);
   myConnection.Open();
   IDataReader myReader = myCommand.ExecuteReader();

So far we have done nothing but lower the number of points which we would have to change if we decided to change from using System.Data.SqlClient to System.Data.Odbc or another provider. A savvy wielder of regular expressions would point out that if I needed such a change, even across a large number of files, a regular expression could be formulated to make the change for me. I’ll concede to that. (I’ll also not elaborate that such a regex would probably be difficult to construct and those who would try, like me, are probably begging for trouble.) I’ll counter with one big difference.

The types used to instantiate these objects and indeed even the assembly used to instantiate these objects can easily be changed at runtime. You could easily use app.config, or another mechanism of your choice to move from SQL Server to PostgreSQL at runtime, without compiling, or even without stopping your running application.

Programmers familiar with Microsoft’s Data Access Application Blocks may be thinking this is obvious, and of course DAAB does this, but I’ll be quick to counter than DAAB as shipped in the latest Patterns and Practices Enterprise Application Blocks doesn’t include OleDb, ODBC, or any drivers other than SQL Server, Oracle, and DB2. DB2 needs a separate download to work. Also limiting is the license surrounding DAAB. DAAB is open source with a specific line item in the license not allowing applications which use DAAB to require derivative works to be open source. In other words, no GPL allowed. That drawback and the overall size and complexity(admittedly it isn’t THAT big or bad) lean me to steer away from using DAAB.

However, there is a far smaller, simpler, and more powerful-for-the-size library included in Mono! The Mono.Data assembly contains a Mono.Data namespace (surprise!). Within the namespace is 6 classes. One of them is a Enumerator. One is a configuration section handler. That leaves four classes to investigate. DataTools is a class with static members. In 2.0 I think this would be a static class. It has two function which have two and three overloads respectively. AddParameter adds a Parameter to an IDbCommmand. FillDataSet does… well… I’ll let you guess. The remaining classes are where the meat is. They are Provider, ProviderCollection, and ProviderFactory. As the names suggest they all revolve around Provider.

It is probably easiest to show some code:


   IDbConnection conn = ProviderFactory.CreateConnectionFromConfig("CmDbStr");
   conn.Open();
   IDbCommand idc = conn.CreateCommand();
   idc.CommandText = mySelectQuery;
   IDataReader reader = idc.ExecuteReader();

As you can see, the only class that is actually called is a Factory Method from ProviderFactory. This returns a Provider instance. This instance then does the rest of the work. So what about which classes and assemblies are utlimately used? Well this is handled (in this case) in the app.config file. The CreateConnectionFromConfig Method reads settings from app.config which look like this:


< ?xml version="1.0" encoding="utf-8" ?  >
< configuration >
	< configsections >
	< sectiongroup name="mono.data" >
			< section name="providers" type="Mono.Data.ProviderSectionHandler,Mono.Data" / >
		< /sectiongroup >
	< /configsections >
	< appsettings >
		< add key="CmDbStr" value="factory=System.Data.SqlClient;server=MySqlServer;database=MyDataBase;uid=MyDBUser;pwd=1234" / >
	< /appsettings >
	< mono .data >
		< providers >
			< provider name="System.Data.SqlClient" connection="System.Data.SqlClient.SqlConnection"
			adapter="System.Data.SqlClient.SqlDataAdapter" command="System.Data.SqlClient.SqlCommand"
			description="Microsoft Sql Server Provider"
			assembly="System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/ >
			< provider name="System.Data.OleDb" connection="System.Data.OleDb.OleDbConnection" 
			adapter="System.Data.OleDb.OleDbDataAdapter" command="System.Data.OleDb.OleDbCommand"
			assembly="System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
			description="OLEDB" / >
			< provider name="System.Data.Odbc" connection="System.Data.Odbc.OdbcConnection" 
			adapter="System.Data.OleDb.OdbcDataAdapter" command="System.Data.Odbc.OdbcCommand"
			assembly="System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
			description="ODBC"/ >
			< provider name="Mono.Data.TdsClient" connection="Mono.Data.TdsClient.TdsConnection" 
			adapter="Mono.Data.TdsClient.TdsDataAdapter" command="Mono.Data.TdsClient.Command"
			assembly="Mono.Data.TdsClient" / >
			< provider name="Mono.Data.MySql" connection="Mono.Data.MySql.MySqlConnection" 
			adapter="Mono.Data.MySql.MySqlDataAdapter" command="Mono.Data.MySql.MySqlCommand"
			assembly="Mono.Data.MySql" / >
			< provider name="Mono.Data.PostgreSqlClient" 
			connection="Mono.Data.PostgreSqlClient.PgSqlConnection" 
			adapter="Mono.Data.PostgreSqlClient.PgSqlDataAdapter"  command="Modo.Data.PostgreSqlClient.PgSqlCommand"
			assembly="Mono.Data.PostgreSqlClient" / >
			< provider name="Mono.Data.SqliteClient" 
			connection="Mono.Data.SqliteClient.SqliteConnection" 
			adapter="Mono.Data.SqliteClient.SqliteDataAdapter"  command="Mono.Data.SqliteClient.SqliteCommand"
			assembly="Mono.Data.SqliteClient" / >
			< provider name="Mono.Data.SybaseClient" 
			connection="Mono.Data.SybaseClient.SybaseConnection"  command="Mono.Data.SybaseClient.Sybase.Command"
			adapter="Mono.Data.SybaseClient.SybaseDataAdapter" 
			assembly="Mono.Data.SybaseClient" / >
		< /providers >
	< /mono >
< /configuration >

The Provider class actually instantiates these using Type.GetType() with a string composited with type names and assembly names from the providers section. This means the limits of Type.GetType() apply here. The assembly must either be registered in the GAC, or be in the applications working directory. (More correctly the BaseDirectory of the current AppDomain)

Remember all that junk about DAAB licensing? Well, Mono.Data is part of Mono. Mono is licensed under the… hahaha… you thought I was going to type GPL didn’t you? It is not. It turns out Mono is licensed under the MIT license. Miguel explains why in a recent interview. But this license means you can pretty much do whatever you want with it.

One of the biggest benefits I found in using Mono.Data over DAAB is that the Providers expose the DataAdapter Interface in a bit nicer way. The one common option which isn’t handled by Mono.Data, because it isn’t a required part of an ADO.NET provider is the commonly used Command Builder. Unfortunately SqlCommandBuilder and ODBCCommandBuilder are specific helper classes and not part of a standard “command builder” interface or required implementation for an ADO.NET Provider. Fortunately building commands ones self is straightforward, and since best practices say that most of the commands should be stored procedures anyway, the Command Builder isn’t useful except for those quick and dirty programs.

For an example of another Data access layer check out this code project article http://www.codeproject.com/vb/net/data_access_layer.asp.

To see just how simple Mono.Data is, check out the source in viewcvs. http://svn.myrealbox.com/viewcvs/trunk/mcs/class/Mono.Data/

Just remember that the Data Access layer of your applications can either bind you to a particular database, or it can free you from the limits of one particular database. I recommend choosing freedom whenever possible.

Desktop Batch Processing

I stumbled across this old paper by Jim Gray and Chris Nyberg. I’m not sure what year it was written, but it looks like it was the mid ninties.

http://research.microsoft.com/users/Gray/DeskTopBatch.doc

It is always fun and interesting to go back and read things which speculated on the near future and see which parts were correct, which parts were just wrong, and which parts can be considered true, but came about in ways different than what the author suggested, or hinted. The last category is my favorite.

I was searching for a batch processing engine of sorts. In all honesty I wasn’t sure what I was looking for, I only know that if something was out there that would help me, I wanted to use it. This was one of those google hit distractions that was completely worthwhile.

I found section 3.2 particularly interesting “PC and UNIX File Systems are Improving”. It seems almost none of the things mentioned in this section have come to bear. Even the disk throughput numbers are about the same. Hopefully somework will be done in this area over the next few years.

Remeber the milk – not just another todo list

I’ve been using http://www.rememberthemilk.com. Like most todo lists (even paper) you have to remember to look at them, but once you do, they work well.

Remember the Milk works better than any I’ve used on paper or computer. The ability to subscribe to your todo lists via RSS is pretty cool, and you can send todo items to other users or just plain share them with other users. I just don’t know anyone else using the service.

The UI is absolutely beautiful. AFAICT it is all AJAX, which is good for the most part, unless I want to use my browsers back button. The beautiful UI is FAST. I guess that speed is what comes with everything running on the client and all server calls running asyncronously in the background. Not having to wait for the server is pretty cool.

I was getting InvalidCastException when referencing a type created from AppDomain.CreateInstanceFrom

Thanks Rob!

Rob had the answer for me in an April post at
http://blogs.msdn.com/robgruen/archive/2005/04/05/405763.aspx

The funny part about my situation was that it was working fine, but I read all dlls in a directory, so when I changed my source directory to one containing dlls already loaded in my AppDomain… ka-boom- this issue got in my way.

It is all good now. It makes sense, there is no need to load the same assembly into a single appdomain more than once.

Plugin System nearly complete. Now if I could just get log4net to work in that second appdomain 🙁