Skip to content

Archive

Tag: MySQL

Many times, duplicate data exists in tables that we would rather ignore. So, doing a SELECT for example would return the duplicate data, when in fact that would be a bit of a problem for us.

One way to get around this is to get MySQL to ignore the duplicate records, which can be done with the command

ALTER IGNORE TABLE testtable ADD UNIQUE INDEX(col1,col2);

What this does is creates an index on two columns in the table, getting unique rows for that index, and ignoring any additional rows that match. This won’t check the entire table for a match, but only those two columns, so it might leave out useful rows if you are not careful with which columns you select in the index.

If all is working as it should, the resulting queries on this table should return back the data, excluding duplicates.

Share

Visual Studio.NET is very geared to being paired up with Microsoft SQL Server, and natively supports each other. That would make sense, since both are Microsoft products. The downside of this is that SQL Server is not exactly cheap.

The good thing though is that Visual Studio.NET can also connect to MySQL databases, which has several advantages. Most web applications which are built around PHP use MySQL, which would then make easier interaction if your system has multiple platforms. And let us not forget that MySQL is free.

You can, of course, use the OleDB components to access a MySQL database, but there is a plugin for Visual Studio.NET which allow you to connect directly to the database, much like the SQL Server data access components work. You can download the MySQL Connector .NET 6.1 here.

Once you have installed this plugin, you need to add a reference to the MySQL.Data assembly, which you can then use in your code.

Using the methods is almost exactly the same as with doing SQL Server connections. First you create a connection using the connection string, and then create a command, which will contain the query to execute.

This command can then be executed and the result read with a reader object. Nothing particularly esoteric here if you have any experience writing database code in C#.

using System;
using MySql.Data.MySqlClient;

namespace Solarius
{
    public class DBAccess
    {
        private static string _connString = Globals.connectionString;

        public static int ExecuteNonQuery(string query)
        {
            int retVal;

            MySqlConnection connection = new MySqlConnection(DBAccess._connString);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = query;
            connection.Open();
            retVal = command.ExecuteNonQuery();
            connection.Close();
            return retVal;
        }

        public static void ExecuteReader(string query)
        {
            MySqlConnection connection = new MySqlConnection(DBAccess._connString);
            MySqlCommand command = connection.CreateCommand();
            MySqlDataReader Reader;
            command.CommandText = "select * from mycustomers";
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
            //Loop through the reader
            }
            connection.Close();
        }

        
    }
}

Share

This is one big and comprehensive book, but do not let the title fool you. This book is about both MySQL and PHP and how to use them, but the real strength is how they work together.

It is most convenient to have both these technologies in one book, since in modern PHP programming, it is almost inconceivable to use one without the other, so it makes a very handy reference.

The book also includes tutorials and case studies to show real world examples.





Share