Categories
Search

How to create a event in MySQL (Schedule to run a Store procedure)

June 29th, 2009

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;

How to send mail using smtpclient

June 25th, 2009

You can create a Mail Message dynamically and send the mail to recepient list through smtp client. You need to use System.Net.Mail to use the smtp client class.

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
using System.Configuration;
using System.Net.Mail;
 
        public static bool SendMailNotification()
        {
            MailMessage msg = new MailMessage();
            try
            {
                StringBuilder strBody = new StringBuilder("");
                strBody.Append("<span style='font-size: 9pt; font-family: Arial'>" + team + " Message body goes here<b>" + DateTime.Now.ToString("MMMM") + "</b><br /> ");
                strBody.Append("<br/><span style='font-size: 16pt; font-family: Arial'>Message body goes here<b>" + "</b></span><br /> <br />");
 
                string[] lsMailList = "Get mail receiptent list from database";
 
                foreach (string mail in lsMailList)
                {
                    msg.To.Add(mail);
                }
                msg.From = new MailAddress("from@domain.com");
                msg.Subject = "any Subject to the mail";
                AlternateView Body = AlternateView.CreateAlternateViewFromString(strBody.ToString(), null, "text/html");
                msg.AlternateViews.Add(Body);
                SendMail(msg);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
 
        public static void SendMail(MailMessage msg)
        {
            try
            {
                SmtpClient smtp = new SmtpClient();
                smtp.Host =  ConfigurationSettings.AppSettings["MailServer"].ToString(); //relay server name
                smtp.Send(msg);
            }
            catch { }
        }

Below is a MySql trigger to insert/update other table

June 25th, 2009

Trigger in backend is very useful to report the input data in many different way.. This will be last step in performance tuning when the data double up drastically.

DROP trigger IF EXISTS “Trigger Name”;

delimiter $$
CREATE TRIGGER “Trigger Name”
AFTER INSERT ON “Table Name - where the trigger will be fired on Insert/Update”
FOR EACH ROW

BEGIN

Declare l_count, l_topErrorCount INT;
Declare l_Top1Error, l_Top2Error, l_Top3Error VARCHAR(150);

//To determine where you need to update or insert the value in other table
SELECT COUNT(*) INTO l_topErrorCount FROM “Table Name”
WHERE Team = NEW.Team
AND Date = NEW.ErrorDate
AND Report = NEW.Report;

////
You Logic goes here…..
//Retrive Top Error…//
////
IF l_topErrorCount > 0 THEN
UPDATE “Table Name”
SET Top1Error= l_Top1Error,
Top2Error=l_Top2Error,
Top3Error=l_Top3Error
WHERE Team = NEW.Team
AND Date = NEW.ErrorDate
AND Report = NEW.Report;
ELSE
INSERT INTO “Table Name”
(Date, Team, Report, Top1Error, Top2Error, Top3Error)
VALUES(NEW.ErrorDate, NEW.Team, NEW.Report, l_Top1Error, l_Top2Error, l_Top3Error);
END IF;

END;
$$

How to Write bunch of text to a file

April 22nd, 2009

Here is one of the way to Write bunch of text to flat file:

1
2
3
4
5
6
7
8
9
10
11
12
Public Sub WriteFile(ByVal sText As String, ByVal sFile As String, Optional ByVal bAppendMode As Boolean = True)
    Try
        Dim Stream_Writer As New IO.StreamWriter(sFile, bAppendMode)
        Stream_Writer.Write(sText & vbCrLf)
        Stream_Writer.Flush()
        Stream_Writer.Close()
    Catch ex As Exception
        'Statements to handle Errors
    Finally
 
    End Try
End Sub

How to Check whether given file exists or not

April 22nd, 2009

Here is the code to Check whether the given file exists or not in VB.Net:

1
2
3
Public Function IsFileExists(ByVal strFileName As String) As Boolean
    IsFileExists = System.IO.File.Exists(strFileName)
End Function