How to import MS SQL Server Data into MS Access database
The following simple code will import the MS SQL Server data/table into an Access Database. It uses ADO.
1 2 3 4 5 6 7 | 'Set Reference to ADODB Private Sub Import_MS_SQL_Server_Data_Into_MS_Access() Dim cn As New Adodb.Connection cn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=Your_Access_Database.mdb;DefaultDir=C:\test;Uid=admin;Pwd=admin;" cn.Execute "SELECT * INTO [table_data] FROM [ODBC;Driver=SQL Server; SERVER=MySQL_Server;DATABASE=MySQL_DB;UID=MySQL_User;PWD=MySQL_Password;].[table_data];" cn = Nothing End Sub |
Get Maximum duplicated value in a array list
The below code give you the Maximum Value with have duplicated in the provided Array of Integer.
I have used Hashtable and try to Sort the Hashtable by Value instead of Key and get the respective Key of the maximum value data.
public class Testing {
//Type 1
public int GetMaxDuplicateValueType1(int[] ValueList)
{
Hashtable slData = new Hashtable();
int maxDuplicateCount=0;
int maxDuplicate = 0;
foreach(int key in ValueList)
{
if (slData.Contains(key))
{
slData[key] = (int)slData[key] + 1;
if ((int)slData[key] > maxDuplicateCount)
{
maxDuplicate = key;
maxDuplicateCount = (int)slData[key];
}
}
else
slData.Add(key, 0);
}
return maxDuplicate;
}
//Type 2
public int GetMaxDuplicateValueType2(int[] ValueList)
{
Hashtable slData = new Hashtable();
foreach(int value in ValueList)
{
if (slData.Contains(value))
slData[value] = (int)slData[value] + 1;
else
slData.Add(value, 0);
}
ArrayList aList = new ArrayList(slData.Values);
aList.Sort();
int MaxDuplicateCount = (int)aList[aList.Count - 1];
foreach (int Key in slData.Keys)
{
if (slData[Key].Equals(MaxDuplicateCount))
return Key;
}
return 0;
}
static void Main(string[] args)
{
TestAppLibrary objTest = new TestAppLibrary();
int[] intArray = new int[10];
intArray[0] = 100;
intArray[1] = 200;
intArray[2] = 100;
intArray[3] = 40;
intArray[4] = 100;
intArray[5] = 300;
intArray[6] = 100;
intArray[7] = 2100;
intArray[8] = 400;
intArray[0] = 400;
Console.WriteLine("Maximum Duplicate value: {0}", objTest.GetMaxDuplicateValue(intArray));
Console.ReadLine();
}
}
Send Meeting Request using Smtp methods
Below code will help to send a meeting request to the give attendees. It is dynamicaly creating the .ics file which is hidden properties structure file for any appointment / calendar type action in Outlook.
Onces send the attendees will see the meeting requested in the mailbox and in their calendar after accepting. The code is tested in Outlook 2003 and 2007.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 | //Create Meeting Request - Mail Message public static MailMessage CreateMeetingRequest(DateTime start, DateTime end, string subject, string summary, string location, string organizerName, string organizerEmail, string locationEmail, Hashtable attendeeEmailList) { MailAddressCollection col = new MailAddressCollection(); foreach (DictionaryEntry de in attendeeEmailList) col.Add(new MailAddress(de.Value.ToString(), de.Key.ToString())); return CreateMeetingRequest(start, end, subject, summary, location, organizerName, organizerEmail, new MailAddress(locationEmail, location), col); } public static MailMessage CreateMeetingRequest(DateTime dtStart, DateTime dtEnd, string strSubject, string strSummary, string strLocation, string strOrganizerName, string strOrganizerEmail, MailAddress metRoom, MailAddressCollection metAttendeeList) { //Create an instance of mail message MailMessage mmMessage = new MailMessage(); // Set up the different mime types contained in the message System.Net.Mime.ContentType typeText = new System.Net.Mime.ContentType("text/plain"); System.Net.Mime.ContentType typeHTML = new System.Net.Mime.ContentType("text/html"); System.Net.Mime.ContentType typeCalendar = new System.Net.Mime.ContentType("text/calendar"); // Add parameters to the calendar header typeCalendar.Parameters.Add("method", "REQUEST"); typeCalendar.Parameters.Add("name", "meeting.ics"); // Create message body parts in text format string strBodyText = "Type:Single Meeting\r\nOrganizer: {0}\r\nStart Time:{1}\r\nEnd Time:{2}\r\nTime Zone:{3}\r\nLocation: {4}\r\n\r\n*~*~*~*~*~*~*~*~*~*\r\n\r\n{5}"; strBodyText = string.Format(strBodyText, strOrganizerName, dtStart.ToLongDateString() + " " + dtStart.ToLongTimeString(), dtEnd.ToLongDateString() + " " + dtEnd.ToLongTimeString(), System.TimeZone.CurrentTimeZone.StandardName, strLocation, strSummary); AlternateView viewText = AlternateView.CreateAlternateViewFromString(strBodyText, typeText); mmMessage.AlternateViews.Add(viewText); //Create the Body in HTML format string strBodyHTML = "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 3.2//EN\">\r\n<HTML>\r\n<HEAD>\r\n<META HTTP-EQUIV=\"Content-Type\" CONTENT=\"text/html; charset=utf-8\">\r\n<META NAME=\"Generator\" CONTENT=\"MS Exchange Server version 6.5.7652.24\">\r\n<TITLE>{0}</TITLE>\r\n</HEAD>\r\n<BODY>\r\n<!-- Converted from text/plain format -->\r\n<P><FONT SIZE=2>Type:Single Meeting<BR>\r\nOrganizer:{1}<BR>\r\nStart Time:{2}<BR>\r\nEnd Time:{3}<BR>\r\nTime Zone:{4}<BR>\r\nLocation:{5}<BR>\r\n<BR>\r\n*~*~*~*~*~*~*~*~*~*<BR>\r\n<BR>\r\n{6}<BR>\r\n</FONT>\r\n</P>\r\n\r\n</BODY>\r\n</HTML>"; strBodyHTML = string.Format(strBodyHTML, strSummary, strOrganizerName, dtStart.ToLongDateString() + " " + dtStart.ToLongTimeString(), dtEnd.ToLongDateString() + " " + dtEnd.ToLongTimeString(), System.TimeZone.CurrentTimeZone.StandardName, strLocation, strSummary); AlternateView viewHTML = AlternateView.CreateAlternateViewFromString(strBodyHTML, typeHTML); mmMessage.AlternateViews.Add(viewHTML); //Create the Body in VCALENDAR format string strCalDateFormat = "yyyyMMddTHHmmssZ"; string strTextBodyCalendar = "BEGIN:VCALENDAR\r\nPRODID:-//Microsoft Corporation//Outlook 10.0 MIMEDIR//EN\r\nVERSION:2.0\r\nMETHOD:REQUEST\r\nBEGIN:VEVENT\r\nATTENDEE;CN=NMK;ROLE=REQ-PARTICIPANT;RSVP=TRUE:MAILTO:Manikandan.Narayannan@merrillcorp.com\r\nATTENDEE;CN=#RES-MTSI-CONF-Ardelis;ROLE=NON-PARTICIPANT;RSVP=TRUE:MAILTO:Ardelis@merrillcorp.com\r\nATTENDEE;CN=Sri;ROLE=REQ-PARTICIPANT;RSVP=TRUE:MAILTO:Sriraman.Srinivasan@merrillcorp.com\r\nORGANIZER:MAILTO:Subash.Vasudevan@merrillcorp.com\r\nDTSTART:20100108T203000Z\r\nDTEND:20100108T121000Z\r\nLOCATION:#RES-MTSI-CONF-Ardelis\r\nTRANSP:OPAQUE\r\nSEQUENCE:0\r\nUID:040000008200E00074C5B7101A82E0080000000090AA48ECF95BCA01000000000000000010000000F720C129F5F7A34E997F9CE6AE0680C2\r\nRECURRENCE-ID:20100106\r\nDTSTAMP:20091108T145030Z\r\nDESCRIPTION:Testing\r\nSUMMARY:QRM Meeting\r\nPRIORITY:5\r\nX-MICROSOFT-CDO-IMPORTANCE:1\r\nCLASS:PUBLIC\r\nBEGIN:VALARM\r\nTRIGGER:-PT15M\r\nACTION:DISPLAY\r\nDESCRIPTION:Reminder\r\nEND:VALARM\r\nEND:VEVENT\r\nEND:VCALENDAR"; StringBuilder strBodyCalendar = new StringBuilder(); strBodyCalendar.AppendLine("BEGIN:VCALENDAR"); strBodyCalendar.AppendLine("PRODID:-//Microsoft Corporation//Outlook 10.0 MIMEDIR//EN"); strBodyCalendar.AppendLine("VERSION:2.0"); strBodyCalendar.AppendLine("METHOD:REQUEST"); strBodyCalendar.AppendLine("BEGIN:VEVENT"); foreach (MailAddress attendee in metAttendeeList) { mmMessage.To.Add(attendee); strBodyCalendar.AppendFormat("ATTENDEE;CN={0};ROLE=REQ-PARTICIPANT;RSVP=TRUE:MAILTO:{1}\r\n", attendee.DisplayName, attendee.Address); } if(!String.IsNullOrEmpty(metRoom.Address)) strBodyCalendar.AppendFormat("ATTENDEE;CN={0};ROLE=NON-PARTICIPANT;RSVP=TRUE:MAILTO:{1}\r\n", metRoom.DisplayName, metRoom.Address); strBodyCalendar.AppendFormat("ORGANIZER:MAILTO:{0}\r\n", strOrganizerEmail); strBodyCalendar.AppendFormat("DTSTART:{0}\r\n", dtStart.ToUniversalTime().ToString(strCalDateFormat)); strBodyCalendar.AppendFormat("DTEND:{0}\r\n",dtEnd.ToUniversalTime().ToString(strCalDateFormat)); strBodyCalendar.AppendFormat("LOCATION:{0}\r\n", metRoom.DisplayName); strBodyCalendar.AppendFormat("TRANSP:OPAQUE\r\n"); strBodyCalendar.AppendFormat("SEQUENCE:0\r\n"); strBodyCalendar.AppendFormat("UID:{0}\r\n", Guid.NewGuid().ToString()); strBodyCalendar.AppendFormat("DTSTAMP:{0}\r\n", DateTime.Now.ToUniversalTime().ToString(strCalDateFormat)); strBodyCalendar.AppendFormat("DESCRIPTION:{0}\r\n", strSummary); strBodyCalendar.AppendFormat("SUMMARY:{0}\r\n", strSummary); strBodyCalendar.AppendLine("PRIORITY:5"); strBodyCalendar.AppendLine("X-MICROSOFT-CDO-IMPORTANCE:1"); strBodyCalendar.AppendLine("CLASS:PUBLIC"); strBodyCalendar.AppendLine("BEGIN:VALARM"); strBodyCalendar.AppendLine("TRIGGER:-PT15M"); strBodyCalendar.AppendLine("ACTION:DISPLAY"); strBodyCalendar.AppendLine("DESCRIPTION:Reminder"); strBodyCalendar.AppendLine("END:VALARM"); strBodyCalendar.AppendLine("END:VEVENT"); strBodyCalendar.AppendLine("END:VCALENDAR"); AlternateView viewCalendar = AlternateView.CreateAlternateViewFromString(strBodyCalendar.ToString(), typeCalendar); //viewCalendar.TransferEncoding = TransferEncoding.SevenBit; mmMessage.AlternateViews.Add(viewCalendar); //Adress the message mmMessage.From = new MailAddress(strOrganizerEmail); mmMessage.Subject = strSubject; return mmMessage; } |
Calculate Standard Deviation for the given data points
Below code will give us the Standard Deviation for the list data (double type) provided.
You need to be carefully in handle 'Divide by zero' error.
Formulae for the Standard Deviation:
standard deviation
Code here :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | /// <summary> /// Return the Standard Deviation for the provide set of data array to the calling function. /// Parameter : Double Array /// </summary> public static double StandardDeviation(double[] data) { double ret = 0; double DataAverage = 0; double TotalVariance = 0; int Max = 0; try { Max = data.Length; if (Max == 0) { return ret; } DataAverage = Average(data); for (int i = 0; i < Max; i++) { TotalVariance += Math.Pow(data[i] - DataAverage, 2); } ret = Math.Sqrt(SafeDivide(TotalVariance, Max)); } catch (Exception) { throw; } return ret; } private static double Average(double[] data) { double ret = 0; double DataTotal = 0; try { for (int i = 0; i < data.Length; i++) { DataTotal += data[i]; } return SafeDivide(DataTotal, data.Length); } catch (Exception) { throw; } return ret; } private static double SafeDivide(double value1, double value2) { double ret = 0; try { if ((value1 == 0) || (value2 == 0)) { return ret; } ret = value1 / value2;</code> } catch { } return ret; } |
How to create a event in MySQL (Schedule to run a Store procedure)
Below statement will set an event on MySQL Database which will be fired at a given interval. You could execute another query or call a Stored Procedure to accomplish you daily routine tasks.
CREATE EVENT 'Event Name'
ON SCHEDULE EVERY 1 DAY -- 'Day Interval'
STARTS '2009-06-29 13:05:' -- 'Start time - Execute on the same time every day'
DO CALL 'Stored Procedure with Parameter;