Wednesday, June 27, 2012

#14 : StreamReader and StreamWriter + C#

StreamReader reads text files and StreamWriter writes text files. It is under the System.IO namespace. You can probably use this if you have application that requires data to be save as text file such as database logs or system logs. 

StreamReader 

You can use the following code to read the single line content of a Text file;
private void ReadTxt()
{ 
        string myTxt;
 using (StreamReader readTxt = new StreamReader(@"C:\\MyTxt.txt"))
 {
     myTxt= readTxt .ReadLine();
 }
 MessageBox.Show("Read Text!" + myTxt +, "New Text", MessageBoxButtons.OK);
 }

You can use the following code to read multi line content of a Text file. 
Then you can save each value in an array of string.

private void ReadMultiLineTxt()
{ 
        string myTxt;
 using (StreamReader readTxt = new StreamReader(@"C:\\MyTxt.txt"))
 {
     while (sr.Peek() >= 0)
            { 
               myTxt= readTxt .ReadLine();
            } 
        }
 MessageBox.Show("Read Text!" + myTxt +, "New Text", MessageBoxButtons.OK);
 }
 
StreamWriter

You can use the following code to create and write content of a Text file;

private void WriteTxt()
{ 
        string myTxt;
 using (StreamWriter writeTxt = new StreamWriter(@"C:\\MyTxt.txt"))
 {
     writeTxt .Write("Welcome");
     writeTxt .WriteLine("Hello");
     writeTxt .WriteLine("World!");
 }
 MessageBox.Show("New Text File Created!", "New Text", MessageBoxButtons.OK);
 }

You can use the following code to write and append content of a Text file;

private void WriteAppendTxt()
{ 
        string myTxt;
 using (StreamWriter writeTxt = new StreamWriter(@"C:\\MyTxt.txt",true))
 {
     writeTxt .WriteLine("Hello");
 } 
        using (StreamWriter writeTxt = new StreamWriter(@"C:\\MyTxt.txt",true))
 {
     writeTxt .WriteLine("World!");
 }
 
       MessageBox.Show("New Text File Created!", "New Text", MessageBoxButtons.OK);
 }

 It's so easy and simple to read and write. You can use my code and play around with it.

Saturday, May 5, 2012

#13: Reading MS Excel the OLEDB Way + WPF

Sorry folks for a bit delay of this blog. I've been so busy for the past few days. Lots of projects are coming and you know the feeling of pushing yourself just to bet the deadline. Anyway, I'm back to the blogging world and hopefully this one could help you also on your projects.

 Reading Excel file can also be done using the Interop.Excel. Its basically the most common way of reading an excel data. However, I just notice that it takes so much time reading the file specially if you have lots of rows and columns. But still using Interop is fine as long as you don't require faster reading.

This time we will talk about how to read the Excel file using the OLEDB. Well, if you compare it, OLEDB is faster in reading Excel Data. Also we will build this as WPF Application.

In your Visual Studio, create new project and select Visual C# WPF Application.
 

Then in your  Xaml, type below;

<Grid>
                <DataGrid FrozenColumnCount="1" AutoGenerateColumns="True" Margin="6" Name="dgList" SelectionMode="Single" SelectionUnit="CellOrRowHeader" CanUserAddRows="False" CanUserReorderColumns="False" CanUserResizeColumns="True" CanUserResizeRows="True" CanUserSortColumns="True" ItemsSource="{Binding Path=.}" LoadingRow="dgList_LoadingRow"></DataGrid>
</Grid>

You can also drag and drop a Datagrid from the toolbox then setup properties. Just follow the above Xaml datagrid settings.


In Code behind type the following;
 


private void Window_Loaded(object sender, RoutedEventArgs e)              
           {
                System.Data.OleDb.OleDbConnection MyConnection;             
                System.Data.OleDb.OleDbDataAdapter MyCommand;
                sPath = "E:/Test.xls"; //excel file path
                MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data     

               Source=" + sPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=0\";");
                MyConnection.Open();
                MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from WorksheetName$A1:J30", 
               MyConnection);
               MyCommand.TableMappings.Add("Table", "TestTable");
               DtSet = new System.Data.DataSet();
               MyCommand.Fill(DtSet);

           dgList.ItemsSource = DtSet.Tables[0].DefaultView; 
           }

Take note that in our Oledb connection we set HDR=Yes which means that the first row will be considered as the column names. IMEX=0 also means the excel data will read as it is, but if you set it IMEX=1, this will read excel data intermixed such as number, date and strings. If you're not sure what data type under excel, you can leave it as IMEX=0.

When you run the code above, you should have the excel data loaded in the datagrid. Quite easy but that's all you need to do.

Now we just completed a WPF excel file reader. For questions and inquiries just hit the comments.


 



Saturday, April 14, 2012

Its more fun in Manila Bay

If you are looking for a romantic place to bond with your wife or girlfriend try Manila Bay Dinner Cruise. It takes only about an hour but you'll surely appreciate the beauty of our very own bay. Don't forget to bring your camera, you'll surely love to take shots as the ship starts to cruise at the bay. The price is affordable which include dinner and live acoustic band.

Personal Fun Experience. I was looking for a nice spot to get a sunset photo with my DSLR. Then it was a right timing that we are going to celebrate our 2nd wedding anniversary. We decided to celebrate it on Easter Sunday at Manila Bay Dinner Cruise. Bought a ticket online and went ahead to the cruise terminal right at the back of Folk Arts. It was a perfect location to get a nice angle for the sunset. Its more fun in Manila Bay!

Philippines is really a nice place to live. Come home Filipinos and see the beauty of our own country! Let's be proud and rebuild our country for the next generations to come!













Friday, April 6, 2012

#12: Vitamin C# for the Newbies Part 2 {CRUD}

Vitamin C# part 2 is a continuation of my previous blog in which we discuss about how to retrieve data from our database. This time we will talk about how add, edit or delete data from the database.
 
CRUD
Create, Read, Update, Delete or simply CRUD are the four basic functions for database applications. Once you know how to create such functions, you have a way to go in Software development. Let's do this in a simple way, our C sharp way.

Let's add first the action buttons in our form1.


Then add new form. In the solution explorer, right click on the project name and select Add>Windows Form as shown;

In the new form, call it Form2 and follow the layout below. Let's name the LastName textbox as txbLName, FirstName as txbFName, Birthdate as dtpBirth, Address as txbAddress and ContactNo as txbContact.
  
We will prepare Form2 to accept ID and AddRecord variable. In code behind, add the following line;
      public string ID { get; set; }
        public bool AddRecord { get; set; }


Let's go back to Form1 and add events on the add, edit, delete buttons.

CREATE
In Form1, double click on the Add button and write the following code

         Form2 frm = new Form2();
            frm.AddRecord = true;
            frm.ShowDialog();


Then in Form2, we will create the InsertRecord function below;

 public void InsertRecord(string LName, string FName, DateTime BirthDate, string Address, string ContactNo)
        {
            OleDbCommand cmd = new OleDbCommand();
            string strSql;

            strSql = "INSERT INTO tblCustomer (" +
                        "LastName, " +
                        "FirstName, " +
                        "Birthdate, " +
                        "Address, " +
                        "ContactNo " +
                        ") ";
            strSql = strSql + "values ('" +
                    LName + "', '" +
                    FName + "', '" +
                    BirthDate + "', '" +
                    Address + "', '" +
                    ContactNo + "' )";

            DataSet DtSet = new DataSet();

            string connection_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=appdata/Database1.mdb;Persist Security Info=False;";
            OleDbConnection mycon = new OleDbConnection(connection_str);
            mycon.Open();

            cmd.Connection = mycon;
            cmd.CommandText = strSql;
            cmd.ExecuteNonQuery();
            mycon.Close();         
        }


READ
In Form1, double click on the edit button and add the following code;
            Form2 frm = new Form2();
            frm.AddRecord = false;
            frm.ID = dgvCustomer.CurrentRow.Cells[0].Value.ToString();
            frm.ShowDialog();

In Form2, write the LoadCustomerData function;

DataSet DtSet = new DataSet();
string connection_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=appdata/Database1.mdb;Persist Security Info=False;";
            OleDbConnection mycon = new OleDbConnection(connection_str);
            mycon.Open();
            string strSql;

            strSql = "SELECT * FROM tblCustomer WHERE id = " + ID + " ";

            OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(strSql, mycon);
            myDataAdapter.Fill(DtSet);

            if (DtSet.Tables[0].Rows.Count > 0)
            {
                txbLName.Text = DtSet.Tables[0].Rows[0][1].ToString();
                txbFName.Text = DtSet.Tables[0].Rows[0][2].ToString();
                dtpBirth.Value = Convert.ToDateTime(DtSet.Tables[0].Rows[0][3]);
                txbAddress.Text = DtSet.Tables[0].Rows[0][4].ToString();
                txbContact.Text = DtSet.Tables[0].Rows[0][5].ToString();
            }

            mycon.Close();


Then call this in form load;

 private void Form2_Load(object sender, EventArgs e)
        {
            if (AddRecord == false)
            {
                LoadCustomerData();
            }
        }


UPDATE
In Form2, let's create the UpdateRecord Function;

public void UpdateRecord(string ID, string LName, string FName, DateTime BirthDate, string Address, string ContactNo)
        {
            OleDbCommand cmd = new OleDbCommand();
            string strSql;

            strSql = "UPDATE tblCustomer SET " +
                "LastName = '" + LName + "', " +
                "FirstName = '" + FName + "', " +
                "Birthdate = '" + BirthDate + "', " +
                "Address = '" + Address + "', " +
                "ContactNo = '" + ContactNo + "' " +
                " WHERE id = " + ID + " ";

            DataSet DtSet = new DataSet();
            string connection_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=appdata/Database1.mdb;Persist Security Info=False;";
            OleDbConnection mycon = new OleDbConnection(connection_str);
            mycon.Open();

            cmd.Connection = mycon;
            cmd.CommandText = strSql;
            cmd.ExecuteNonQuery();

            mycon.Close();
        }

Now double click on the OK button and add the following line;

 if (AddRecord == true)
            {
                InsertRecord(txbLName.Text, txbFName.Text, dtpBirth.Value, txbAddress.Text, txbContact.Text);

            }
            else { UpdateRecord(ID, txbLName.Text, txbFName.Text, dtpBirth.Value, txbAddress.Text, txbContact.Text); }
            this.Close();


Our IF condition tell us that if the ADD button in Form1 is clicked, then AddRecord is true in Form2 thus it executes the InsertRecord function which will add new record in the database once the user clicks the OK button. IF it is false, then it executes the UpdateRecord function

DELETE
In Form1, add the DeleteRecord Function;

public void DeleteRecord(string ID)
        {
            OleDbCommand cmd = new OleDbCommand();
            string strSql;

            strSql = "DELETE  FROM tblCustomer " +
                    " WHERE id = " + ID + " ";

            DataSet DtSet = new DataSet();
            string connection_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=appdata/Database1.mdb;Persist Security Info=False;";
            OleDbConnection mycon = new OleDbConnection(connection_str);
            mycon.Open();

            cmd.Connection = mycon;
            cmd.CommandText = strSql;
            cmd.ExecuteNonQuery();

            mycon.Close();
        }


Then call it in the Delete button event

DeleteRecord(dgvCustomer.CurrentRow.Cells[0].Value.ToString());

To refresh your data in Form1, you just have to call the LoadAllCustomerData function;

private void LoadAllCustomerData()
        {
            DataSet DtSet = new DataSet();
            string connection_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=appdata/Database1.mdb;Persist Security Info=False;";
            OleDbConnection mycon = new OleDbConnection(connection_str);
            mycon.Open();
            string strSql;

            strSql = "SELECT * FROM tblCustomer ";

           OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(strSql, mycon);
           myDataAdapter.Fill(DtSet);

           dgvCustomer.DataSource = DtSet.Tables[0];
           mycon.Close();
        }


Now we have successfully created the basic functions in database applications. Good luck to your future project folks. Happy coding!

Saturday, March 24, 2012

#11: Vitamin C# for the newbies

This blog is intended for those who want to learn how to create a database driven windows application in .Net platform. Though I am not that expert but I just want to share what I had learned especially for those who wants to pursue a career in Software development. I require you to have Microsoft Visual Studio 2010 and MS Access installed in your PC.

Vitamin C#?

I know most of us are aware that Vitamin C is good for our immune system. But we're not talkin' about vitamins here. What I am trying to say is that let's take a look at the C# not as a guitar chord but as a coding standard. I am referring to Microsoft's C# programming language. Check wiki for the history and background of this language.

Let's start from the very beginning

A very good to start is, to start from the very beginning. Yeah you're right I got it from a song but we'll apply it in software development. Let's say we want to have a Customer list data application. We need to retrieve the data from the database and display it in the datagrid. For this example, we will use MS Access database. 

The Database
Open MS Access and create new database and call it Database1. Be sure to know the exact path of this database. In your Access DB, create new table and call it tblCustomer. Add ID, LastName, FirstName, Birthdate, Address and ContactNo datafields as shown below;


Now, let's add sample data on the database then close Access application.


The Project
Open Visual Studio and create new project. Select Visual C# Windows in the project template. Name the project as CustomerApp. Click browse to select the project path and click OK when done.



By default, the template will create a windows form and the Solution folder for the CustomerApp. Right click on the CustomerApp project and select add new folder then name it appdata. Again, right click on the CustomerApp project and select add Existing Item then browse to the access database that we just created.

Just cancel the Data Source configuration wizard. We will create our own data source configuration. Go to the form and add a datagrid then name it dgvCustomer;


The Codes
Double click on the form to view the code behind. By default, the following code should be seen;



Since we are using the Access Database, we need to declare OleDB by adding the following line;

using System.Data.OleDb; 

Now, we will create a function to call the data from the tblCustomer and display it in our datagrid.

 private void LoadCustomerData()
   {
      DataSet DtSet = new DataSet();

     //set the datasource

      string conn= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=appdata/Database1.mdb;Persist Security  Info=False;";
      OleDbConnection mycon = new OleDbConnection(
conn);
      mycon.Open();
      string strSql;
      strSql = "SELECT * FROM tblCustomer ";
 

      OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(strSql, mycon);
      myDataAdapter.Fill(DtSet);

     dgvCustomer.DataSource = DtSet.Tables[0];

    mycon.Close();
    }


Then call LoadCustomerData in the form load event such as;

 private void Form1_Load(object sender, EventArgs e)
        {
            LoadCustomerData();
        }


Now, try running the project by pressing F5. You should see the following output;


Saturday, March 17, 2012

It's more fun in Boracay

One of the best summer getaway here in the Philippines is the Boracay Island. It is one of the famous tourist destination and in fact it is currently holding the title as the world's second best beach. Accordingly, the name Boracay was derive from the word "borac" which means white cotton because of its white powdery sand.


Boracay is an Island in the Western Visayas which is  under the administrative control of the Philippine Tourism Authority in coordination with the Provincial Government of Aklan. It has a total land area of 10.32 square kilometers. It was believe that boracay was home of the Ati Tribe. But by 1990s, the beaches was developed and become a popular tourist destination up to this day.




How to go to Boracay?


From Manila, you can catch up flights going to Caticlan or via Kalibo. When in Kalibo, there are available buses or vans for the tourist going to pier. I haven't tried Caticlan but I am sure there are available vehicles that would take you to the pier station. It take us about two (2) hours from the Kalibo airport to the pier station. Its far but you get the chance to look around Kalibo. For first timers, I recommend to stop by Kalibo and see the beauty within.


From the pier station, there are available boat going to the Boracay Island. 


The Beach

Boracay is known for its beautiful beach. We stayed at the  La Carmela de boracay resort but there are also a lot of resorts within Station 2 which you can choose to stay. A lot of activities you can do when you're in Boracay. You can have Island hopping, snorkeling, scuba diving, helmet diving and a lot more. For the food, if you are into tight budget, there are affordable food counters like Mang Inasal, Andoks, eat-all-you can buffets and other food stores no space to mention here. At night, there are live bands, fire dancing and bar parties.


I've taken some photos, take a look and enjoy the show!














Saturday, March 10, 2012

#10: WCF Service with Entity Framework

On my previous blog, we created a WCF service. This time I'll show you how to use a WCF service with the Entity Framework. First, we will create an ADO.Net Data Entity, then we will create a list collection Class in which we use to call the data in our Entity table.

In the solutions folder, right click on the project and click add new item. Under add new item window, select Ado.Net Entity Data model.



Under the data source, select which data provider. I am using a MySQL database, thus I used the MySQL Data provider.


Be sure to check on the Save entity connection setting in Web Config. This will generate a connection string on your web config.
Now select which table, views or stored procedure you want to use for this entity. Click finish and we have now a data entity model.


Here's how to use your entity model. First, let's create a class in which we will collect the data from our entity. In the solutions, right click on your project and add new item, select Class and name it TestDataClass.cs and follow the ff code;

 [DataContract ]
    public class TestDataClass
    {
        [DataMember]
        public DateTime TimeOff { get; set; }

        [DataMember]
        public string Cause { get; set; }

        [DataMember]
        public decimal Duration { get; set; }

        [DataMember]
        public decimal Customer { get; set; }

        [DataMember]
        public decimal TotalOutage { get; set; }

        [DataMember]
        public string UtilityName { get; set; }

        [DataMember]
        public int ID { get; set; }

    }


The above data member are the matching data on my entity table. You can create your own data member class depending on your table schema. I created this one so that I can create a list collection data which I will call it on the TestService.svc (we created the TestService.svc on the previous blog http://leeandotnet.blogspot.com/2012/03/9-wcf-service-application.html) 

Add the code below on the TestService.svc;

public List<TestDataClass> GetAllResultData()
        {
            List<TestDataClass> lstTestData = new List<TestDataClass>();
            using (aerinetdbEntities  aerinetDB = new aerinetdbEntities())
            {

                var id = from p in aerinetDB .testwcfs  
                              select p;
                foreach (testwcf b in id)
                {
                    TestDataClass a = new TestDataClass();
                    a.TimeOff = b.TimeOff.Value;
                    a.Cause = b.Cause;
                    a.Duration = b.Duration.Value;
                    a.Customer = b.Customer.Value;
                    a.TotalOutage = b.Customer.Value ;
                    a.UtilityName = b.UtilityName;
                    a.ID = b.id;

                    lstTestData.Add(a);
                }
            }
            return lstTestData;
        }



Then your ITestService, add this;

 [OperationContract]
        List<TestDataClass>  GetAllResultData ();

Build your WCF project and publish.

You can call this by adding the WCF to your service referrence and using the code below. Let's just say that we want to display this on our datagrid named dgvList.

             MyTestService = New AerinetWCFWebService.TestServiceClient() ;

             AerinetWCFWebService.TestDataClass  lstData  =  new AerinetWCFWebService.TestDataClass();
          
            dgvList.DataSource = AerinetService.GetAllResultData() 





Friday, March 2, 2012

It's more fun in CDO

Time to break the ice, stop coding and have fun! Anyway, it's summer time as announce by Pag Asa days ago. This is my first contribution to "Its more fun in the Philippines". For me yes it is so much fun living in your own country but its a sad reality that most filipinos living here wants to go abroad. Even the government promote College and University students to take courses which are most demand job abroad. But on the lighter side, Pinoys, no matter how hard life is, still have time to make some fun.

Chada CDO!. This happens about a month ago. Me and my lovely wife attended her sisters' wedding in Cagayan de Oro city. Its my third time visiting this city and all I can say is "Wah", I mean wonderful! Here's a shot at the  CDO airport;


Before airport touchdown, I saw the villages that was wiped out when Typhoon Sendong strikes the city. Its a sad story but all we can do is pray and hope it won't happen again.

CDO is a nice place, not so traffic like Manila. Well, we really didn't roam around the city, its a big place and we have a little time to prepare for the wedding.

Introducing the Dahilayan  Adventure. When you're in CDO, one of the favorite destination is the Dahilayan Adventure Park which is an hour and half drive from the city. Dahilayan is famous with their Longest Dual Zipline, which they claim as Asia's Longest Dual Zipline.

http://www.dahilayanadventurepark.com

Before reaching Dahilayan, we stop first at Camp Phillips to take some pictures. This place is known because of the Del Monte plantation.


This is the biggest pineapple plantation as far as I had seen. Also, you can find the biggest pineapple here. 
The Biggest Pineapple in the world! (But you can't eat this)

 

The road going to Dahilayan is so tough. But when you reach the place, its a perfect spot to take a rest. The temperature is so cool like Baguio City.


We stop at Dahilayan Forest Park. Check out some fees here;



There are also available rooms if you want to stay overnight. But you know what's more fun here? of course the longest Zipline. 

Me (left) and my travel buddy also known as the "groom" BJay

Fly like a Superman

smile and tell the world you survive!

P. S.: Please check your vital signs before doing the Zipline. This is an extreme adventure, like flying without wings!











Thursday, March 1, 2012

#9 : WCF Service Application


Windows Communication Foundation or simply we call it WCF, is part of .Net framework that provides a programming model for building service- oriented applications. You can use it to communicate services across the web or within the enterprise applications. Consider a scenario when you want to develop an application that can be accessible by other software running inside or outside your company. WCF is the best way to develop such application.

Let's start with building a simple WCF Service Application. Open Visual Studio 2010 and create new project. Select WCF Service Application as shown below;


Type in the name of this service. In this example, we will it MyFirstWCF. When you click OK, you will notice that the selected template will automatically create Service1.svc and IService1.cs. Since we want to create from scratch, remove Service1.svc and IService1.cs. On the Solution explorer, right click on your project name and select add new item. Select WCF service and name it TestService.svc.


This will create a TestService.svc and ITestService.cs. Check you Solution explorer you should have the items as shown;



Double click on TestService.svc  and type the code below;

public string SayHi(String yourname)
        {
            return "Hi " + yourname;

        }

Go to ITService.cs and type the code below;

 [OperationContract]
        string SayHi(String yourname);

Go back to TestService.svc  and press F5. The WCF Test Client window will show;



To test the service, double click on the "SayHi" type a name under the Request value and click invoke. The service should have a Response value "Hi " + name.

Our first WCF Service Application is done. You can now deploy it in the web or in your own local IIS. Then add this service in your Client application. You can call this service by using the ff;

static void Main()
    {
        TestServiceClient client = new TestServiceClient();
        client.SayHi("YourName")
        client.Close();
    }