How to move Excel data into Database in C#

Just recently, one of my project requires me to move all data from Excel format into the database, since the solution was really simple, I thought I’d share it.

Using the already provided library from Microsoft Office , we can easily manipulate the data inside each cell.

First, we need to add reference to the Excel Interop DLL (Microsoft.Office.Interop.Excel.DLL) , if you can’t find this , simply browse thru your microsoft office folder, and add excel.exe it already includes that DLL. Now you’re ready.

Add before the namespace: Using Microsoft.Office.Interop.Excel

Declare some private variables for later use:

private static Excel.Workbook MyBook = null; 
private static Excel.Application MyApp = null;
private static Excel.Worksheet MySheet = null;
MyApp = new Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open("c:\worksheet.xls");
MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explicit cast is not required here
var lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row; // how many rows

Then the easy part. Reading the contents of your spreadsheet.

List<Person> person new List<Person>();
for (int index = 2; index <= lastRow; index++)
    System.Array MyValues = (System.Array)MySheet.get_Range("A" + 
       index.ToString(), "D" + index.ToString()).Cells.Value;
    person.Add(new Person{
      Name = MyValues.GetValue(1,1).ToString(),
      Employment = MyValues.GetValue(1,2).ToString(),
      Email = MyValues.GetValue(1,3).ToString(),
      Phone= MyValues.GetValue(1,4).ToString()

Then the inserting it into a database is much easier, since you now have the object filled with data (Person object) you can either pass this to a method that processes your CRUD or simple use it here if you don’t have any db layer. Here’s an example:

using (var db = DBContext())
   foreach (var p in person) {

   TBL_PERSON _person = new TBL_PERSON
     Name = p.Name,
     Employment = p.Employment,
     Email = p.Email,
     Phone = p.Phone



Hope this helps out anyone who has the same task.

Solution adapted from: Nanda Kumar Chimtan

 September 28th, 2016  
 Gil Tabasa  
 0 Comment

How to create custom textbox in AngularJS and wire it with Jquery

So this week, I was task to do a project that requires dynamic input. How do you approach something like this in AngularJS was my question. Now I can use a lot of formatters from AngularJS but I decided to go the easy route, no complication.

So here’s an easy way to create input type=’text’ the Jquery way, then I call the scope (controller) from jquery let’s say $scope.MyFunction. Here’s how it’s done.

First inside your AngularJS once a button is clicked from your html (or cshtml):

on ¬†my html a simple <div id=’myTextBox’></div>

$scope.OnclickCreateTextBox = function (operation,id) {
//edit or delete resolution
if (operation == 'UPDATE')
$('#myTextBox' + id).html('<input type="text" style="width:300px;" id="txtNew1" value="' +
name + '"><i class="glyphicon glyphicon-check" style="color:green" onclick="EditDeleteResolution('+id+')"></i>');
};//on jquery wire it like so:
function EditDeleteResolution(parm1) {
angular.element(document.getElementById('resTextBox' + parm1)).scope().myFunction(parm1);

There it is, if you have any question post them below.

 May 6th, 2016  
 Gil Tabasa  
 Angular JS  
 , , ,   
 0 Comment

How to stop a hung service (starting or stopping) status

Sometimes us developer deals with server every now and then, although we are supported by Admin, there will be times that we have to do it on our own.

Here’s a simple way to stop or kill a hung service. When your service status is “stopping” or “starting” you can kill it using following simple command.

On command window : type

> sc queryex servicename

the reason is you want to get the PID to be able to kill it. Sometimes this doesn’t especially if the service name has spaces for instance “My App Service Prod” in that case use the following command to view them.

On command window: type

>tasklist /svc – this is a powershell command that would list or services with corresponding PID.

Once you’ve achieved the PID, here’s the command to kill it.

On command window: type

> taskkill /f /pid [servicepid]

This will force kill the hung service.

This article is brought to you by : First Brand Media LLC