using RunXc.Web;
using RunXc.DB;


RunXc


Where the DB meets the Web

Free Online Property Management

clock December 24, 2009 06:49 by author

Well lately I have decided to break down and learn ASP.NET MVC 2.  I had a friend who was having a hard time managing his rental properties because of all of the paperwork so I decided to create a Free Online Property Management System named PM-Flow.   I am building it both for experience and to make the best free tool out there as I don't like to do a half hearted job.  I plan on adding a lot of additional functionality including an offline application so that you can manage your properties when you don't have any internet connection.

If you are a property manager and are having difficulties or would just like to stream line your business try out the best Online Property Management Software and send me your feedback.  My goal is to add features as users need them so don't hesitate to send me an Email or leave a comment.

A plug for PM-Flow

Yeah this is a bit of a plug for PM-Flow but I will be posting a lot of lessons learned and some cool new features in ASP.NET MVC 2 and Silverlight 4 as I add those features to the site.

Submit this story to DotNetKicksShout it   Bookmark and Share  


Announcing Kiss Database Change Management

clock November 18, 2009 19:20 by author

So I have been working on a project for myself the past couple of months off and on and lately every time I switch from my laptop to my desktop or try to deploy it into my test environment I have had to update the database and remember to make the same change to my 3 computers.   I went looking for something simple as mud that worked worked exactly like I wanted it to.  I found a lot of good tools to auto generate my database differencing scripts but as I wrote the scripts myself and numbered them so I would remember which ones still needed to be ran those tools didn't help me.  What I needed was something that could run batch scripts against a Sql Server database and that would keep track of the database version for me.   I introduce you to Kiss DB Change Management.

Convention

1.  Create numbered Database Version scripts that are either hand written or created by a tool like DBDiff or RedGate or Whatever.
Examples
001-Creating some tables.sql
002-Creating-some-more-tables.sql
003-Insertingsomedataandcreating_views.sql
2. Place all of the Database change scripts in the same folder.
3. Thats it. What you thought it should be more difficult?

Use

KISS is a single executable file and is invoked in the following manner.(I suggest you create a bat file or run it in your Build script)
KISS.exe /f:Kissprops.xml
Kissprops.xml is a simple xml file that specifies the connection string and Database  Provider that is going to be used see below for an example.

   1: <?xml version="1.0" encoding="utf-16"?> 
   2: <Properties xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 
   3: <Verbose>true</Verbose> 
   4: <Wait>false</Wait> 
   5: <Provider>SQLServer</Provider> 
   6: <ConnectionString>server=.\SQLEXPRESS;database=KISS;User Id=KissUser;Password=$donate$;</ConnectionString> 
   7: <VersionTable>db_schema</VersionTable> 
   8: <VersionScriptsFolder>C:\dev\Schema\SqlServer</VersionScriptsFolder> 
   9: </Properties> 


How does it work

KISS will first discover what version the database is at by reading from the Version Table you specified (If the table doesn't exist KISS will create it so you don't need to worry about it).
KISS will then run each script that has a version above that of the database.
KISS will execute batch scripts so you can create multiple tables, views etc in the same script.
KISS executes each script as a Transaction so if the script fails the Transaction is rolled back the error message is displayed and execution is stopped. Oh it also returns an error code of 400 so that if you use it with MsBuild or Nant your build will Fail.

 

Currently Supported DataBases

SqlServer
I have set it up so that there is only one class and 4 methods needed to create a provider for any other database or to create an alternative SQL Server provider if someone ever needed to.
Submit this story to DotNetKicksShout it   Bookmark and Share  


Convention over Configuration- a .Net call to arms

clock August 26, 2009 06:36 by author

It seems that lately I have seen some of my fellow .Net programmers fall to the dark side.  Namely they have gone from the compiled world of c# to a slightly more hip Ruby on Rails(RoR) world at least for their web development projects.   I enjoy learning new technologies and languages and at first thought that they just were tired of using C# by time they got home and were learning something new in their after hours programming.  If I hadn't have had a new baby at home I might have tried to learn Ruby on Rails as most of my co-workers at my previous job gave it rave reviews.

What I find odd is that when my co-workers put their Ruby on Rails hats on they loved this idea of "Convention over Configuration" but when it came to coding in C# they wouldn't trust an ORM or some other time saving convention.  They preferred to write their own custom Data Access Layer, they preferred to write their own membership provider.  Now I understand that there were usually good reasons for these decisions but they each came with a large hit on the development timeline.

Along comes SubSonic

I at first didn't understand why most of the other developers loved RoR as I didn't take the time to learn it but a while back someone introduced me to SubSonic (which steals most of its ideas from RoR).  I was shocked at how quick I could build a web application.  In a couple of seconds I could add a developer admin page for all of my tables.  With little to no work I could handle the paging of a grid.  If I wanted to change my database schema I could make the change once and have it reflected in my code.   It was like my little tool belt that just kept giving.  It even had handy functions for checking if a string was a valid Email or Credit Card.  When I started using JSON I was enthralled to find out that it could convert my objects to JSON (this was back before ASP.NET 3.5 when it was built in).

We need more than just an ORM

For a good productivity/convention belt we need more than just an orm and we need the different pieces to play together nicely.  The productivity tool kit needs to make use of jQuery and some of its key plug-ins used for displaying and paging data as well as form validation on the client side.   It needs to tie the ORM to the User Management piece and it needs to have a default UI for Data Base manipulation.  The tool belt needs to work with little to no configuration. Too often I have had to read someone's blog about how to make X and Y play nice.  I know that I am asking for a lot but we have to build a lot of productivity saving tools and we need to have a life and play with our kids after work too.  Maybe I am just dreaming but it seems that more of our Open Source tools could converge and make our lives a little easier and happier.

.Net still has the advantage

Now I know that some people have switched to RoR for its productivity but I would argue that .Net still has the advantage.  .Net has a much larger selection of Reporting tools, PDF manipulation libraries, Bar Coding tools, etc etc.  Hey and to top it off C#/.Net is so much cooler than Ruby that it can even interpret Ruby (and many other dynamic languages).

Submit this story to DotNetKicksShout it   Bookmark and Share  


Oracle Cursor how to

clock July 31, 2009 06:12 by author

So I have had to program using quiet a few different databases as a backend for my web projects (Oracle, SqlServer, MySQL, SQLite, SqlAnywhere). I find that quiet often I have to write a script and I need some sort of "for loop" or "for each" loop and I end up using a cursor to get the job done.  Sure it works but I have always found the syntax of a cursor to be laborious and anything but syntactical sugar (a little bit more analogous to lemon juice).  Anyway I recently stumbled across some syntactical sugar when it comes to for loops with Oracle.

Note-  I "grew up" using TSQL and still prefer it to PL/SQL but as PL/SQL can often times be a little more complex and complexity equates to higher salaries.. I find myself using it.

Ok so enough rambling here is a script I wrote using a regular old cursor to drop all of the connections for a user with oracle.

--Kill all the Oracle connections script
DECLARE -- declare variables
  CURSOR c1 IS
  select sid, serial# from v$session
  where username='user';  -- declare cursor
-- declare record variable that represents a row fetched
   kill_it c1%ROWTYPE; -- declare variable with %ROWTYPE attribute
BEGIN
-- open the explicit cursor c1
  OPEN c1;
  LOOP
    FETCH c1 INTO kill_it; -- retrieve record
    EXIT WHEN c1%NOTFOUND;
    BEGIN
      EXECUTE IMMEDIATE
      'alter system kill session '''||kill_it.sid||', '||kill_it.serial#||'''';
    END;
  END LOOP;
  CLOSE c1;
END;

 

You will notice that with the cursor that you have to declare it and fetch from it with each loop, check to make sure that you actually fetched a value and then close the cursor when you are done.

Oracle Implicit Cursor how to go from ~16 to ~6 lines

BEGIN
  FOR kill_it in (select sid, serial# from v$session where username='user')
  LOOP
      EXECUTE IMMEDIATE
      'alter system kill session '''||kill_it.sid||', '||kill_it.serial#||'''';
  END LOOP;
END;

 

Ahh that is much better.  Writing statements like that in PL/SQL makes me feel much better. 

Submit this story to DotNetKicksShout it   Bookmark and Share  


Oracle Kill all sessions for a user

clock July 17, 2009 07:18 by author

I was recently with a client deploying some software and while talking with him he received a non related call asking him to kill all of the Oracle Sessions for a user (some bad application had used up all of the available connections).  Anyway I thought to myself I know how to Kill a session in Oracle and I know how to write a loop with a cursor so I whipped him up a little script to use in the future.

Kill all sessions in Oracle script

--Kill all the Oracle connections script
DECLARE -- declare variables
  CURSOR c1 IS
  select sid, serial# from v$session
  where username='ADPE';  -- declare cursor
-- declare record variable that represents a row fetched
   kill_it c1%ROWTYPE; -- declare variable with %ROWTYPE attribute
BEGIN
-- open the explicit cursor c1
  OPEN c1;
  LOOP
    FETCH c1 INTO kill_it; -- retrieve record
    EXIT WHEN c1%NOTFOUND;
    BEGIN
      EXECUTE IMMEDIATE 'alter system kill session '''||
       kill_it.sid||', '||kill_it.serial#||'''';
    END;
  END LOOP;
  CLOSE c1;
END;

May this save you time in the future

Submit this story to DotNetKicksShout it   Bookmark and Share  


Blog.RunXc

View Bret Ferrier's profile on LinkedIn

Read an Article and Need Help?

Consulting/Contracting -Get a bid

OpenSouce Projects I like -jQuery, SubSonic, Mono, CC.Net

Languages- C#,javascript, VB, SQL, T-SQL, PSQL

DataBases- SqlServer,Oracle,MySql, SQLite, Sql Anywhere

Linux Flavors- OpenSuse, Ubuntu

VM Preference - VirtualBox

Least Favorite Reporting Technology-Crystal Reports

Sign in