Click here to Skip to main content
15,858,490 members
Articles / Desktop Programming / Windows Forms

BSEtunes

Rate me:
Please Sign up or sign in to vote.
4.67/5 (11 votes)
24 Apr 2010CPOL4 min read 64.1K   4.3K   58  
BSEtunes is a MySQL based, full manageable, networkable single or multiuser jukebox application
using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using System.Collections.ObjectModel;
using System.Globalization;
using BSE.Platten.BO.Properties;

namespace BSE.Platten.BO
{
    public class CGenreModel : ModelSql
    {
        #region MethodsPublic

        public CGenreModel()
        {
        }

        public static CDataSetGenre GetDataSetGenreByQueryParams(string strConnection, CGenreData queryParams)
        {
            if (queryParams == null)
            {
                throw new ArgumentNullException(
                    string.Format(
                    CultureInfo.CurrentUICulture,
                    Resources.IDS_ArgumentException,
                    "queryParams"));
            }
            
            string strSelectSql = "SELECT * FROM genre g" +
                " WHERE g.genre LIKE ?Genre" +
                " ORDER BY g.genre";

            CDataSetGenre dataSet = null;
            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                using (MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter())
                {
                    dataSet = new CDataSetGenre();

                    mySqlDataAdapter.SelectCommand = new MySqlCommand(strSelectSql, mySqlConnection);
                    MySqlParameter mySqlParameter = mySqlDataAdapter.SelectCommand.Parameters.Add(new MySqlParameter("Genre", MySqlDbType.VarChar, 100));
                    mySqlParameter.Direction = ParameterDirection.Input;
                    mySqlParameter.SourceColumn = "Genre";
                    mySqlParameter.Value = queryParams.Genre;

                    mySqlDataAdapter.Fill(dataSet, CDataSetGenre.SOURCETABLE);
                }
            }
            return dataSet;
        }

        public DataSet Update(string strConnection, DataSet dataSet)
        {
            if (dataSet == null)
            {
                throw new ArgumentNullException(
                    string.Format(
                    CultureInfo.CurrentUICulture,
                    Resources.IDS_ArgumentException,
                    "dataSet"));
            }

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();
                try
                {
                    if (dataSet.Tables[0].GetChanges() != null)
                    {
                        Update(dataSet, mySqlConnection);
                    }
                    if (dataSet.HasErrors == false)
                    {
                        dataSet.AcceptChanges();
                    }
                }
                catch (MySqlException)
                {
                    throw;
                }
            }
            return dataSet;
        }
        public static CGenreData GetGenreByGenreId(string strConnection, int iGenreId)
        {
            CGenreData genreData = null;
            string strSelectSql = "SELECT * FROM genre g" +
                " WHERE g.genreid = ?GenreId";

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();
                using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectSql, mySqlConnection))
                {
                    MySqlParameter mySqlParamter = mySqlCommand.Parameters.Add(new MySqlParameter("GenreId", MySqlDbType.Int32, 0));
                    mySqlParamter.Direction = ParameterDirection.Input;
                    mySqlParamter.SourceColumn = "genreid";
                    mySqlParamter.Value = iGenreId;

                    using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                    {
                        if (mySqlDataReader.Read())
                        {
                            genreData = new CGenreData{
                                GenreId = GetInt32(mySqlDataReader, "genreid", false, 0),
                                Genre = GetString(mySqlDataReader, "genre", false, null),
                            };
                        }
                    }
                }
            }
            return genreData;
        }
        public static Collection<CGenreData> GetGenres(string strConnection)
        {
            Collection<CGenreData> genreCollection = new Collection<CGenreData>();
            string strSelectSql = "SELECT g.genreid, g.genre, g.guid, g.timestamp" +
                " FROM genre g" +
                " ORDER BY g.genre";

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectSql, mySqlConnection))
                {
                    using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                    {
                        while (mySqlDataReader.Read())
                        {
                            CGenreData genre = new CGenreData();

                            genre.GenreId = GetInt32(mySqlDataReader, "genreid", false, genre.GenreId);
                            genre.Genre = GetString(mySqlDataReader, "genre", false, genre.Genre);
                            genre.Guid = GetGuid(mySqlDataReader, "guid", false, Guid.NewGuid());
                            genre.TimeStamp = GetDateTime(mySqlDataReader, "timestamp", false, new DateTime());

                            genreCollection.Add(genre);
                        }
                    }
                }
            }
            return genreCollection;
        }

        #endregion

        #region MethodsPrivate

        private void Update(DataSet dataSet, MySqlConnection mySqlConnection)
        {
            string strSelectSql = "SELECT * FROM genre g";

            string strInsertSql = "INSERT INTO genre " +
                " (genreid,genre,guid) " +
                " VALUES(" +
                "0,?Genre,?Guid)";

            string strUpdateSql = "UPDATE genre" +
                " SET genre = ?Genre" +
                " WHERE genreid = ?GenreId" +
                " AND guid = ?Guid" +
                " AND timestamp = ?Timestamp";

            using (MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(strSelectSql, mySqlConnection))
            {
                mySqlDataAdapter.MissingSchemaAction = System.Data.MissingSchemaAction.AddWithKey;
                mySqlDataAdapter.InsertCommand = new MySqlCommand(strInsertSql, mySqlConnection);
                InsertGenreParams(mySqlDataAdapter.InsertCommand);
                mySqlDataAdapter.UpdateCommand = new MySqlCommand(strUpdateSql, mySqlConnection);
                UpdateGenreParams(mySqlDataAdapter.UpdateCommand);

                mySqlDataAdapter.RowUpdated += new MySqlRowUpdatedEventHandler(GenreRowUpdated);
                mySqlDataAdapter.Update(dataSet, CDataSetGenre.SOURCETABLE);
            }
        }

        private void GenreRowUpdated(object sender, MySqlRowUpdatedEventArgs rue)
        {
            if (rue.Status == UpdateStatus.ErrorsOccurred)
            {
                rue.Status = UpdateStatus.Continue;
                rue.Row.RowError = rue.Errors.Message;
            }
            else
            {
                rue.Row.ClearErrors();
                string strSelectSql = "SELECT * FROM genre g" +
                " WHERE g.guid = ?guid";

                using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectSql, rue.Command.Connection))
                {
                    MySqlParameter mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("Guid", MySqlDbType.VarChar, 36));
                    mySqlParameter.Direction = ParameterDirection.Input;
                    mySqlParameter.SourceColumn = "guid";
                    mySqlParameter.Value = rue.Row["guid"];

                    using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                    {
                        if (mySqlDataReader.Read())
                        {
                            if (rue.StatementType == StatementType.Insert)
                            {
                                rue.Row["GenreId"] = GetInt32(mySqlDataReader, "GenreId", false, -1);
                            }
                            rue.Row["Timestamp"] = GetDateTime(mySqlDataReader, "Timestamp", false, new DateTime());
                        }
                    }
                }
            }
        }

        private static void InsertGenreParams(MySqlCommand mySqlCommand)
        {
            MySqlParameter mySqlParameter = null;
            mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("GenreId", MySqlDbType.Int32, 0));
            mySqlParameter.Direction = ParameterDirection.Input;
            mySqlParameter.SourceColumn = "genreid";
            mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("Genre", MySqlDbType.VarChar, 100));
            mySqlParameter.Direction = ParameterDirection.Input;
            mySqlParameter.SourceColumn = "genre";
            mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("Guid", MySqlDbType.VarChar, 36));
            mySqlParameter.Direction = ParameterDirection.Input;
            mySqlParameter.SourceVersion = System.Data.DataRowVersion.Original;
            mySqlParameter.IsNullable = false;
            mySqlParameter.SourceColumn = "guid";
        }

        private static void UpdateGenreParams(MySqlCommand mySqlCommand)
        {
            MySqlParameter mySqlParameter = null;
            mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("GenreId", MySqlDbType.Int32, 0));
            mySqlParameter.Direction = ParameterDirection.Input;
            mySqlParameter.SourceColumn = "genreid";
            mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("Genre", MySqlDbType.VarChar, 100));
            mySqlParameter.Direction = ParameterDirection.Input;
            mySqlParameter.SourceColumn = "genre";
            mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("Guid", MySqlDbType.VarChar, 36));
            mySqlParameter.Direction = ParameterDirection.Input;
            mySqlParameter.SourceColumn = "guid";
            mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("Timestamp", MySqlDbType.Timestamp, 0));
            mySqlParameter.Direction = ParameterDirection.Input;
            mySqlParameter.SourceColumn = "timestamp";
        }

        #endregion
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Switzerland Switzerland
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions