Gokulnath's weBLOG - .NET

WinHost–Automate Database Backup

Gokulnath — Sat, 15 Dec 2012 17:07:48 GMT

As you know if you have WinHost $5 or $10 plans, you cannot backup databases from SSMS, you have to do it from the Web Control Panel. After getting tired of doing this manually, I decided to automate this… as I have always been a big fan of automating things. This is how you can do it:

Software Requirements:
1. Selenium IDE
2. Visual Studio (if you’re going to use C#)
3. Java
4. Selenium Server
5. Firefox browser

Steps involved:
1. Install Selenium IDE from here, or open Firefox browser and install from the Add-On page. You should have Firefox installed in your machine.
2. Launch the IDE after installing. This is how the IDE looks:

3. The Selenium IDE starts in recording mode, the red button on the top right corner can be used to start or stop recording.
4. Open a Firefox window, and go to WinHost’s control panel page.
5. Enter your username, password, and follow all the steps that you do for manual database backup. The IDE will record every click event, and you should be able to see the events in the IDE getting added.
6. Once you are done with all the tasks, stop recording.
7. Go to File menu, Export Test Case As and choose C# / NUnit / Remote Control, which will generate a C# class.
8. Create a Console Application or a Service. Console Application works well for me.
9. You need to copy parts of code from the IDE generated class. You can put everything in a method, something like this:

 1:  ISelenium
selenium;

 2:  StringBuilder
verificationErrors;

3:

 4:  selenium
= new DefaultSelenium("localhost",
4444, "*chrome", "https://cp.winhost.com/");

 5:  selenium.Start();

 6:  verificationErrors
= new StringBuilder();

7:

 8:  selenium.Open("/login.aspx?ReturnUrl=%2fdefault.aspx");

 9:  selenium.Type("id=ctl00_cphMain_loginCP_UserName", "YourUserId");

 10:  selenium.Type("id=ctl00_cphMain_loginCP_Password", "YourPassword");

 11:  selenium.Click("id=ctl00_cphMain_loginCP_Login");

 12:  selenium.WaitForPageToLoad("30000");

 13:  selenium.Click("id=sites");

 14:  selenium.WaitForPageToLoad("30000");

 15:  selenium.Click("id=ctl00_cphMain_gvSites_ctl02_lbManage");

 16:  selenium.WaitForPageToLoad("30000");

 17:  selenium.Click("css=img[alt=\"Microsoft
SQL Manager\"]");

 18:  selenium.WaitForPageToLoad("30000");

 19:  selenium.Click("id=" +
labelName);

 20:  selenium.WaitForPageToLoad("30000");

 21:  selenium.Click("link=Backup");

 22:  selenium.WaitForPageToLoad("30000");

 23:  selenium.Click("id=ctl00_cphMain_btnBackup");

 24:  var
confirmation = Regex.IsMatch(selenium.GetConfirmation(), "^Create
backup\\. Are you sure[\\s\\S]$");

 25:  selenium.WaitForPageToLoad("30000");

10. Install Selenium related DLLs from Nuget. Open Visual Studio’s Package Manager Console, and install packages - Selenium.RC and Selenium.Support
11. Download Selenium Server from here, you SHOULD have Java installed in your machine to start the server.
12. The server should be started before executing the above code, you can create a .bat file, with this line “java -jar D:\Utils\selenium-server-standalone-2.25.0.jar” in it.
13. You can execute the bat file like this - System.Diagnostics.Process.Start(@"D:\Utils\startSeleniumServer.bat");
14. Now, when you start the console app, it will repeat the recorded steps, and backup your database.
15. Remember, the database will be backed up in the app_data folder; and if the folder already has a backup file, you need to delete it. You could write a FTP module or use the FTP Utility that I wrote (attached) to delete and download the backup file.
16. You have to pass the file path and the credentials if you’re using my FTP utility. The file path will be something like this - ftp://FTP_IP/app_data/SEE_WHAT_THE_MANUAL_BACKUP_GENERATES.bak

Note: You could use Selenium.WebDriver instead of RC Server, however, it doesn’t handle popup or confirmation window properly; as our process opens a confirmation window, I went with the RC Server.

Download FTP Utility

Enable browser back/forward buttons, and bookmark MVC Ajax Actions

Gokulnath — Sun, 26 Feb 2012 21:11:27 GMT

Some of the disadvantages of using MVC AJAX ActionLinks returning partial views are:
1.    We cannot bookmark the pages with AJAX returned content, as AJAX calls don’t change the URLs. For eg., say we have an AJAX actionlink on the home page called “contact”, and when we click it returns a partial page that gets loaded inside a div tag. If the page is bookmarked, the URL is just going to render the home page without the contact information.
2.    If we want our users to view the AJAX actionlink returned content by directly entering the URL, then we have to create a full view, and in the controller we have to check whether it is an AJAX request (Request.IsAjaxRequest), and if so, return the partial view; else, return the full view.
3.    Browser back/forward button clicks will not do anything.

We can overcome all these problems using hash tags. Gmail is a very good example of an AJAX application; each and every left menu click event just changes hash tag and loads content asynchronously.

Say we are supposed to build a website to render shapes like circle, diamond and star asynchronously, and users should be able to use all the features mentioned above.

How to do?
1.    Create a MVC Application (I’m using MVC 3).
2.    Include jquery.js and jquery.unobtrusive-ajax.js files on the layout page.
3.    Download jQuery history plugin from here, and include it too.

<head>
<title>@ViewBag.Title</title>
<link href="@Url.Content("~/Content/Site.css")" rel="stylesheet" type="text/css"
/> <script src="@Url.Content("~/Scripts/jquery-1.7.1.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.unobtrusive-ajax.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.history.js")" type="text/javascript"></script>
</head>

Create 3 actions in the controller that returns 3 partial views based on the action.

[HttpGet] public ActionResult GetCircle()
{ return PartialView("Circle");
} [HttpGet] public ActionResult GetDiamond()
{ return PartialView("Diamond");
} [HttpGet] public ActionResult GetStar()
{ return PartialView("Star");
}

Add 3 views circle.cshtml, diamond.cshtml and star.cshtml. To keep things simple, just add a div tag in each view with appropriate css class.

Circle.cshtml file’s content:
<div class="circle"></div>

Diamond.cshtml file’s content:
<div class="diamond"></div>

Star.cshtml file’s content:
<div class="star"></div>

Get the styles from here and add it to your layout page.

Now, add 3 action links:

@Ajax.ActionLink("Render
Circle", "GetCircle", null, new AjaxOptions {
HttpMethod = "Get", UpdateTargetId = "divContent",
OnSuccess = "AddHashTag('circle')" }, new {
@id = "circle" }) @Ajax.ActionLink("Render
Diamond", "GetDiamond", null, new AjaxOptions {
HttpMethod = "Get", UpdateTargetId = "divContent",
OnSuccess = "AddHashTag('diamond')" }, new {
@id = "diamond" }) @Ajax.ActionLink("Render
Star", "GetStar", null, new AjaxOptions {
HttpMethod = "Get", UpdateTargetId = "divContent",
OnSuccess = "AddHashTag('star')" }, new {
@id = "star" })

These actionlinks will call the action methods on the controller, and replace the div tag “divContent” (should be added on the page) content with the returned content. These actionlinks will also call javascript method AddHashTag if the AJAX calls get executed successfully.

Add the script block mentioned below. The AddHashTag function adds “hashTag” parameter as the hash tag to the url. The other function (from jquery history plugin) checks for hash tag, and if found, triggers click event. In this example, I’m just passing the tag’s id to the AddHashTag function.

<script type="text/javascript"> $(function ()
{ $.history.init(function (hash) { if (hash.length
> 0) { $("#" + hash).click(); } }, { unescape: ",/" });
}); function AddHashTag(hashTag) { window.location.hash
= hashTag; } </script>

Run the application.

When we click “Render Circle” link, circle.cshtml partial view will be returned and hash tag “circle” will be appended to the url.

To render star, we can click “Render Star” or enter http://localhost:60129/#star, which will render:

We can bookmark these links and use browser back/forward buttons.

We are done with the application, you can modify the AddHashTag function or the AjaxLinks according to your needs.

Please let me know if you have any questions.

Here is the source code -

download

Lego Hexapod Controlled using Microsoft Robotics Developer Studio (MRDS)

Gokulnath — Mon, 27 Jun 2011 00:27:09 GMT

At last got some time to complete coding my hexapod to make it walk and sense obstacles. Click here to download the source code.

<\/param><\/embed><\/object><\/div>";" alt="">

If you are beginner, and want to use MRDS to control your Lego Mindstorms NXT 2.0, refer to my previous posts:

1. Learn how to create DSS Service and use Manifest Editor
2. Lego Bot controlled using MRDS

Feel free to contact me if you have any questions or suggestions.

WCF Data Service in IIS Deployment (HTTP 500 Error)

Gokulnath — Sat, 19 Mar 2011 15:57:32 GMT

My WCF Data Service worked fine in the VS Web Server, but when I deployed it to IIS (7.5), it stopped working. I was able to view the service page listing all the entities, but when I tried to access the entities in a RESTFUL manner, I wasn’t.

I found out the problem by setting

config.UseVerboseErrors = true;

public static void InitializeService(DataServiceConfiguration
config)

The problem was I was using windows authentication to connect to my database, after changing to SQL Server Authentication, everything worked fine.

Spent hours in figuring out this…

My Lego Bot

Gokulnath — Fri, 05 Nov 2010 16:08:10 GMT

In this article I’m going to explain about creating a fully computer controlled Lego Mindstorms NXT 2.0 Bot (with camera mounted on it) using Microsoft Robotics Developer Studio (MRDS). My previous article Microsoft Robotics Service for LEGO NXT 2.0 should be useful if you haven’t created any service using the Studio.

Requirements:
Lego Mindstorms NXT 2.0 Kit
Microsoft Robotics Developer Studio
Network/IP Camera (I used my iPhone with WiFi Camera app installed on it)
WPF Dashboard Controls

Download Source Code:
MyLegoCar.zip (388.43 KB)

Create a MRDS Service and add the partners - NxtDrive and NxtBattery. Adding the partners will automatically declare and instantiate an object for BatteryOperations and an object for DriveOperations. Please add one more for DriveOperations as the code mentioned below.

 ///
<summary>

 ///
NxtBattery partner

 ///
</summary>

 [Partner("NxtBattery",
Contract = battery.Contract.Identifier, CreationPolicy = PartnerCreationPolicy.UseExistingOrCreate)]

 battery.BatteryOperations
_nxtBatteryPort = new battery.BatteryOperations();

 ///
<summary>

 ///
NxtDrive partner

 ///
</summary>

 [Partner("NxtDrive",
Contract = drive.Contract.Identifier, CreationPolicy = PartnerCreationPolicy.UseExistingOrCreate)]

 drive.DriveOperations
_nxtDrivePort = new drive.DriveOperations();

 drive.DriveOperations
_nxtDriveNotify = new drive.DriveOperations();

Add a new class library project to the solution, and add an interface to it. This project needs to be referenced in the MRDS Service and the WPF UI projects (will explain the UI project later).

 public interface IMyLegoCarService

 double GearPower
{ get; set; }

 long LeftEncoderCurrent
{ get; set; }

 long RightEncoderCurrent
{ get; set; }

 double LeftPowerCurrent
{ get; set; }

 double RightPowerCurrent
{ get; set; }

 double BatteryPower
{ get; set; }

 void Drive(DriveAction
driveDirection);

 void StopEngine();

Change the MRDS Service in such a way that it implements this interface. Values for all the properties but the first property (GearPower) will be set in the service, and they will be retrieved and used in the UI layer.

Now, add a new WPF project. VS will choose x86 as the targeted platform by default, change it to "Any CPU". Declare a property named Service of type IMyLegoCarService, and add a constructor, something like this.

 public Dashboard(IMyLegoCarService
service) : this()

 Service
= service;

 Service.GearPower = 0;

 brsr_ipcamera.Navigate(new Uri("http://ipoftheiphoneorthewebserver/iphonecamera/index.htm"));

 UpdateInitialOdometer();

 uiTimer
= new DispatcherTimer();

 uiTimer.Interval
= TimeSpan.FromSeconds(1);

 uiTimer.Tick
+= uiTimer_Tick;

 uiTimer.Start();

This constructor should be called from the Service’s constructor, so that the service and the UI will be on the same thread. Here “brsr_ipcamera” is the web browser control to display the ip camera’s image/video (in my case my iphone). I added an html page to my webserver displaying only the video from the camera. Add a timer control to display the information retrieved from the service periodically. Here I’ve used WPF Dashboard Controls’ dial controls as speedometers (for left motor front, left motor reverse, right motor front and right motor reverse), odometer control as odometer and progress bar control as fuel gauge. Left/Right Power Current properties were used to initialize the speedometers. Left/Right Encoder properties were used to initialize the odometer, these properties basically give us the degrees that the servo motors rotated. Using the formula: distance = Convert.ToInt32(Math.Abs(currentEncoderCurrent) / 360 * 2 * 3.14 * 0.75, we can calculate the distance covered. Here, pi = 3.14 and 0.75 is the radius of the wheels.

Coming back to the service. Declare and/or instantiate the following classes.

 wpf.WpfServicePort _wpfServicePort;

 drive.SetDriveRequest
_nxtSetDriveRequest = new drive.SetDriveRequest();

 battery.BatteryState
_nxtBatteryState;

WpfServicePort is used toinvoke the WPF UI, SetDriveRequest to rotate the motors and the BatteryState to get the battery information.

Add a port named “TimerTick” to the service types similar to the automatically created ports “Get”, “Subscribe” etc. Now your serviceoperations class declaration will be something like this -

 [ServicePort]

 public class MyLegoCarServiceOperations
: PortSet<DsspDefaultLookup, DsspDefaultDrop, Get, Subscribe, TimerTick>

 public class TimerTick
: Update<TimerTickRequest, PortSet<DefaultUpdateResponseType, Fault>>

 public TimerTick()

 : base(new TimerTickRequest())

 [DataContract]

 public class TimerTickRequest

Modify the service’s start method something like this -

 protected override void Start()

 SpawnIterator(DoStart);

 private IEnumerator<ITask>
DoStart()

 DispatcherQueue
queue = new DispatcherQueue();

 this._wpfServicePort
= wpf.WpfAdapter.Create(queue);

 //
invoke the UI

 var runWindow
= this._wpfServicePort.RunWindow(() => (Window)new Dashboard(this));

 yield return (Choice)runWindow;

 var
exception = (Exception)runWindow;

 if (exception
!= null)

 LogError(exception);

 StartFailed();

 yield break;

 //
Subscribe to partners

 var
subscribe1 = this._nxtDrivePort.Subscribe(_nxtDriveNotify);

 yield return (Choice)subscribe1;

 _timerPort.Post(DateTime.Now);

 //
Activate independent tasks

 Activate<ITask>(

 Arbiter.Receive<drive.DriveEncodersUpdate>(true,
_nxtDriveNotify, DriveEncoderHandler),

 Arbiter.Receive(true,
_timerPort, TimerHandler)

);

 //
Start operation handlers and insert into directory service.

 StartHandlers();

 private void StartHandlers()

 //
Activate message handlers for this service and insert into the directory.

 base.Start();

Timerport is used to retrieve the battery information periodically.

 [ServiceHandler(ServiceHandlerBehavior.Exclusive)]

 public IEnumerator<ITask>
TimerTickHandler(TimerTick incrementTick)

 incrementTick.ResponsePort.Post(DefaultUpdateResponseType.Instance);

 battery.Get
batteryGet;

 yield return _nxtBatteryPort.Get(GetRequestType.Instance, out batteryGet);

 _nxtBatteryState
= batteryGet.ResponsePort;

 if (_nxtBatteryState
!= null)

 BatteryPower
= _nxtBatteryState.PercentBatteryPower;

 yield break;

 void TimerHandler(DateTime
signal)

 _mainPort.Post(new TimerTick());

 Activate(

 Arbiter.Receive(false,
TimeoutPort(3000),

 delegate(DateTime
time)

 _timerPort.Post(time);

);

DriveEncoderUpdate to retrieve the information from the servo motors.

 private void DriveEncoderHandler(drive.DriveEncodersUpdate
statistics)

 LeftEncoderCurrent
= statistics.Body.LeftEncoderCurrent;

 RightEncoderCurrent
= statistics.Body.RightEncoderCurrent;

 LeftPowerCurrent
= statistics.Body.LeftPowerCurrent;

 RightPowerCurrent
= statistics.Body.RightPowerCurrent;

Create an enum named “DriveAction” in the common class library project. This is to handle the keyboard or the click events from the UI Layer.

 public enum DriveAction

 Front,

 Back,

 Left,

 Right,

 Stop

Implement the Drive method in the service.

 public void Drive(DriveAction
driveAction)

 switch (driveAction)

 case DriveAction.Front:

 _nxtSetDriveRequest.LeftPower
= -GearPower;

 _nxtSetDriveRequest.RightPower
= -GearPower;

 _nxtDrivePort.DriveDistance(_nxtSetDriveRequest);

 break;

 case DriveAction.Back:

 _nxtSetDriveRequest.LeftPower
= GearPower;

 _nxtSetDriveRequest.RightPower
= GearPower;

 _nxtDrivePort.DriveDistance(_nxtSetDriveRequest);

 break;

 case DriveAction.Left:

 _nxtSetDriveRequest.LeftPower
= -.4;

 _nxtSetDriveRequest.RightPower = .4;

 _nxtDrivePort.DriveDistance(_nxtSetDriveRequest);

 break;

 case DriveAction.Right:

 _nxtSetDriveRequest.LeftPower
= .4;

 _nxtSetDriveRequest.RightPower = -.4;

 _nxtDrivePort.DriveDistance(_nxtSetDriveRequest);

 break;

 case DriveAction.Stop:

 _nxtDrivePort.AllStop(MotorStopState.Coast);

 break;

 default:

 break;

I suppose I’ve explained most of the important parts in the service. Please let me know if you have any questions.

My bot in action:

<\/param><\/embed><\/object><\/div>";" alt="">

demo

Microsoft Robotics Service for LEGO NXT 2.0

Gokulnath — Sat, 04 Sep 2010 03:51:21 GMT

I’ve explained here about creating Microsoft Robotics Service for LEGO NXT 2.0 and configuring its manifest. When I wanted to create my first robotics service, I had many difficulties and found very few resources over the internet for LEGO NXT 2.0 using Microsoft Robotics, so just thought of blogging about this.

Requirements
Microsoft Robotics Studio – Click here to download. Click here to know more about the studio.
LEGO NXT 2.0

Installation
Microsoft Robotics Studio can be used as a stand-alone development environment or it can be used with any of the Visual Studio 2008 or 2010 Editions, including the Express Editions.

How to create DSS Service?
1. After Installing Microsoft Robotics Studio, open Visual Studio IDE (I’m using 2010).
2. Click File/New Project, you should be able to see Microsoft Robotics under C# like this -

3. Create the project under Microsoft Robotics Studio installation folder (it will be mostly in c:\user\username\microsoft robotics dev studio 2008 r3\), so that the common robotics dlls will be referenced properly.
4. Click OK and you will be allowed to enter your service name, namespace and other details. The most important thing is – you will be allowed to choose your partners from here -

5. Choose the partners one by one from the list and click “Add as partner”. Check the “Add notification port” checkbox and leave the Creation Policy as it is. Lego NXT Brick (v2) should be selected as one of the partners, and the other partners can be selected based on the sensors that you will be using. In case if you want to edit or delete any of the added partners, you can choose the partner from the partner dropdown and perform your actions. Once all the partners are selected, click OK.
6. You should be able to see the auto-generated code now. Build your solution.

How to create manifest using DSS Manifest Editor?
1. Open DSS Manifest Editor.
2. Choose your service from the left, drag and drop it on the editor. You should be able to see this -

3. Search for your partners on the left, drag and drop on “Use service’s partner definition (UseExistingOrCreate)” boxes appropriately.
4. As the sensors in turn use the nxtbrick as their partner, another set of “Use service’s partner definition (UseExistingOrCreate)” boxes will be created for the brick.
5. You need to select nxtbrick service again and drop it on the boxes. If you see a pop like the one below, choose the already created service and NOT “Add a new instance”.

6. To configure the service, click it and then press “Create Initial State” button on the properties window. For the NxtBrick, you need to set the serial port number used for bluetooth connection. For the sensors, you need to set the ports appropriately.
7. Replace this manifest with the manifest created by the service.
8. To start the service execute dsshost /port:50000 /manifest:"C:\Users\username\Microsoft Robotics Dev Studio 2008 R3\MyDemoService\MyDemoService.manifest.xml” in DSS Command Prompt.
9. You can also start the service from the DSS Control Panel. In order to view the control panel, start DSS Node and then go to http://localhost:50000 (default port is 50000, if you’ve changed it use it accordingly).
10. Switch ON your LEGO NXT 2.0 and then start the service. If everything is configured properly, you should be able to hear connecting sound from the NXT Brick.

This is it for now, in the next blog post, I’ll be explaining about adding handlers to the sensors and using the motors.

My Website

Gokulnath — Mon, 05 Jul 2010 20:57:00 GMT

I have learned a lot on developing my website, about .NET, ASP.NET, Web Design, Servers, Database etc., it’s been a good platform to learn the new technologies and implement them. I have played and been playing many roles in its development, here are them:

Web Designer
Being a creative person, I’ve always liked web designing, graphics, animation etc. This pushed me to create my first version of my website (HTML based), spent a lot of time on it and at the same time learned (self-learned) a lot on designing, good and bad techniques :). When I started working closely with a designer in HHS, learned many good practices on HTML Designing from her. It eventually pushed me to redesign my website. Though I acquired decent designing skills, I didn’t spend much time on designing as I wanted it to be simple and neat. I just followed the good practices on designing, this made my site look consistent in most of the browsers, in different resolutions, colors, point sizes etc.

Network Administrator
Before setting up my site I just had a minimal knowledge on networking - to just share folders, open firewall/router ports, setup static ip etc. But, after setting up my site or in the process of setting up my site I learned a lot about networking technologies/methodologies like Virtualization, Domain Controllers, Backup Strategies, Re-routing Network Traffic, Security, Access Control etc. Initially, I had my site hosted on Godaddy’s Servers, and later on built my own servers and deployed everything on my servers. It was definitely worth spending extra bucks in building my own server and taking business connection (as I my ISP blocked port 80 for residential connections). Its advantages – installed softwares as I wished, and upgraded them as and when updates were released, opened ports in the firewall, changed security settings as per my needs, and many more. My responsibilities as a network admin:-
1. Instead of buying a server machine, I just converted my desktop to a server by installing server 2003 r2, and added RAM (had 4 GB RAM, added another 4 GB) and hard drives (bought another hard drive for backup).
2. Thought Virtualization will serve me better in terms of security, availability, usage etc. So, I used VMWare Server to setup virtualized servers. Built a virtualized server in my main server machine (server 2003 r2), and another virtualized server in my personal computer (windows 7), thus got 3 servers (windows 2003 r2). Setup my server machine as the domain controller (just for reference, I’ll name it as “A”), the virtual machine in it as the web/ftp/file/shoutcast/sql servers (name - “B”), and the virtual machine in my PC as the backup server (or testing machine), clone of B (name - “C”). Just before major software updates or changes in the network settings or any other changes that was supposed to affect the web traffic in B, I rerouted my network traffic to C.
3. Synchronization Strategy – Synchronized servers B and C using SyncToy (for folders) and Backup/Restore feature in SQL Server (for database). I will explain this in detail in my next blog.
4. Backup Strategy – Scheduled nightly backup of windows folders, SQL server and few other settings, and monthly backup of virtual machines (manually copied virtual machine files) to a hard drive dedicated for backups.
5. At least at the time of writing this blog, I didn’t have anything to do with ASP.NET Sessions, so rerouting the web traffic was not a big deal – just opening and closing ports in my router took care of it. However, as I didn’t have a proper way to reroute my radio traffic, I just played some pre-recorded announcements saying “Server is going to be restarted, it will be back in X minutes” or “Server is unavailable due to maintenance, it will be back in few minutes (or hours)”
6. Domain Management – I added and modified @ and CNAME records, changed name servers etc.

Database Administrator/Developer
Godaddy’s ASP.NET Hosting was good (not great though), but their Database Hosting was ridiculous. They had an ASP.NET based interface, just to execute db scripts… running DDL scripts was not a problem; however, executing DML or importing data was not easy. So, decided to host my SQL Server Database in a different place, and only when I found that Godaddy blocked remote SQL Server access. All these limitations and the price factor pushed me to setup my own database server. My responsibilities as a database administrator or developer:
1. I’ve never liked writing dynamic or inline queries in the frontend, so always followed stored procedure approach. Performed most of the database operations using stored procedures. Once I started using LINQtoSQL and Entity Framework, the usage of stored procedures got reduced, I used them only for complex operations.
2. Created database, user accounts, roles etc., mapped user accounts or roles to data objects appropriately.
3. Built backup (full and differential) and restore strategy, and scheduled sql server jobs to perform these operations appropriately.
4. Created and used SSIS for routine GeoIP to Location data imports from MaxMind.

Tester
I used my server “C” as a backup or a testing server. I always applied patches or installed/upgraded softwares here, and only after thorough testing, I performed the same operations in my server “B” (my main server).

Web Developer
Working as a Web Developer, I created many Web 2.0 websites at my work, and at one point I found Web Development to be interesting than Windows Development. At that point, I felt I should convert my HTML site to Web 2.0 using all the latest web technologies. With all the experiences that I gained on web development from my job, I developed my website, and based on the experiences that I gained from developing my website, I did many things at my office – my work and my website served each other well.
I developed the first .NET version of my site using VS 2005/.NET 2.0 Framework with SQL Server 2005. Later on, upgraded to .NET Framework 3.5/VS 2008, used LINQtoSQL and other new features in VS 2008. My current version (as of Aug 15 2010), uses .NET 4.0 and SQL Server 2008.

If you need any help on building your own server, feel free to contact me.

Link to my site – http://www.gokulnath.com

Accessing TFS 2010 using Team Explorer 2008 Client

Gokulnath — Mon, 26 Apr 2010 15:21:03 GMT

If you are getting “None of the products that are addressed by this software update are installed on this computer. Click cancel to exit setup” (the popup below) while trying to install Visual Studio Team System 2008 Service Pack 1 Forward Compatibility Update for Team Foundation Server 2010 (this allows to use the Team Foundation Server 2010 server with Team Explorer 2008 SP1 client) , first install VS 2008 SP1 (not 3.5 SP1) and then try installing the compatibility update.

Regular space and html space (ascii 32 and 160 respectively)

Gokulnath — Wed, 09 Dec 2009 20:44:20 GMT

Can anybody find the difference between the first and the second ‘testing abc’ in this file test.txt (.02 KB). Open the file and search for ‘testing’ (there should be 2 matches), ‘abc’ (2 matches) and then ‘testing abc’ (1 match). It is because the ‘space’ between the two words varies, one is a regular space and the other is a html space (not nbsp), their ascii values are 32 and 160 respectively. I figured this after converting the spaces to ascii values! (spent almost an hour)

Take an asp.net application offline easily

Gokulnath — Wed, 09 Dec 2009 20:36:59 GMT

We can take an ASP.NET 2.0 application offline temporarily by uploading a file called app_offline.htm to the root directory. It will shut-down the application, unload the application domain from the server, and stop processing any new incoming requests for that application.

To know more about this - http://weblogs.asp.net/scottgu/archive/2005/10/06/426755.aspx

Working around the "IE Friendly Errors" feature - http://weblogs.asp.net/scottgu/archive/2006/04/09/442332.aspx

I came to know this only today when I was listening to Hansel minutes, this will definitely help me in taking my website down easily.

ASP.NET menu control - hiding root node and arrow image

Gokulnath — Wed, 09 Dec 2009 16:28:18 GMT

This is for my future reference.

If you are using sitemap as the data source for the menu control, then you can set the property ShowStartingNode = "false" of sitemapdatasource. Inorder to hide the arrow image, set the properties DynamicEnableDefaultPopOutImage = "False" and StaticEnableDefaultPopoutImage = "False" of the menu control.

Setting up ShoutCast Server (online radio) and fetching its statistics from .NET application

Gokulnath — Wed, 09 Dec 2009 04:08:22 GMT

Setting up shoutcast server is just like installing any other software. Installation and configurations should not take you enough time, whereas opening your firewall/router ports might take some time, it depends on your networking knowledge. If you are aware of setting up a router or any other network device, this should not take you enough time.

Here you go:

1. Download Winamp. Install it.
2. Download DSP plug-in. Install it.
3. Download appropriate DNAS Shoutcast Server for your server and OS. Install GUI version.
4. Run DNAS server. This will open your GUI.

5. Start Winamp and open Preferences (CTRL-P) and click on the DSP/effects subcategory under the Plug-ins tree.
6. Select Nullsoft SHOUTCast Source DSP v x.x. This will open a pop-up like this

7. Play some song in winamp, this will change the dB on Input Meters (Main Tab).
8. Go to the 'Encoder' tab. Change the Encoder Type to MP3 Encoder, if you are playing mp3 song. This will automatically choose Encoder Settings to 24 Kbps, 22.050kHz, Mono. You can change this based on your needs.
9. Go to the 'Output' tab. Click the Connect button, this will start streaming the song current being played i.e., the status will change from 'Not Connected' and it will look like the window below. Click Yellowpages to add information about your radio.

10. If the status has changed, you can jump to step . If the status has not changed, check whether the shoutcast server has been added to your Windows Firewall exceptions list. Open your control panel, double-click windows firewall and go to Exceptions tab. Check whether 'sc_serv' is in the exception list, if not, click add program and add your shoutcast server exe (programfiles\shoutcast).

11. To check whether the shoutcast server is streaming properly, open windows media player. Go to File menu and click Open Url (ALT + F, U), and type http://localhost:8000. You should be able to listen to the song being played in winamp.
12. Open ports 8000 and 8001 in your windows firewall (see step 10 to know about opening firewall ports), and then open the same ports in your router (look for port forwarding in your router config page). This will allow you to stream your radio on the internet.
13. You can change the Shoutcast server's settings based on your needs (open DNAS GUI and click Edit Config). This site explains clearly about the settings - http://www.gentoo.org/doc/en/shoutcast-config.xml#doc_chap1

Fetching Shoutcast statistics from .NET application

WebClient webClient = new WebClient();
String xmlDoc;
DataSet shoutCastStatistics = new DataSet();

webClient.Headers.Add("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.0.3705;)");
xmlDoc= webClient.DownloadString("http://yourradiourlORwanip:8000/admin.cgi?pass=PASSWORD&mode=viewxml");
shoutCastStatistics.ReadXml(new StringReader(xmlDoc), XmlReadMode.Auto);

Dataset shoutCastStatistics should have 5 tables: shoutcastserver, webdata, listeners, listener, songhistory and song. You can use them based on your needs.

Feel free to contact me if you have any questions.

CSS Friendly Adapters

Gokulnath — Thu, 19 Nov 2009 15:39:14 GMT

If we want to get control over finally rendered output of ASP.NET controls like Menu, TreeView, DataList etc, we can use CSS Control Adapter. This adapter emits CSS friendly html output and can significantly help when using CSS on the website. To know more about this adapter, follow this link - http://www.codeplex.com/cssfriendly . To see the difference between the html generated with and without using this adapter, follow this link - http://www.asp.net/CSSAdapters/Menu.aspx

Choose Items in Visual Studio Toolbox crashes Visual Studio

Gokulnath — Mon, 16 Nov 2009 15:04:38 GMT

I was trying to add AJAX Control Toolkit to my Toolbox, for some reason Visual Studio was crashing. I found a solution for it, see this works out for you – start Visual Studio in safe mode (go to Visual Studio’s command prompt and execute ‘devenv /safemode’) and then try to add items to your toolbox, I was able to do it! There might be a better solution for this, please let me know if there is one.

To prevent your system from idling

Gokulnath — Thu, 12 Nov 2009 22:52:51 GMT

This program prevents your system from getting locked even if it is left idle for time greater than your screen saver time. It basically presses ‘down’ arrow key every minute.

Create a Windows Forms Application and add a timer to the form.

public Form1()

{

InitializeComponent();

StartAction();

}

private void StartAction()

{

// the timer is started and the form is minimized

// set the timer interval as per your needs. I have set it to be 60000 milliseconds = 1 min

timer_keypress.Enabled = true;

this.WindowState = FormWindowState.Minimized;

this.ShowInTaskbar = false;

}

To add the application to your system tray when minimized, add a notify icon from your toolbox to the forms and set properties appropriately. I chose an icon and changed the text property. By default this form will be in minimized state, as I have added code to do so in my StartAction function.

Add context menu from your toolbox to show menu when the system tray icon is right clicked. Right click contextMenuStrip and choose Edit items to add your menu items. Double click on the menu item to add a handler. I’ve added ‘Exit’ menu. Set your notify icon’s contextmenustrip property.

private void exitToolStripMenuItem_Click(object sender, EventArgs e)

{

StopAction();

}

private void StopAction()

{

// the timer is stopped and the form is closed

timer_keypress.Enabled = false;

this.Close();

}

// this event will be triggered every minute

private void timer_keypress_Tick(object sender, EventArgs e)

{

SendKeys.SendWait("{DOWN}");

}

There are many ways to do this, I found this one to be easy.

Here is the file - KeyPress.exe (889 KB)

Get Windows account name on your ASP.NET page

Gokulnath — Fri, 06 Nov 2009 15:04:37 GMT

With <authentication mode="Windows"/> in your application and Anonymous access enabled in IIS, you will see the following results:

System.Environment.UserName: Computer Name

Page.User.Identity.Name: Blank

System.Security.Principal.WindowsIdentity.GetCurrent().Name: Computer Name

With <authentication mode="Windows"/> in your application, and ‘Anonymous access’ disabled and only ‘Integrated Windows Authentication’ in IIS, you will see the following results:

System.Environment.UserName: ASPNET (user account used to run ASP.NET service)

Page.User.Identity.Name: Domain\ Windows Account Name

System.Security.Principal.WindowsIdentity.GetCurrent().Name: Computer Name\ASPNET

With <authentication mode="Windows"/> and <identity impersonate ="true"/> in your application, and ‘Anonymous access’ disabled and only ‘Integrated Windows Authentication’ in IIS, you will see the following results:

System.Environment.UserName: Windows Account Name

Page.User.Identity.Name: Domain\ Windows Account Name

System.Security.Principal.WindowsIdentity.GetCurrent().Name: Domain\ Windows Account Name

With <authentication mode="Forms"/>:

Request.ServerVariables["LOGON_USER"]: Domain\ Windows Account Name

Postback problem on a modal popup or Session transfer to a popup or a page opened from another popup

Gokulnath — Fri, 23 Oct 2009 11:07:04 GMT

Solutions for postback problem:
1. Add "<base target = “_self” />" inside <head> tag of the popup.
2. You can use window.open instead of window.showModalDialog.

Solutions for session transfer problem:

1. You can use window.open instead of window.showModalDialog.

2. You can use AJAX modal popup.

This seems to be a IE bug (working fine in firefox and chrome), I have this problem if I open the .NET application after opening an instance of IE (say google.com) whereas if I open the former before the latter, things are fine.

Please let me know if anybody has any good solution for this.

Session expiration Notification & Redirection using AJAX Modal Popup

Wed, 30 Sep 2009 18:55:52 GMT

This article describes how to notify users about session expiration and allow them to renew the session.

Controls that need to be added:
1. A Panel with an OK button.

2. A label with session expiration notification message.

3. An AJAX ModalPopupExtender.

4. A dummy button and set ModalPopupExtender’s TargetControlId as this button.

This is how it looks:

<cc1:ModalPopupExtender ID="modext_promptmessage" runat="server" BehaviorID="popup_promptmessage"

PopupControlID="pnl_popup" TargetControlID="btn_dummy">

</cc1:ModalPopupExtender>

<asp:Panel ID="pnl_popup" runat="server" Style="display: none">

<th>

Prompt Message

</th>

</tr>

<tr>

<td>

<label>Your session is going to expire in 2 minutes. Please click OK to renew it."></label>

<asp:Button ID="btn_ok" runat="server" OnClientClick="ResetSession()" Text="OK" />

</td>

</tr>

</table>

</asp:Panel>

My code behind class looks like this:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

' checks whether session expired

If Current.Session.Keys.Count = 0 Then

Response.Redirect("sessionexpired.aspx", True)

Exit Sub

End If

' Javascript function PromptSessionExpiration() will be called 2 minutes before session expiration (120000 milliseconds)

Dim strExpireSessionScript As String = String.Format("setTimeout('PromptSessionExpiration()', {0}); ", (Me.Session.Timeout * 60000) - 120000)

Me.Page.ClientScript.RegisterClientScriptBlock(Me.GetType(), "expirescript", strExpireSessionScript, True)

End Sub

''' This click event will renew the session, need not add any code inside it

Protected Sub btn_ok_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_ok.Click

End Sub

My javascript:

function PromptSessionExpiration() {

// the function below will be triggered at the 125^th second

setTimeout('CheckSessionStatus()', 125000);

// show popup

$find('popup_promptmessage').show();

}

// handles closing popup

function ResetSession() {

$find('popup_promptmessage').hide();

}

// this function will be called if user doesn’t respond to the prompt message

// which will redirect to sessionexpired page

function CheckSessionStatus() {

window.location = “sessionexpired.aspx”;

}

</script>

Thanks.

Populating HTML tables using PageMethods/AJAX

Gokulnath — Tue, 15 Sep 2009 19:24:25 GMT

This article shows you how to use ASP.NET AJAX PageMethods to perform Create, Read, Update and Delete (CRUD) operations with an HTML table. Here HTML table acts as a light-weight DataGrid.

Inorder to make PageMethods work, following things need to be done:

ScriptManager should be added to your ASPX page.
EnablePageMethods property of the ScriptManager should be set to true.
System.Web.Services namespace should be added as reference on your codebehind class.
Page Methods on your code-behind should be decorated with [WebMethod] attribute.

First let us start with Read.

As mentioned above, add a ScriptManager and set its 'EnablePageMethods' property to true. Add an HTML button and an onclick handler to it, and then add an HTML table with thead, tbody and tfoot. Since the HTML table will be referenced from javascript, add id to the table and its body. Here, only HTML tags/controls are used because, server side controls cannot be referenced in PageMethods.

Your ASPX page should look something like this.

<body>

<asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods ="true" ></asp:ScriptManager>

<%--This click event handles loading data from the database--%>

<div>

<tr>

Column1

</td>

Column2

</td>

Edit

</td>

</tr>

</thead>

</tbody>

<tfoot>

<tr>

</td>

</td>

<%--This click event handles adding data to the database--%>

</td>

</tr>

</tfoot>

</table>

</div>

</form>

</body>

Now add your JavaScript function to load data from the database using PageMethods. PageMethod call should always have a success handler (this will be executed if the page method is executed successfully) and an exception handler (this will be executed if an exception is thrown). Say suppose we added 'GetData()' as the page method on the code behind, our javascript will be PageMethods.GetData(SuccessHandler, ExceptionHandler). Just for understanding, I have named the success and exception handler appropriately, you can name them as you wish. In case, the page methods takes parameters, you can add like PageMethods.GetData(param1, param2, SuccessHandler, ExceptionHandler).

Page methods should be decorated with [WebMethod] attribute and should be declared as static. Its signature shoul look something like this:

[WebMethod]

public static string GetData()

Page method to return data to javascript.

public partial class AJAXGrid : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

[WebMethod]

public static IEnumerable<MyEntity> GetData()

{

try

{

Data fetch part should go here

// used List, as collections are serializable. See below for MyEntity class

List<MyEntity> MyEntities = new List<MyEntity>();

MyEntities.Add("1", "abc");

MyEntities.Add("2", "xyz");

MyEntities.Add("3", "pqr");

MyEntities.Add("4", "mno");

return MyEntities;

}

catch(Exception ex)

{

throw ex;

}

}
}

MyEntity class

public class MyEntity

{

private string _Column1;

public string Column1

{

get { return _Column1; }

set { _Column1 = value; }

}

private string _Column2;

public string Column2

{

get { return _Column2; }

set { _Column2 = value; }

}

public MyEntity(string sCol1, string sCol2)

{

_Column1 = sCol1;

_Column2 = sCol2;

}

Javascript function that calls page method and populates the HTML table

//Loading Data

// Handles btn_load click event

function LoadData() {

// If data was fetched successfully, SuccessHandler will be called; else, ExceptionHandler

PageMethods.GetData(SuccessHandler, ExceptionHandler);

// Incase parameters need to be passed to PageMethods, you can do like this PageMethods.GetData(param1, param2, SuccessHandler, ExceptionHandler)

}

// After fetching the data successfully

function SuccessHandler(result) {

var tbody = $get("tbody_grid");

// clear the table

for (var j = tbody.rows.length; j > 0; j--) {

tbody.deleteRow(j - 1);

}

// populate the table

for (var i = 0; i < result.length; i++) {

//two columns fetched from database are sent as parameters

AddRow(result[i].Column1, result[i].Column2);

}

return true;

}

// Edit and Delete buttons are added to the rows

function AddRow(col1, col2) {

var tbody = $get("tbody_grid");

var row = document.createElement("tr")

var td1 = document.createElement("td")

td1.innerText = col1;

var td2 = document.createElement("td");

td2.innerText = col2;

var td3 = document.createElement("td");

// add buttons

var btnEdit = document.createElement('input');

btnEdit.setAttribute('type', 'button');

btnEdit.setAttribute('name', 'Edit');

btnEdit.setAttribute('value', 'Edit');

// first parentNode represents <td> and the second represents <tr>

btnEdit.onclick = function() { Edit(this.parentNode.parentNode); };

var btnDelete = document.createElement('input');

btnDelete.setAttribute('type', 'button');

btnDelete.setAttribute('name', 'Delete');

btnDelete.setAttribute('value', 'Delete');

btnDelete.onclick = function() { DeleteRow(this.parentNode.parentNode); };

td3.appendChild(btnEdit);

td3.appendChild(btnDelete);

row.appendChild(td1);

row.appendChild(td2);

row.appendChild(td3);

tbody.appendChild(row);

}

// Handles exception

function ExceptionHandler(result) {

}

After populating the HTML table

Javascript functions to handle Edit, Update, Delete and Insert:

Editing Data

// this function handles edit button click

function Edit(row) {

var col1 = row.childNodes[0].innerText;

var col2 = row.childNodes[1].innerText;

// populates values in textboxes and displays Update and Cancel buttons

var editableRow = document.createElement("tr")

var td1 = document.createElement("td")

var txtBox1 = document.createElement('input');

txtBox1.setAttribute('type', 'text');

txtBox1.setAttribute('name', 'col1');

txtBox1.setAttribute('value', col1);

txtBox1.setAttribute('width', 30);

td1.appendChild(txtBox1);

var td2 = document.createElement("td");

var txtBox2 = document.createElement('input');

txtBox2.setAttribute('width', 300);

txtBox2.setAttribute('type', 'text');

txtBox2.setAttribute('name', 'col1');

txtBox2.setAttribute('value', col2);

td2.appendChild(txtBox2);

var td3 = document.createElement("td");

var btnUpdate = document.createElement('input');

btnUpdate.setAttribute('type', 'button');

btnUpdate.setAttribute('name', 'Update');

btnUpdate.setAttribute('value', 'Update');

btnUpdate.onclick = function() { Update(this.parentNode.parentNode); };

var btnCancel = document.createElement('input');

btnCancel.setAttribute('type', 'button');

btnCancel.setAttribute('name', 'Cancel');

btnCancel.setAttribute('value', 'Cancel');

btnCancel.onclick = function() { Cancel(this.parentNode.parentNode); };

td3.appendChild(btnUpdate);

td3.appendChild(btnCancel);

editableRow.appendChild(td1);

editableRow.appendChild(td2);

editableRow.appendChild(td3);

row.parentNode.replaceChild(editableRow, row);

}

After edit button click

Updating Data

// this function handles update button click

function Update(row) {

// fetches values entered in the textboxes

// first childNode represent <td> inside <tr> and second childNode represents textbox

var col1 = row.childNodes[0].childNodes[0].value;

var col2 = row.childNodes[1].childNodes[0].value;

// values sent to server

PageMethods.UpdateData(col1, col2, UpdateSuccess(row), ExceptionHandler);

}

// After updating the values successfully

function UpdateSuccess(row) {

var col1 = row.childNodes[0].childNodes[0].value;

var col2 = row.childNodes[1].childNodes[0].value;

var editableRow = document.createElement("tr")

var td1 = document.createElement("td")

td1.innerText = col1;

var td2 = document.createElement("td");

td2.innerText = col2;

var td3 = document.createElement("td");

var btnEdit = document.createElement('input');

btnEdit.setAttribute('type', 'button');

btnEdit.setAttribute('name', 'Edit');

btnEdit.setAttribute('value', 'Edit');

btnEdit.onclick = function() { Edit(this.parentNode.parentNode); };

var btnDelete = document.createElement('input');

btnDelete.setAttribute('type', 'button');

btnDelete.setAttribute('name', 'Delete');

btnDelete.setAttribute('value', 'Delete');

btnDelete.onclick = function() { DeleteRow(this.parentNode.parentNode); };

td3.appendChild(btnEdit);

td3.appendChild(btnDelete);

editableRow.appendChild(td1);

editableRow.appendChild(td2);

editableRow.appendChild(td3);

row.parentNode.replaceChild(editableRow, row);

}

// this function handles cancel button click

function Cancel(row) {

// values are again populated in labels instead of textboxes

var col1 = row.childNodes[0].childNodes[0].value;

var col2 = row.childNodes[1].childNodes[0].value;

var editableRow = document.createElement("tr")

var td1 = document.createElement("td")

td1.innerText = col1;

var td2 = document.createElement("td");

td2.innerText = col2;

var td3 = document.createElement("td");

var btnEdit = document.createElement('input');

btnEdit.setAttribute('type', 'button');

btnEdit.setAttribute('name', 'Edit');

btnEdit.setAttribute('value', 'Edit');

btnEdit.onclick = function() { Edit(this.parentNode.parentNode); };

var btnDelete = document.createElement('input');

btnDelete.setAttribute('type', 'button');

btnDelete.setAttribute('name', 'Delete');

btnDelete.setAttribute('value', 'Delete');

btnDelete.onclick = function() { DeleteRow(this.parentNode.parentNode); };

td3.appendChild(btnEdit);

td3.appendChild(btnDelete);

editableRow.appendChild(td1);

editableRow.appendChild(td2);

editableRow.appendChild(td3);

row.parentNode.replaceChild(editableRow, row);

}

// this function handles 'add' button click

function Add() {

var col1 = $get("txt_addcol1").value;

var col2 = $get("txt_addcol2").value;

// data sent to the database

PageMethods.InsertData(col1, col2, AddSuccess(col1, col2), ExceptionHandler);

}

// After adding the data successfully

function AddSuccess(col1, col2) {

// add the values to the table

AddRow(col1, col2);

// clear the textboxes in the footer

$get("txt_addcol1").value = "";

$get("txt_addcol2").value = "";

}

Deleting Data

// this function handles delete button click

function DeleteRow(row) {

var col1 = row.childNodes[0].innerText;

// delete from the database

PageMethods.DeleteData(col1, DeleteSuccess(row), ExceptionHandler);

}

function DeleteSuccess(row) {

// delete the row from the table

var tbody = $get("tbody_grid");

tbody.removeChild(row);

}

</script>

Page methods to handle Edit, Update, Delete and Insert:

[WebMethod]

public static void UpdateData(string sCol1, string sCol2)

{

try

{

Data update part should go here

}

catch(Exception ex)

{

throw ex;

}

[WebMethod]

public static void InsertData(string sCol1, string sCol2)

{

try

{

Data insert part should go here

}

catch(Exception ex)

{

throw ex;

}

[WebMethod]

public static void DeleteData(string sCol1)

{

try

{

Data delete part should go here

}

catch (Exception ex)

{

throw ex;

}

We can add paging and sorting too.

This code has been tested in IE7+, Firefox, Chrome and Safari.

Some of the limitations in using ASP.NET AJAX PageMethods:

1. We can't access asp.net server controls (like TextBox control) in the WebMethod directly as we normally access in the server side methods.

2. We can't access any variable declared in the code behind.

Advantage: PageMethods is a simple lightweight way to submit/fetch data to the server using ASP.NET AJAX. This doesn't submit whole page data to the server and also as opposed to the ASP.NET AJAX call back this doesn't even fire the Page_Load and other Page events of the code behind page.

Free ASP.NET CAPTCHA control

Sat, 01 Aug 2009 17:45:44 GMT

As I started getting too many spams because of bots, I implemented Completely Automated Public Turing test to tell Computers and Humans Apart (CAPTCHA) in all the interactive pages of my site.

Took the control from here http://www.codeproject.com/KB/custom-controls/CaptchaControl.aspx, and customized according to my needs. The code was easy to understand and customize.

OpenSource Database & .NET documentation softwares

Tue, 16 Jun 2009 17:44:48 GMT

I got a chance to work on the below documentation softwares recently, were pretty good. There might be some other softwares better than these, if so, let me know.

SQLSpec – For database documentation, it supports various databases like MS SQL Server, Oracle, Access, DB2, Sybase etc. It is very easy to use, however, customizations like excluding objects and xml commenting took me sometime. It creates MSDN help file style documents. Download link - http://www.elsasoft.org/
SandCastle – Free software, for .NET project documentation. Supports .NET 1.1, 2.0, 3.0 and 3.5. It is also very easy to use and creates MSDN help file style documents. Download it from here http://www.codeplex.com/Sandcastle and install it, and then install SandCastle Help File Builder from here http://www.codeplex.com/SHFB

Adding custom configurations in Web.config or App.config

Mon, 01 Jun 2009 17:43:34 GMT

You can add your own configuration sections in Web.config or App.config file. There might be some good way to do it, if so, please let me know.

Step 1

Add the follwing inside <configSections> tag:

</configSections>

Step 2

Add your custom section before or after <appSettings> but after <configSections>

</customSection>

Your app.config will be like this

---------------------------------------------------------------------------------------------------------------

<?xml version="1.0" encoding="utf-8"?>

</configSections>

</customSection>

</appSettings>

<system.web>

</system.web>

</configuration>

--------------------------------------------------------------------------------------------------------------

Step 3

How to access it?

NameValueCollection colCustomSection = (NameValueCollection)ConfigurationManager.GetSection("customSection");

string key, value;

for (int count = 0; count < colCustomSection.Count; count++)

{

// we can get the keys

key = colCustomSection.GetKey(count);

// we can get the values

value = colCustomSection.Get(count);

}

To add multiple sections

Add this inside <configSections>

</sectionGroup>

</configSections>

Add this around your custom section:

</customSection1>

</customSection2>

</customSectionGroup>

To access it, just change the path:

NameValueCollection colCustomSection = (NameValueCollection)ConfigurationManager.GetSection("customSectionGroup/customSection1");

customSectionGroup/customSection1 or customSectionGroup/customSection2

Working with Excel sheet in .NET i.e., Create Read Update Delete

Thu, 16 Apr 2009 17:10:39 GMT

Select or Insert or Update or Delete rows in Excel using .NET. i.e., CRUD operations in EXCEL using .NET. We can literally use the excel sheet as a database, just the connection string matters. Note: 1. This code cannot be used for password protected excel sheets. 2. Delete does not work. See below for explanations. There might be some other good way to do this, if so, let me know.

Insert a row in an excel sheet

protected void btn_create_Click(object sender, EventArgs e)

{

OleDbConnection objConnection;

OleDbCommand objCommand;

OleDbParameter objParameter;

// Here HDR= YES represents that header is present

// If the excelsheet you are dealing with does not have header, you can mention HDR = NO,

// and while fetching columns you have to mention F1, F2 rather than the column names

objConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Excel\CRUD_EXCEL.xls;

Extended Properties=""Excel 8.0;HDR=YES;""");

objCommand = new OleDbCommand();

objCommand.Connection = objConnection;

objCommand.CommandType = CommandType.Text;

// The sheet name should be followed by $ sign

objCommand.CommandText = "INSERT INTO [SHEETNAME$](COLUMN_INT, COLUMN_VARCHAR) VALUES(@COLUMN_INT, @COLUMN_VARCHAR)";

objParameter = new OleDbParameter("@COLUMN_INT", OleDbType.Integer);

objParameter.Value = 3;

objCommand.Parameters.Add(objParameter);

objParameter = new OleDbParameter("@COLUMN_VARCHAR", OleDbType.VarChar);

objParameter.Value = "testing";

objCommand.Parameters.Add(objParameter);

objConnection.Open();

objCommand.ExecuteNonQuery();

objConnection.Close();

}

Select rows in an Excel sheet

protected void btn_read_Click(object sender, EventArgs e)

{

OleDbConnection objConnection;

OleDbCommand objCommand;

OleDbParameter objParameter;

// Here HDR= YES represents that header is present

// If the excelsheet you are dealing with does not have header, you can mention HDR = NO,

//and while fetching columns you have to mention F1, F2 rather than the column names

objConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Excel\CRUD_EXCEL.xls;

Extended Properties=""Excel 8.0;HDR=YES;""");

objCommand = new OleDbCommand();

objCommand.Connection = objConnection;

objCommand.CommandType = CommandType.Text;

// If the excel sheet you are dealing with does not have header, your query will be SELECT F1, F2 FROM [SHEETNAME$]

objCommand.CommandText = "SELECT COLUMN_INT as COLUMN1, COLUMN_VARCHAR AS COLUMN2 FROM [SHEETNAME$]";

objDataAdapter = new OleDbDataAdapter();

objDataSet = new DataSet();

objDataAdapter.SelectCommand = objCommand;

objConnection.Open();

objDataAdapter.Fill(objDataSet);

objConnection.Close();

}

Update a row in an excel sheet

protected void btn_update_Click(object sender, EventArgs e)

{

OleDbConnection objConnection;

OleDbCommand objCommand;

OleDbParameter objParameter;

// Here HDR= YES represents that header is present

// If the excelsheet you are dealing with does not have header, you can mention HDR = NO,

// and while fetching columns you have to mention F1, F2 rather than the column names

objConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Excel\CRUD_EXCEL.xls;

Extended Properties=""Excel 8.0;HDR=YES;""");

objCommand = new OleDbCommand();

objCommand.Connection = objConnection;

objCommand.CommandType = CommandType.Text;

objCommand.CommandText = "UPDATE [SHEETNAME$] SET COLUMN_VARCHAR = @COLUMN_VARCHAR WHERE COLUMN_INT = @COLUMN_INT";

objParameter = new OleDbParameter("@COLUMN_VARCHAR", OleDbType.VarChar);

objParameter.Value = "PQR";

objCommand.Parameters.Add(objParameter);

objParameter = new OleDbParameter("@COLUMN_INT", OleDbType.Integer);

objParameter.Value = 3;

objCommand.Parameters.Add(objParameter);

objConnection.Open();

objCommand.ExecuteNonQuery();

objConnection.Close();

}

Delete

protected void btn_delete_Click(object sender, EventArgs e)

{

As far as I know ADO does not support Excel row delete

You are more restricted in deleting Excel data than data from a relational data source.

In a relational database, "row" has no meaning or existence apart from "record";

in an Excel worksheet, this is not true. You can delete values in fields (cells). However, you cannot:

1. Delete an entire record at once or you receive the following error message:

Deleting data in a linked table is not supported by this ISAM.

You can only delete a record by blanking out the contents of each individual field.

2. Delete the value in a cell containing an Excel formula or you receive the following error message:

Operation is not allowed in this context.

3. You cannot delete the empty spreadsheet row(s) in which the deleted data was located,

and your recordset will continue to display empty records corresponding to these empty rows.

}

Importing XML data to Oracle database

Thu, 16 Apr 2009 17:08:18 GMT

Here is a method to import/send XML to Oracle and thus we need not call the database repeatedly for mass insert, update etc. DBMS_XMLSTORE has been used here. There might be some other good way to do this, if so, please let me know.

Here you go:

1. Importing Excel from .NET interface to Oracle. Just to get some xml, I have used an excel sheet here, you can replace it with your xml data.

COL1	COL2	COL3
1	Aaa	abc
2	Bbb	pqr
3	Aaa	Abcdef
4	ddd	pqrst

.NET Code

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Excel\\excel.xls;Extended Properties=Excel 8.0;";

OleDbDataAdapter daImportExcel = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

DataSet dsExcelData = new DataSet();

daImportExcel.Fill(dsExcelData, "TABLE_NAME");

OracleParameter parameter = new OracleParameter();

parameter = new OracleParameter("XML_CLOB", OracleDbType.Clob);

parameter.Direction = ParameterDirection.Input;

parameter.Value = dsExcelData.GetXml();

OracleCommand objCommandImport = new OracleCommand();

objCommandImport.Connection = objConnection;

objCommandImport.CommandType = CommandType.StoredProcedure;

objCommandImport.CommandText = "BIMS.XML_PARSER";

objCommandImport.Parameters.Add(parameter);

objCommandImport.ExecuteNonQuery();

Note: I am passing XML as CLOB.

Oracle Procedure

I am inserting the dataset, I mean the XML into table XML_TESTING. Here COL1 is integer and other 2 columns are varchar.

CREATE OR REPLACE PROCEDURE XML_PARSER

(

XML_CLOB IN CLOB

)

INSERT_CONTEXT DBMS_XMLSTORE.CTXTYPE;

ROW_COUNT NUMBER;

BEGIN

INSERT_CONTEXT := DBMS_XMLSTORE.NEWCONTEXT('XML_TESTING');

DBMS_XMLSTORE.SETROWTAG(INSERT_CONTEXT, 'TABLE_NAME');

DBMS_XMLSTORE.CLEARUPDATECOLUMNLIST(INSERT_CONTEXT);

DBMS_XMLSTORE.SETUPDATECOLUMN(INSERT_CONTEXT,'COL1');

DBMS_XMLSTORE.SETUPDATECOLUMN(INSERT_CONTEXT,'COL2');

DBMS_XMLSTORE.SETUPDATECOLUMN(INSERT_CONTEXT,'COL3');

ROW_COUNT := DBMS_XMLSTORE.INSERTXML(INSERT_CONTEXT, XML_CLOB);

DBMS_XMLSTORE.CLOSECONTEXT(INSERT_CONTEXT);

END XML_PARSER;

Note:

Names of the columns that are inserted and of the underlying database table should be same. By default, XML documents are expected to identify rows with the <ROW> tag. This is the same default used by DBMS_XMLGEN when generating XML. This may be overridden by calling the setRowTag function. Here ‘TABLE_NAME'’ has been set as rowtag as it is our table name.

Drawback:

.NET is not allowing to use GetXml in huge datasets. If somebody has a solution for it, please let me know.

You can understand better about DBMS_XMLSTORE from this article - http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10790/xdb_dbmstore.htm#CACEJGEH