Import Data From CSV File To SQL Server With Import And Export Wizard

There are many ways to import data from csv files to SQL Server but the easiest is using SQL Server's Import And Export Wizard. The wizard is very very versatile as it can do both import and export from various different sources and destinations such Microsoft Access, Micorosft Excel, Flat File (csv,text files), and SQL Server among others.
Here's the steps to follow to use the Wizard for importing a csv file to SQL Server:


  1. Open SQL Server Management Studio and connect to the destination database. Right Click the database, select Tasks and click  Import Data...

  2.  On the opening screen, click Next.

  3.  On the "Choose A Data Source Screen", select a Data Source in the combobox. In our case, we're going to choose Flat File Source because we're importing from a csv file. Click "Browse" and find the csv file to be imported. Check the checkbox if the csv data contain columns names in the first row.


  4. Click Columns. Here you can specify the row delimiter and column delimiter. Since we're using a csv file, column delimiter is a comma and row delimiter is a new line.

  5. Click Advanced. Here, you can change the data type for each columns of the csv file. The Wizard detects the type of data and automatically set it for you. But if you think the Wizard is incorrect, you can set the data type manually.


  6. Click Preview to see the preview and check if the settings you have made are correct. Then click Next.


  7. In the "Choose A Destination" window, select a destination in the combobox. In our case, since we are importing to a SQL Server database, we're selecting SQL Server Native Client. Enter the Server Name and Authentication credentials needed for accessing the database. Then click Next.


  8. In the "Select Source Tables and Views" window, check the source you want to import and on the right column select a destination table. For the destination table, you can either select an existing table or a new table.

  9. Click "Edit Mapping". In the "Column Mappings" window, there are 3 options available. "Create destination table" option is selected if you're creating a new table as the destination. If you're importing to an existing table, you can choose either to "Delete rows in existing table" or "Append rows to the destination table". You can also enable to insert identity data - this should be enabled if your source csv file contains Identity data that you want inserted as the row ID in the table. Then you need to check and modify accordingly the Column Mappings between the csv file and destination table. Then click OK and click Next.


  10. In the "Review Data Type Mapping", you can check the mapping results and determine if there are problems and inconsistencies so you can always click Back and modify your settings accordingly. When all is OK, click Next.

  11. In the Run Package, check Run immediately. This will execute Import immediately when you click Finished. If you're using SQL Server version that is higher than the Express edition, you can have the other option of saving the Import steps so you can have the luxury of running it again in the future without the need to perform the previous steps again.

  12. Click Next. In the "Complete the Wizard" steps, the import summary steps are displayed. If these steps are correct, then you can proceed to click Finish to begin importing your data



    If you want to be able to save this Import steps, you need to upgrade to a higher version of SQL Server. The saved step is called SSIS package and you can choose to run this package periodically using SQL Server Agent. But that topic is worth another blog page. I'll write about it next time.















Unit Testing With NUnit

Unit Testing is a methodology in computer programming where the smallest unit of software logic is tested to determine if it's correctly doing what the programmer has designed for it to do. Performing unit tests on the programs can effectively reduce and prevent bugs in the program and improve the coding efficiency of the developer.

Unit Testing requires skill and patience. At the start, performing unit tests may seem to just lengthen the time of coding and double your effort since you now need to write additional codes for testing. But in the long run, as you make myriads of changes to the software to cater to customer's unending demands, unit testing can help a lot as it can easily detect if a logic is broken due to the latest changes in your codes.

There are a lot of Frameworks in .NET that can help the developer perform unit testing easily. One that I like is NUnit. I've used NUnit in one of my projects and it's easy to understand and use. NUnit is an open source product so it's free to use. You can download it from http://www.nunit.org/.  When it's installed, it has a Test Runner program that can be used to automatically run your test.

To demonstrate the use of NUnit, I'm going to test the following simple class with its "IsNoteValid" method:

public class Notes
    {       
        public bool IsNoteValid(string note)
        {
            return !string.IsNullOrEmpty(note); 
        }
    }

"IsNoteValid" procedure returns false if its parameter is empty or null and returns true otherwise.

How do we test this code? A unit test usually comprises three main actions: ARRANGE, ACT, and ASSERT.
Arrange is the process of setting up the objects, Act is the processing of calling the procedure under test and Assert is the process of making sure the procedure under test behaves according to its specification. In our case, we are going to test the "IsNoteValid" procedure if it behaves correctly.

Below are the test codes. You can search on the internet for the proper syntax of NUnit but basically in the first Test Procedure, it is just calling the IsNoteValid procedure with a valid parameter and asserting that it must return true. In the second Test Procedure, it is calling the IsNoteValid procedure with an invalid parameter and asserting that it must return false.

       [TestFixture]
        public class NotesTests
        {
            [Test]
            public void IsNoteValid_validNote_ReturnsTrue()
            {
                //arrange
                Notes note = new Notes();
                //act
                bool result = note.IsNoteValid("mynote");
                //assert
                Assert.IsTrue(result, "note should be valid!");
            }

            [Test]
            public void IsNoteValid_invalidNote_ReturnsFalse()
            {
                //arrange
                Notes note = new Notes();
                //act
                bool result = note.IsNoteValid(string.Empty);
                //assert
                Assert.IsFalse(result, "note should be invalid!");
            }
        }

When the Test codes are done, you can run Test Runner, select the assembly .dll of your Test Project and click  Run. The Test Runner  will display how many tests have passed and failed.



So when at a later time, I need to refactor and modify my Notes codes, all I have to do is Run the Test Runner again to see if it has failed a test. This way, bugs can easily be detected.






First Attempt With WPF

The Windows Presentation Foundation (WPF) is the latest graphical display system for Windows. Now with version 4, it's the most radical change to hit Windows user interfaces since Windows 95. The underlying technology behind the power of WPF is DirectX. As a result, you can have rich user interface effects such as transparency, anti-aliasing, 3D graphics and hardware acceleration to name a few.
See below for the screenshots of one of my projects with my first attempt in using WPF. Obviously, it did not look as sophisticated given the vast power of WPF for generating great user interfaces. I've found out that you also need to be good on graphics design (which I'm not) to go hand in hand with WPF to successfully create the user interface of your dreams.





















ClickBank API in C#


Founded in 1998, ClickBank is a secure online retail outlet for more than 50,000 digital products and 100,000 active affiliate marketers. Just like Paypal, you can use ClickBank in your websites to handle payment transactions for your customers.

ClickBank publishes an API (a set of programming rules and specifications) for properly using their services. You can read about their API in http://www.clickbank.com/help/account-help/account-tools/clickbank-api/.

The current version of ClickBank’s Service API is: 1.2 and it uses REST for communication through the internet. ClickBanks provide example in C# but it's not detailed and hard to grasp especially if you're still new to REST.


In order to access their API, first you need the Clerk API key and the Developer Key. These keys are used for authorization and security so you must keep these in private and not show these keys to other people.

To acquire Developer and Clerk API keys, simply login to your ClickBank account and go to the Account Settings tab. Then, click “edit” in the Developer and Clerk API Key areas. Each key must be approved, present, and active for successful authentication to occur.

In one of our projects, we use ClickBank's API for determining if a customer's subscription is active and for canceling a customer's subscription.

Determining If A Customer Is Active

To determine if a user is active, you need to use the Orders API. This API's specification is published in https://api.clickbank.com/rest/1.2/orders. And here's how I've coded it in a C# procedure.


private bool IsClickBankAccountActive(string transactionId)
{
             //get authorization key string
            string authorizationKey = "DEV-A32" + ":"
               + "API-1SE";

            //set base uri
            Uri clickBankBaseUri = new Uri(_configuration.ClickBankRestUrl);

            //set uri template
            UriTemplate clickBankUriTemplate = new UriTemplate("/1.2/orders/{receipt}");

            //set complete uri
            Uri clickBankTicketUri = clickBankUriTemplate.BindByPosition(
                clickBankBaseUri, transactionId);

            //create request
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(
                clickBankTicketUri);
            request.Accept = "application/xml";
            request.Headers.Add(HttpRequestHeader.Authorization, authorizationKey);
            request.Method = "HEAD";

            HttpWebResponse response = null;

            //get response
            try
            {
                response = (HttpWebResponse)request.GetResponse();
                HttpStatusCode c = response.StatusCode;
                if (c == HttpStatusCode.NoContent)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }

        }
}



Canceling A Customer's Subscription

To cancel a customer's subscription, you need to use the Tickets API. This API's specification is published in https://api.clickbank.com/rest/1.2/tickets.

When you call this Ticket API, it returns a data that determines the status of Ticket and other relevant info.
I've created a TicketData class for representing the data that is returned.

Here's the TicketData class:


public class TicketData
{
        public enum StatusOfTicket
        {
            OPEN = 0,
            REOPEN = 1,
            CLOSED =2
        }

        public enum TypeOfTicket
        {
            TECH_SUPPORT = 0,
            REFUND =1,
            CANCEL=2,
            ORDER_LOOKUP=3,
            ESCALATED=4,
            APPROVAL_AD=5,
            APPROVAL_IMAGE=6,
            APPROVAL_UPSELL=7,
            APPROVAL_CATEGORY_CHANGE=8,
            APPROVAL_MAX_PRICE=9,
            APPROVAL_BLOG_POST=10,
            APPROVAL_PRODUCT=11,
            CATEGORY_SUGGESTION=12,
            BUSINESS_DEVELOPMENT=13,
            ACCT_QUESTION_MARKETING=14,
            ACCT_QUESTION_ACCOUNTS=15,
            ACCT_QUESTION_ACCOUNTING=16,
            APPROVAL_GENERIC=17,
            SPAM=18,
            ABUSE=19

        }

        public int TicketId
        { get; set; }

        public string Receipt
        { get; set; }

        public StatusOfTicket TicketStatus
        { get; set; }

        public TypeOfTicket TicketType
        { get; set; }

}


Also, when cancelling a customer, the API requires a reason on why it's cancelled.
I've coded the reasons in a Dictionary:

//Dictionary for determining cancellation reasons
public Dictionary<string, string> GetCancellationReasons()
{
   Dictionary<string, string> reasons = new Dictionary<string, string>();
   reasons.Add(CancellationReason.ticket_type_cancel_1.ToString(), 
     "I did not receive additional value for the recurring payments");
   reasons.Add(CancellationReason.ticket_type_cancel_2.ToString(), 
     "I was not satisfied with the subscription / Subscription " + 
     "did not meet expectations");
   reasons.Add(CancellationReason.ticket_type_cancel_3.ToString(), 
     "I was unable to get support from the vendor");
   reasons.Add(CancellationReason.ticket_type_cancel_4.ToString(), 
     "Product was not compatible with my computer");
   reasons.Add(CancellationReason.ticket_type_cancel_5.ToString(), 
     "I am unable to afford continuing payments for this subscription");
   reasons.Add(CancellationReason.ticket_type_cancel_6.ToString(), 
     "I did not realize that I accepted the terms for continuing payments");
   reasons.Add(CancellationReason.ticket_type_cancel_7.ToString(), 
     "Other");
   return reasons;
}


And here's the code for cancelling the user's subscription:


//this procedure cancel's the subscription of a customer
public TicketData CancelSubscription(
   string ReceiptNo,CancellationReason reason, String Comment)
{
           
  TicketData responseTicket = null;

  //get cancel reason string
  string cancelReason = reason.ToString().Replace('_', '.');

  //get cancel type string
  string cancelType = CancellationType.cncl.ToString();
            
  //get authorization key string
  string authorizationKey = "DEV-A32" + ":"
     + "API-1SE8";
            
  string cancelComment = string.IsNullOrEmpty(Comment) ? Comment : string.Empty;
            
  //set base uri
  Uri clickBankBaseUri = new Uri(_configuration.ClickBankRestUrl);
            
  //set uri template
  UriTemplate clickBankUriTemplate = new UriTemplate(
   "/1.2/tickets/{receipt}/?type={type}&reason={reason}&comment={comment}");
            
  //set complete uri
  Uri clickBankTicketUri = clickBankUriTemplate.BindByPosition(
     clickBankBaseUri, ReceiptNo, cancelType, cancelReason, cancelComment);

  //create request
  HttpWebRequest request = (HttpWebRequest)WebRequest.Create(
    clickBankTicketUri);
  request.Accept = "application/xml";
  request.Headers.Add(HttpRequestHeader.Authorization, 
    authorizationKey);
  request.Method = "POST";

  HttpWebResponse response = null;

 //get response
 try
  {
     response = (HttpWebResponse)request.GetResponse();
     string responseMessage = string.Empty;

     //Get Stream Response
     using (StreamReader reader = new
      StreamReader(response.GetResponseStream()))
     {
       responseMessage = reader.ReadToEnd();
     }

     //Parse Response using XDOCUMENT
     if (!string.IsNullOrEmpty(responseMessage))
     {
        XDocument document = XDocument.Parse(responseMessage);

        responseTicket = new TicketData();

        responseTicket.TicketId = Convert.ToInt32(document.Element(
           "ticketData").Element("ticketid").Value);
        responseTicket.Receipt = document.Element(
           "ticketData").Element("receipt").Value;
        responseTicket.TicketStatus = (
           Ncf.InsideEdge.Model.TicketData.StatusOfTicket)
           Enum.Parse(typeof(
           Ncf.InsideEdge.Model.TicketData.StatusOfTicket), 
            document.Element(
             "ticketData").Element("status").Value, true);
             responseTicket.TicketType = 
             (Ncf.InsideEdge.Model.TicketData.TypeOfTicket)Enum.Parse(
             typeof(Ncf.InsideEdge.Model.TicketData.TypeOfTicket),
              document.Element(
             "ticketData").Element("type").Value, true);
     }

     }
     catch
     {

     }

    //check response ticket
    if (responseTicket != null && responseTicket.Receipt == ReceiptNo
         && responseTicket.TicketType ==
         Ncf.InsideEdge.Model.TicketData.TypeOfTicket.CANCEL)
    {
        return responseTicket;
    }
    else
    {
        return null;
    }
  }
 }


I hope you find my article useful and it's able to help you understand better how to use RESTFUL Services, specifically the ClickBank API Service.








Creating a VB.NET Web Service in VS 2010




I was given the task of creating a program to communicate with a web service provided by VideoNext Network Solutions, Inc. There are various ways you can achieve communications in .NET world. For communicatons through internet, you can either use the awesome WCF technology, use the barebones WebRequest Class, etc. For this project, I've used the power of Web Service technology.

It's a little bit tricky to create a Web Service in a Visual Studio 2010 Class Library Project because currently, WCF is the default tool to do it. Anyhow, here's how I do it:

1. First, right click on the Class Library Project in Solution Explorer and click "Add Service Reference".




2. On the Add Service Reference Dialog, click Advance.



3. On the Service Reference Settings, click Add Web Reference.



4. On the Add Web Reference Dialog, type in the URL address box the URL address of the service that you wish to communicate. In my project, URL is  http://207.207.160.4:80/axis2/services/EventLogService?wsdl


After you type in the url, if Visual Studio's been able to communicate with the service, it will display the methods available in the service. In my case, there are only 2 methods - event_action and get_events methods.
You can change the Web Reference name if you wish. In my project, I've renamed it from WebReference to EventLogReference.

5. After you've done the above successfully, you should be able to see the EventLogReference in Web References folder.

 
6. Behind the scenes, Visual Studio automatically generated a lot of codes for you for communicating to the specified service. All you need to do is to use the generated codes and call the appropriate methods.

In my project, Visual Studio generated the EventLogService class. I need to create an instance of this service and call either of the 2 available methods - eventaction and get_events methods. In my project, I will be using the event_action method. Based from the above display, this method requires an ActionRequestType object as a parameter.

Finally, here's the code in VB.NET on how to instantiate the required parameters and call the appropriate method: The code that you use must be based on how the method that you want to call must be called.



'create CREATE ACTION TYPE
Dim actType As ACTIONType = New ACTIONType()
actType.NAME = "create"

'create ACTIONREQUESTType 
Dim actionRequesttype As ACTIONREQUESTType = New ACTIONREQUESTType()
actionRequesttype.ACTION = actType

'call Service
Dim eventLog As EventLogService = New EventLogService()

Dim response As ACTIONRESPONSEType = eventLog.event_action(actionRequesttype)

If Not response Is Nothing AndAlso Not response.ACTIONSTATUS Is Nothing _
   AndAlso Not response.ACTIONSTATUS.PARAM Is Nothing _
   AndAlso response.ACTIONSTATUS.PARAM.Count > 0 _
   Then
   result = response.ACTIONSTATUS.PARAM(0).VALUE
End If