mercoledì 7 novembre 2012

Delete Commad with Null Parameter Value

Sometimes we need to make where conditions with null value.
If we use Command Paramenters the first thing we make is:

      
       SqlCommand.CommandText = "DELETE table WHERE field1 = @param1";

       SqlCommand.Parameters.Add(new SqlParameter("@param1"DBNull.Value)


Your problem is that you cannot use the '=' operator in SQL for null value.  
Instead, you need to generate different SQL for this case.
See the following example:


public void DeleteYourRecord(SqlConnection con, string param1, string param2, string param3)
        {
            SqlCommand com = new SqlCommand();
            com.Connection = con;

            com.Connection = con;
            com.CommandText = "DELETE table" +
                              "WHERE field1 = @param1 AND field2 = @param2 ";

            com.Parameters.Add(new SqlParameter("@param1", param1));
            com.Parameters.Add(new SqlParameter("@param2", param2));


            if (param3.Trim() == "")
            {
                com.CommandText += " AND field3 is null";
            }
            else
            {
                com.CommandText += " AND field3 = @param3";
                com.Parameters.Add(new SqlParameter("@param3", param3));
            }

            com.ExecuteNonQuery();
        }




My Two Cents...

martedì 30 ottobre 2012

C# Google API - Create a Calendar

Sometimes, could be useful have differents calendar, for example one for work and one for privacy, the following code shows how to create a calendar.
The example shows how specify title, summary, and color of the calendar.


private void CreateCalendar()
        {
            CalendarService service = new CalendarService("appName");
            service.setUserCredentials("yourMail@gmail.com", "yourPassword");
            service.QueryClientLoginToken();

            CalendarEntry calendar = new CalendarEntry();
            calendar.Title.Text = "Calendar Title";
            calendar.Summary.Text = "Calendar Description";
            calendar.Hidden = false;
            calendar.Color = "#2952A3";

            Uri postUri = new Uri("https://www.google.com/calendar/feeds/default/owncalendars/full");
         
            CalendarEntry createdCalendar = (CalendarEntry)service.Insert(postUri, calendar);
        }


** I want to remeber to go here http://support.google.com/mail/bin/answer.py?hl=it&answer=1173270 to get  Application-specific password

My Two Cents...

C# Google API - Delete Calendar Event

The following code shows how to delete a calendar event


private void DeleteEvent()
        {
            EventQuery query = new EventQuery();
            CalendarService service = new CalendarService("appName");

            service.setUserCredentials("yourMail@gmail.com", "yourPassword");
            service.QueryClientLoginToken();
                   
                     // to get the Uru of the calendar event/feed go to:
                 // C# Google API - Retrieve All Calendar Feeds
            query.Uri = new Uri("the Uri of the event you want to delete");

            EventFeed calFeed = service.Query(query) as EventFeed;
            AtomEntryCollection ee = calFeed.Entries;

            EventEntry ev = ee[0] as EventEntry;
            ev.Delete();
            // delete ok
        }

** I want to remeber to go here http://support.google.com/mail/bin/answer.py?hl=it&answer=1173270 to get  Application-specific password

My Two Cents...

C# Google API - Update Calendar Event

The following code shows how to update a calendar event, for example the title.


 private void UpdateEvent()
        {
            EventQuery query = new EventQuery();
            CalendarService service = new CalendarService("appName");

            service.setUserCredentials("yourMail@gmail.com", "yourPassword");
            service.QueryClientLoginToken();
                   
                      // to get the Uru of the calendar event/feed go to:
                 // C# Google API - Retrieve All Calendar Feeds
            query.Uri = new Uri("the Uri of the event");

            EventFeed calFeed = service.Query(query) as EventFeed;
            AtomEntryCollection ee = calFeed.Entries;

            EventEntry ev = ee[0] as EventEntry;

            //in this example i modify the title of the event
            //but we can modify everything
            ev.Title.Text = "write the new title of the event";

            ev.Update();
            //update ok
        }

** I want to remeber to go here http://support.google.com/mail/bin/answer.py?hl=it&answer=1173270 to get  Application-specific password

My Two Cents...

C# Google API - Insert Calendar Feed

If we want to insert a reminder/appointment (feed) in our calendar, using our application, the following code shows how to do it


private void InsertCalendarFeed()
        {
            CalendarService service = new CalendarService("cl");

            service.setUserCredentials("yourMail@gmail.com", "yourPassword");
            service.QueryClientLoginToken();

            EventEntry entry = new EventEntry();

            entry.Title.Text = txt_titolo.Text;
            entry.Content.Content = "the content of the feed";
            When w = new When();
            DateTime start = Convert.ToDateTime("start date--> dd/mm/yyyy hh:mm:ss");
            DateTime end = Convert.ToDateTime("end date--> dd/mm/yyyy hh:mm:ss");
            w.StartTime = start;
            w.EndTime = end;
          
            entry.Times.Add(w);

                      //select the calendar where the feed will be inserted
            //"cmb_calendars" is a ComboBox on the form containing the calendar.
            // view: C# Google API - Retrieve Own Calendars List
            Uri postUri = new Uri("http://www.google.com/calendar/feeds/" + ((ComboboxItem)cmb_calendars.SelectedItem).Value.ToString() + "/private/full");

            AtomEntry insertedEntry = service.Insert(postUri, entry);
             
            // insert ok
        }


** I want to remeber to go here http://support.google.com/mail/bin/answer.py?hl=it&answer=1173270 to get  Application-specific password


My Two Cents...

lunedì 29 ottobre 2012

C# Google API - Retrieve All Calendar Feeds

The following code shows how to Retrieve All Calendar Feeds


public void GetCalendarFeeds()
        {
            //"_day" is a global Hashtable. The key is yearDay(string)-->the value is composed by
            // a list of object "Impegno". In this list there is an item for each events in day.
            //in the object "Impegno" there are the seguent info: start date, end date, title, description, event's uri.
            //I use this Hashtable to make update/delete of the events.
            //when i click on the MonthCalendar i load some variables with the references
            _days.Clear();

            EventQuery query = new EventQuery();
            CalendarService service = new CalendarService("appName");

            service.setUserCredentials("yourMail@gmail.com", "yourPassword");
            service.QueryClientLoginToken();

            //"cmb_calendars" is a ComboBox on the form containing the calendar.
            // view: C# Google API - Retrieve Own Calendars List
            query.Uri = new Uri("http://www.google.com/calendar/feeds/" + ((ComboboxItem)cmb_calendars.SelectedItem).Value.ToString() + "/private/full");

            //the start/end period i want to get
            query.StartTime = DateTime.Now.AddMonths(Convert.ToInt32("n-month before"));
            query.EndTime = DateTime.Now.AddMonths(Convert.ToInt32("n-month after"));

            EventFeed calFeed = service.Query(query) as EventFeed;
            DateTime[] events = new DateTime[calFeed.Entries.Count];

            while (calFeed != null && calFeed.Entries.Count > 0)
            {
                foreach (EventEntry entry in calFeed.Entries)
                {
                    When w = entry.Times[0];
                    DateTime d = w.StartTime;
                    string day = d.Year.ToString() + d.DayOfYear.ToString();


                    // if the feed is into _day, remove it and add it else...
                    if (_days.ContainsKey(day))
                    {
                        //the list of the events per day
                        List<Impegno> even = (List<Impegno>)_days[day];
                        //below the definition of the class "Impegno" 
                        Impegno imp = new Impegno(w.StartTime, w.EndTime, entry.Title.Text, "", entry.EditUri.ToString());
                        even.Add(imp);
                        _days.Remove(day);
                        _days.Add(day, even);
                    }
                    else
                    {
                        // if the feed is not present add it
                        List<Impegno> eventi = new List<Impegno>();
                        Impegno imp = new Impegno(w.StartTime, w.EndTime, entry.Title.Text, "", entry.EditUri.ToString());
                        eventi.Add(imp);
                        _days.Add(day, eventi);
                    }

                    //calendar is a MonthCalendar on the form
                    calendar.AddBoldedDate(d);
                }

                if (calFeed.NextChunk != null)
                {
                    query.Uri = new Uri(calFeed.NextChunk);
                    calFeed = service.Query(query) as EventFeed;
                }
                else
                    calFeed = null;
            }

            calendar.UpdateBoldedDates();
        }


*The following code shows the definition of the class "Impegno"


   class Impegno
    {
        public DateTime _start;
        public DateTime _end;
        public string _title;
        public string _description;
        public string _uri;

        public Impegno(DateTime start, DateTime end, string title, string description, string uri)
        {
            _start = start;
            _end = end;
            _title = title;
            _description = description;
            _uri = uri;
        }
    }


** I want to remeber to go here http://support.google.com/mail/bin/answer.py?hl=it&answer=1173270 to get  Application-specific password

C# Google API - Retrieve Own Calendars List

I think google calendar is a very useful service. 
The following code shows how to get the list of our calendars, if we want to do our own program to getting them.


public void LoadCalendars()
 {
    //first i clear the ComboBox 
    cmb_calendars.Items.Clear();
    CalendarService service = new CalendarService("appName");
    service.setUserCredentials("yourMail@gmail.com", "yourPassword");

    CalendarQuery query = new CalendarQuery();

    query.Uri = new Uri("https://www.google.com/calendar/feeds/default/owncalendars/full");
    CalendarFeed calendarFeed = (CalendarFeed)service.Query(query);

    foreach (CalendarEntry entry in calendarFeed.Entries)
    {
        // see the code down for the definition of the "ComboboxItem" object  
        ComboboxItem item = new ComboboxItem();
        item.Text = entry.Title.Text;

        //take only the part that refers to the calendar for future use
        item.Value = entry.Id.AbsoluteUri.Substring(55);

        cmb_calendars.Items.Add(item);
    }
 }


Here the definition of the "ComboboxItem" class used above to filling the combobox
class ComboboxItem
    {
        public string Text { get; set; }
        public object Value { get; set; }

        public override string ToString()
        {
            return Text;
        }
    }

** I want to remeber to go here http://support.google.com/mail/bin/answer.py?hl=it&answer=1173270 to get  Application-specific password

My Two Cents...

venerdì 26 ottobre 2012

Getting Data From a Web Page

Sometimes could be usefull get values from web pages. 
The following code show how to achieve this scope.


WebBrowser web_browser = new WebBrowser();
web_browser.Url = new Uri("the url of the page you need to get data");
string data = web_browser.Document.GetElementById("ID of the element containing the data you need*").GetAttribute("the name of the attribute of the element **").ToString();

* you can take the ID viewing the html code of the page
** for example "value"


My Two Cents...

C# Google API - Retrieve Google Contact Image

If we have a lot of contacts, it's very useful have the photo that help us to remember. 
The following code shows how to get google contact image


string qr = "YourContactUri".Replace("base", "full");
GDataCredentials Credentials = new GDataCredentials("yourMail@gmail.com", "yourPassword");
RequestSettings settings = new RequestSettings("appName", Credentials);
settings.UseSSL = true; //if you want to use ssl connection

ContactsRequest cr = new ContactsRequest(settings);
Contact contatto = cr.Retrieve<Contact>(new Uri(qr));
Image img = Image.FromStream(cr.Service.Query(contatto.PhotoUri));
pcb_foto.Image = img; //pcb_foto is a PictureBox put on the form


* if there is no image for the contact it need to manage the exception
** I want to remeber to go here http://support.google.com/mail/bin/answer.py?hl=it&answer=1173270 to get  Application-specific password


My Two Cents...

giovedì 25 ottobre 2012

C# Google API - Delete Google Contact

The following code shows a c# method to delete a google contact.



        public void DeleteContact(ContactsRequest cr, Uri contactURL)
        {
            Contact contact = cr.Retrieve<Contact>(contactURL);

            try
            {
                cr.Delete(contact);
            }
            catch (GDataVersionConflictException e)
            {
                MessageBox.Show(e.Message);
            }
        }



The following code shows how to use the previous method.


            GDataCredentials Credentials = new GDataCredentials("yourMail@gmail.com", "yourPassword");
            RequestSettings settings = new RequestSettings("appName", Credentials);
            settings.UseSSL = true;

            ContactsRequest cr = new ContactsRequest(settings);
            Contact contatto = cr.Retrieve<Contact>(new Uri(("Contact URL"));

            DeleteContact(cr, new Uri("Contact URL"));




I want to remeber to go here http://support.google.com/mail/bin/answer.py?hl=it&answer=1173270 to get  Application-specific password