MYSQL common functionalities.

Here are some of the basic functionalities of mysql

1. Installing a mysql Server.

2. Setting up the system environment for mysql.

3. Starting and terminating mysql service.

4. Connecting to mysql server through command line client.

5. Backup mysql db.

6. Restore mysql db.

Installing mysql server.

1.  Download the latest version of mysql server from here 
2. Download the query browser form here

Users can choose to choose to typical install if just need server to be configured with command line tool. Or choose to install the mysql workbench by choosing either custom or complete installation.

Setting up system environment for mysql.

To set up system environment for mysql follow the following steps.

1. Right click on My Computer and click on properties.

2. In case of XP , from the properties window click on Advance tab.

In case of Windows7 , from the properties window click on advanced system settings.

3. From the advance tab, click Environment variables.

4. From the Environment variables window choose path variable from system variables, and click on edit

5. Now append the mysql path to the path variable. Mysql ll be usually present in “C:\Program Files\MySQL\xyz\bin” 

In my case it is “C:\Program Files\MySQL\MySQL Server 5.6\bin”

6. Click OK.

Find the snapshots below for the same.

Advance properties

Environment Variables

path variable 

To test the configuration 

1. Open RUN command and type ‘mysqld’ and press enter.

( For those who don’t know how to open RUN command

Press Windows key+R ).

2. If a console pops up and closes, system environment has been configured for mysql.

Starting and Terminating mysql service.

To start mysql service 

1. Open RUN command, type ‘mysqld’. The mysql service ‘ll be started.

To terminate mysql service

1. Open command prompt. 

2. Type in the following commands.

“mysqladmin -u <username> -p shutdown”

Eg:  mysqladmin -u root -p shutdown.

3. The console ‘ll prompt for password, enter the password and press enter.

Mysql service ‘ll be terminated.

Connecting to mysql client through command line client

To connect to mysql server, 

1. Open command prompt

2. Type in following command

“mysql -h <hostname> -u <username> -p<password> dbname” //not recommended

“mysql -h <hostname> -u <username> -p dbname” // recommended

3. Type in the password. Connection is created if u can see the shell change to mysql.

Below find the snapshots

Mysql Connect 

Mysql Connection Success

Backup mysql db

There are different ways to backup MYSQL db. Here i share 2 methods of doing them.

1. Mysql Command Line client.

2. Mysql Query browser.

Backup through Mysql command line client.

To backup only tables and data.

1. Open command prompt.

2. Type in the below commands.

“mysqldump -h <hostname> -u <username> -p --databases dbname1 dbname2 >backup_location”

Usage : “ mysqldump -h localhost -u root -p --databases test1 test2 >c:\backup\backup.sql”

3. The console ‘ll prompt for password. Enter the password,  the backup ll be stored at the specified backup location.

To backup Entire database including procedures and triggers.

mysqldump -h <hostname> -u <username> -p --routines --databases dbname1 dbname2 >backup_location

To backup all databases.

mysqldump -h <hostname> -u <username> -p --routines --all-databases  >backup_location

Restore Mysql DB.

To restore mysql db.

1. Open command prompt.

2. Type  in the following commands

“mysql -h <hostname> -u <username> -p dbname < backupfile.sql”

Enter  the password when prompted.

OR

“mysql -h <hostname> -u <username> -p dbname”

Enter the password when prompted.

“source backupfile.sql”

Or

“\. backupfile.sql”

Backup through mysql query browser

Content ll be updated soon.....

AutoUpdating of C# Application.

Updating the software to customers every time manually is gruesome task.This consumes lots of time of effort.

           Example consider , releasing a new version within short span of time, or installing it to large no of customers really tough right. 

Here i share a simple way of updating the software Automatically.

The main idea behind Auto-updating the software is,

1. Provide a way to track the latest update available , this can be achieved through updating the version in a Settings file or DB. In this example am using a settings file.
   
   
   
   
2.  Replacing the setup with a newer version.This can be done in 2 ways.
1. Probably the easiest one, Set the DetectNewInstalledVersion and ReplacePreviousVersions property in visual studio to true.
   


2. Using WMI to read all the software's installed then uninstall the previous version of the application being upgraded. The code is explained below.                                                  Note: Add a reference to System.Management object.
   
   
    
   
   
   
   
   
   
   
   
   


3. Installing the new version ,

First step in creating this application is hosting the settings file.

       The best place would be in a server accessible to all the users.

Assuming that u have hosted the settings file in your server "http://myserver/" ,
For the first time , the application has to be installed manually, later on for the next updates setup files can be placed in the server.
lets continue with Coding part.

Creating a form

 

The form basically consists of 2 labels , progressbar , button and a backgroundworker.

Dwelling into the functionality.

    The  Application mainly consists of the following functions.

1. private void Form1_Load(object sender, EventArgs e).
2. private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
3.  private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
4. private void btnupdate_Click(object sender, EventArgs e)
5. public void CheckUpdate()
6. private bool installupdates()
7. private void unisntall()
8. private void downloadFiles(string filename)  

Lets understand the functions more clearly.

---

1.  FORM LOAD event.

         In the form load event , just display the product version.

        private void Form1_Load(object sender, EventArgs e)
        {
            lblversion.Text = this.ProductVersion;
        }

---

2. ButtonClick event.

     This this we trigger the background job to start.

        private void btnupdate_Click(object sender, EventArgs e)
        {
            if(!backgroundWorker1.IsBusy)
                backgroundWorker1.RunWorkerAsync();
        }

---

3. Backgroundworker_Dowork event.

        Here we call the CheckUpdate() to perform the update tasks.

         private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        { 
            if(!backgroundWorker1.IsBusy)
                CheckUpdate();
        }

---

4. Checkupdate function

       performs the update tasks.

         /// <summary>
        /// performs the update tasks
        /// </summary>
        public void CheckUpdate()
        {
            /// gets the product current version.
            version = new Version(this.ProductVersion);
            DataSet ds = new DataSet();
            try
            {
                /// downloads the settings file containing the version information from the server.
                downloadFiles("version.xml");
                ds.ReadXml(@"D:\test\version.xml");
                backgroundWorker1.ReportProgress(40);
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    /// compares the product version and the latest version available from the server.
                    if (version.CompareTo(new Version(row[0].ToString())) < 0)
                    {
                        ///update available.
                        ///downloads the updates(setup file).
                        backgroundWorker1.ReportProgress(70);
                        downloadFiles("application_name.msi");

                        //installs the updates.
                        isInstalled=installupdates();
                        if (isInstalled)
                        {
                            backgroundWorker1.ReportProgress(100);
                            ///if u plan to uninstall manually, call this function
                            ///currently not called.
                            //  unisntall();
                        }
                        else
                            ///report error.
                            backgroundWorker1.ReportProgress(-1);
                        return;
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                backgroundWorker1.ReportProgress(-1);
                return;
            }

            backgroundWorker1.ReportProgress(99);
        }

---

5.Backgroundworker progress changed event

         /// <summary>
        /// Updates the progress bar and status of the operations.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            switch (e.ProgressPercentage)
            {
               case 40: lblstatus.Text = "Checking for updates...";
                    progressBar1.Value = e.ProgressPercentage;
                    break;
                case 70: lblstatus.Text = "Downloading necessary components...";                   
                    progressBar1.Value = e.ProgressPercentage;
                    break;
                case 80: lblstatus.Text = "Installing components...";
                    progressBar1.Value = e.ProgressPercentage;               
                    break;
                case 90: lblstatus.Text = "Removing previous components...";
                    progressBar1.Value = e.ProgressPercentage;
                    break;
                case 100: lblstatus.Text = "completing Installation";
                    progressBar1.Value = e.ProgressPercentage;                   
                    this.Hide();
                     MessageBox.Show("Restart the application for the changes to take effect.","Restart");
                     this.Close();
                    break;
                case 99: lblstatus.Text = "";
                    progressBar1.Value = 100;
                    this.Hide();
                    MessageBox.Show("Product is upto date");
                    this.Close();
                    break;
                default: lblstatus.Text = "update failed";
                    this.Hide();
                    MessageBox.Show("Update failed");
                    this.Close();
                    break;
            }

        }

---

6. InstallUpdates function

        /// <summary>
        /// Installs the new version of application
        /// </summary>
        /// <returns></returns>
        private bool installupdates()
        {
            bool appInstalled = false;
            backgroundWorker1.ReportProgress(80);
            ///creates a process to install the process.
            Process p = new Process();
            ///Msi installer
            p.StartInfo.FileName = "msiexec.exe";
            ///parameters to install the application.
            ///  /i = install
            ///  /qb = basic user interface
            p.StartInfo.Arguments = "/i \"tempfolder\application_name.msi\"/qb";
            p.Start();
            //wait for process to complete
            p.WaitForExit();
            if (p.ExitCode == 0)
                appInstalled = true;
            return appInstalled;
        }

---

6. unistall function

   uninstalls the previous version of the application

         private void unisntall()
        {
            ///selects all installed products
            ManagementObjectSearcher mos = new ManagementObjectSearcher("SELECT * FROM Win32_Product");
            ManagementObjectCollection moReturn = mos.Get();
            foreach (ManagementObject mo in moReturn)
            {
                ///checks for the application name and version
                if (mo["name"].ToString() == "setup_name" && version.CompareTo(new Version(mo["version"].ToString())) == 0)
                {

                    backgroundWorker1.ReportProgress(90);
                    //uninstalls the application
                    mo.InvokeMethod("UNINSTALL", null);
                    backgroundWorker1.ReportProgress(100);
                    return;
                }
                else
                    continue;
            }
        }

---

8. Downloadfile function

    Downloads the files from the server , "http://myserver/".

        private void downloadFiles(string filename)
        {
            try
            {
                System.Net.WebClient webclient = new System.Net.WebClient();
                webclient.DownloadFile("http://myserver/" + filename, "tempfolder" + filename);
            }
        } 

---

Last step , Creating a setup project.

Follow the steps below.

1. http://www.advancedinstaller.com/user-guide/tutorial-ai-ext-vs.html
2. http://www.thecodekey.com/Articles/C_VB/Create_Setup_in_Windows_Application.aspx

Download Source.

 

References 

1. Installing MSI through command line.
2. http://msdn.microsoft.com/en-us/library/System.Management%28v=vs.110%29.aspx
   
   
   

My First C# Application - SimpleCalculator

Here i share with all, my first windows forms application.

A Simple Calculator

First step in created this application is , Creating a Winforms design.

Below find the snapshot of the design.

Forms Design

Done with the Design ???

     Next up , Register to all the button click events.

Once done with all these, let's dig into the functionality.

Key Modules/ Functions

 1. CallValidate

 2. ValidateKey

 3.  OperandTrack

 4.  Evaluate

 

CallValidate

---

  /// <summary>
        /// Detects the key or button input, and performs the appropriate operation
        /// </summary>
        /// <param name="ch">The character being inputted through keyboard or GUI button </param>
        /// <param name="flag">performs different Operations based on the Flag Value
        /// Flag=0 validates the keypressed from keyboard
        /// flag=1:aappends the numbers to the stringBuilder
        /// flag=2: Backspace Operation
        /// Flag=3:Dot operation
        /// flag=4:Xor OPeration for +/- button
        /// Flag=5:Clear Operation </param>
        /// <param name="t">Reference to the form TextBox </param>
        /// <param name="myString">reference to the string Builder </param>
        public void CallValidate(char ch, int flag, TextBox t)
        {
            switch (flag)
            {
                case 0: if (ValidateKey(ch, t))
                        {
                            if (eflag == 1)
                            {
                                oprCount = 0;
                            }
                            myString += ch;
                            t.Text =myString;
                            consecutiveCount = 0;
                        }
                        break;

                case 1: if (eflag == 1)
                            oprCount = 0;
                        myString += ch;
                        t.Text = myString;
                        consecutiveCount = 0;
                        break;

                case 2: if (myString.Length > 0)
                        {
                            myString=myString.Remove(myString.Length-1);
                            t.Text =myString;
                            if (myString.Length < x)
                            {
                                x = 0;
                            }
                            if (t.Text.Length == 0)
                                t.Text = "0";
                        }
                        else if (t.Text.Length == 0)
                            t.Text = "0";
                      
                        break;
                 
                  
                case 3: if (x == 0)
                        {
                            myString += ch;
                            t.Text = myString;
                            consecutiveCount = 0;
                            x=myString.Length;
                        }
                        break;

                case 4: res = float.Parse(t.Text) * (-1);
                        t.Text = res.ToString();
                        prev = res;
                        break;

                case 5: t.Text = "0";
                        myString = "";
                        consecutiveCount = 0;
                        next = 0;
                        oprCount = 0;
                        prev = 0;
                        res = 0;
                        x = 0;
                        break;

            }
        }

---

ValidateKey

---

 /* Checks the keypressed and acts according to the key

        diffrentiates btwn alphabets,numeric and  specaial keys(*,+,-,/) etc*/

        public bool ValidateKey(char ch, TextBox t)

        {

             switch (ch)

            {

               /* case '1': return true;

                case '2': return true;

                case '3': return true;

                case '4': return true;

                case '5': return true;

                case '6': return true;

                case '7': return true;

                case '8': return true;

                case '9': return true;

                case '0': return true;*/

                case '+': eflag = 0;

                    OperandTrack(ch, t);

                    return false;

                case '-': eflag = 0; OperandTrack(ch, t);

                    return false;

                case '*': eflag = 0; OperandTrack(ch, t); 

                    return false;

                case '/': eflag = 0; OperandTrack(ch, t);

                    return false;

                case '=':eflag = 1;

                    OperandTrack(ch, t);

                    if (consecutiveCount >= 2)

                    {

                        consecutiveCount++;

                        if (Evaluate(res, opr, next, t))

                        {

                            prev = res;

                            t.Text = res.ToString();

                        }

                    }

                        return false;

             }

            return false;

        }

---

OperandTrack

---

         /// <summary>

        /// This function keeps track of the prev and next operands,

        /// The operator value on which the operation has to be performed

        /// 

        /// if there is 2 Distinct operators then Evaluate the result

        /// Ex:2+3+ OR 2+3= 

        /// 

        /// if there there was only a single operator with a single

        /// operand, tracks the prev operand and the operator

        /// Ex: 2* or 0+ etc 

        /// 

        /// if operators are pressed multiple times , get the current

        /// operator neglecting the previous operator

        /// Ex: 2*+ , the * is neglected and + is considered

        /// 

        /// </summary>

        /// <param name="e"></param>

        /// <param name="textBox1"></param>

        /// <param name="myString"></param>

        public void OperandTrack(char ch, TextBox textBox1)

        {

            MessageBox.Show("");

            oprCount++;            

            consecutiveCount++;

            x = 0;

            if (oprCount == 2&&consecutiveCount<=1)

            {

                eflag = 0;

                next = float.Parse(textBox1.Text);   //Sets the second operand from the textbox

                if (Evaluate(prev, opr, next, textBox1))

                {

                   prev = res; // Sets the result as the prev operand value

                    textBox1.Text = res.ToString(); // Displays the result to the Form

                    oprCount = 1;

                    if (ch != '=')

                        opr = ch;

                    myString = "";

                    //textBox1.Text = "";

                }

            }           

            else if (oprCount == 1)

            {

                opr = ch;

                prev =float.Parse(textBox1.Text);

                myString="";

                //textBox1.Text = "";

            }

            else

            {

                --oprCount;

                if(ch!='=')

                    opr = ch;

                myString="";

               // textBox1.Text = "";

            }

        }

---

Evaluate

---

/* Evaluates the Expression and Returns the Result */

        public bool Evaluate(float x, float opr, float y,TextBox t)
        {
            
            if (opr == '+')
                 res = x + y;
            
            else if (opr == '-')
                  res = x - y;

            else if (opr == '*')
                res = x * y;

            else if (opr == '/')
                    {
                        if (y == 0)
                        {
                            t.Text = "divide by zero error";
                            return false;
                        }
                        else
                            res = x / y;
                    }
            else if(opr==61)
                res = float.Parse(t.Text);
            return true;
        }
    }

---

Finally Call the functions through Button click events.

Declare the ASCII constants for backspace and Delete buttons.

        public const char back = '\u0008';
        public const char del = '\u007F';

Function Calls.

---

         private void Form1_Load(object sender, EventArgs e)
        {
            this.Focus();
        }

        private void button19_Click(object sender, EventArgs e)
        {
            k.CallValidate(back, 2, textBox1);
        }

        private void button20_Click(object sender, EventArgs e)
        {
            k.CallValidate('`', 5, textBox1);
        }
         
        private void button18_Click(object sender, EventArgs e)
        {
           k.CallValidate('=', 0, textBox1);
        }
           
        private void button17_Click(object sender, EventArgs e)
        {
            k.CallValidate(char.Parse(button17.Text), 0, textBox1);
        }

        private void button16_Click(object sender, EventArgs e)
        {
            k.CallValidate(char.Parse(button16.Text), 0, textBox1);
        }

        private void button15_Click(object sender, EventArgs e)
        {
            k.CallValidate(char.Parse(button15.Text), 0, textBox1);
        }

        private void button14_Click(object sender, EventArgs e)
        {
            k.CallValidate(char.Parse(button14.Text), 0, textBox1);
        }

        private void button13_Click(object sender, EventArgs e)
        {
            k.CallValidate('.', 3, textBox1);
        }

        private void button11_Click(object sender, EventArgs e)
        {
            k.CallValidate('-', 4, textBox1);
        }

        private void button10_Click(object sender, EventArgs e)
        {
            k.CallValidate('0', 1, textBox1);
        }

        private void button9_Click(object sender, EventArgs e)
        {
            k.CallValidate('9', 1, textBox1);
        }

        private void button8_Click(object sender, EventArgs e)
        {
            k.CallValidate('8', 1, textBox1);
        }

        private void button7_Click(object sender, EventArgs e)
        {
            k.CallValidate('7', 1, textBox1);
        }

        private void button6_Click(object sender, EventArgs e)
        {
            k.CallValidate('6', 1, textBox1);
        }

        private void button5_Click(object sender, EventArgs e)
        {
            k.CallValidate('5', 1, textBox1);
        }

        private void button4_Click(object sender, EventArgs e)
        {
            k.CallValidate('4', 1, textBox1);
        }

        private void button3_Click(object sender, EventArgs e)
        {
            k.CallValidate('3', 1, textBox1);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            k.CallValidate('2', 1, textBox1);
        }

        private void button1_Click(object sender, EventArgs e)
        {

            k.CallValidate('1', 1,textBox1);
     
        }

/// To differntiate special char's ,numbers and alphabets.

        private void Form1_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (Char.IsDigit(e.KeyChar))
                k.CallValidate(e.KeyChar, 1, textBox1);
            else if (e.KeyChar == '.')
                k.CallValidate(e.KeyChar, 3, textBox1);
            else if (e.KeyChar == back)
                k.CallValidate(e.KeyChar, 2, textBox1);
            else
                k.CallValidate(e.KeyChar, 0, textBox1);
 
        }

---

Find the link to the Application here