RSS

Monthly Archives: January 2012

C# – Importing a csv into a strongly typed Dataset

Often there will be a requirement to import data into your application and more often than not, when the question is asked ‘so what format will the data be in that you need to import?’ we are replied to with those 3 little letters CSV when what we really want to hear are the letters XML.

However if you can tie the client down to the format of the CSV file then all isn’t too bad.  So heres an example of how to read a csv file and import the data into a strongly typed dataset.

Step 1 – Create our demo project

First create a new Windows forms project and on the form add the following;

  • button called btnOpen
  • dataGridView called dataGridView1
  • openFileDialog called fdChooseFile

 

Our basic form

 

Step 2 – Create a strongly typed dataset

ok first we have to know the format of the incoming csv file before creating the dataset.  One this example I have created a simple csv file called fileimport.csv with the headings ‘ID’, ‘Firstname’, ‘Surname’ and ‘Address’

Quick CSV file with headings

Next add a new Dataset and drag a DataTable from the Toolbox onto the designer.  The columns of the dataTable should match those in the incoming CSV file so you end up with something like:

Creating the strongly typed dataset

Step 3 – Reading the CSV File

ok so we have a sample csv file and a strongly typed dataset which contains a data table matching the same columns as out csv file.  Now for the code to read the CSV file and populate the DataTable.  For this we will use the Microsoft Text Driver and the code will be something like;

Method to read the csv file and fill the datatable

This method accepts the datatable to fill and the path and filename details of the csv file.  Don’t worry I will wire all this in in the next step but I wanted to first show the code at the heart of this post.

Using the Microsoft Text Driver and an odbcDataAdapter we can query the csv file directly, assuming we know what we are looking for.  then using the Fill method of the adapter we can just populate the datatable and supply this as the data source for the grid view.  simple.

Step 4 – Bringing it all together

The last part is to load in the csv file in the first place and pass it too the ReadData method above.  For simplicity I have thrown all the code into the click event of the button (as shown below).  Take note of wheer I’m creating instances of the dataset and datatable  though before calling the ReadData method

code to load the csv file on the button click event

 

 
Leave a comment

Posted by on January 23, 2012 in C#, Knowledge Base

 

Unlock the ‘sa’ account in SQL Server

Just thought I would add this invaluable bit of SQL as it comes up time and time again here.  You only get a couple of attempts to give the correct password to the sa account when loggin into SQL Server before the account becomes locked.

If this happens hopefully you will have a backup admin account or able to log in via integrated authentication.

If this is the case, log in and open a new Query window and type the following:

ALTER LOGIN sa WITH PASSWORD = ‘new_password’ UNLOCK

Job done, the sa account is now unlocked ready for someone to come along and lock it out again!!