Click here to Skip to main content
15,860,972 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.2K   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 CMediumModel : ModelSql
    {
        #region MethodsPublic

        public CMediumModel()
        {
        }

        public static CDataSetMedium GetDataSetByQueryParams(string strConnection, CMediumData queryParams)
        {
            if (queryParams == null)
            {
                throw new ArgumentNullException(
                    string.Format(
                    CultureInfo.CurrentUICulture,
                    Resources.IDS_ArgumentException,
                    "queryParams"));
            }
            
            string strSelectSql = "SELECT * FROM medium m" +
                " WHERE m.medium LIKE ?Medium" +
                " AND m.beschreibung LIKE ?Beschreibung" +
                " ORDER BY m.medium,m.beschreibung";

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

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

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

                    mySqlDataAdapter.Fill(dataSet, CDataSetMedium.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)
                    {
                        dataSet.AcceptChanges();
                    }
                }
                catch (MySqlException)
                {
                    throw;
                }
            }
            return dataSet;
        }

        public static Collection<CMediumData> GetMedia(string strConnection)
        {
            Collection<CMediumData> mediaCollection = new Collection<CMediumData>();
            string strSelectSql = "SELECT m.mediumid, m.medium, m.beschreibung," +
               " m.guid, m.timestamp" +
               " FROM medium m" +
               " ORDER BY m.medium";

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

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

                            medium.MediumId = GetInt32(mySqlDataReader, "mediumid", false, medium.MediumId);
                            medium.Medium = GetString(mySqlDataReader, "medium", false, medium.Medium);
                            medium.Beschreibung = GetString(mySqlDataReader, "beschreibung", true, medium.Beschreibung);
                            medium.Guid = GetGuid(mySqlDataReader, "guid", false, Guid.NewGuid());
                            medium.TimeStamp = GetDateTime(mySqlDataReader, "timestamp", false, new DateTime());

                            mediaCollection.Add(medium);
                        }
                    }
                }
            }
            return mediaCollection;
        }

        #endregion

        #region MethodsPrivate

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

            string strInsertSql = "INSERT INTO medium " +
                " (mediumid,medium,beschreibung,guid) " +
                " VALUES(" +
                " 0,?Medium,?Beschreibung,?Guid)";

            string strUpdateSql = "UPDATE platten.medium" +
                " SET medium =?Medium, beschreibung = ?Beschreibung" +
                " WHERE mediumid =?MediumId" +
                " 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);
                InsertParams(mySqlDataAdapter.InsertCommand);
                mySqlDataAdapter.UpdateCommand = new MySqlCommand(strUpdateSql, mySqlConnection);
                UpdateParams(mySqlDataAdapter.UpdateCommand);

                mySqlDataAdapter.RowUpdated += new MySqlRowUpdatedEventHandler(this.RowUpdated);

                mySqlDataAdapter.Update(dataSet, CDataSetMedium.SOURCETABLE);
            }
        }
        private void RowUpdated(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 medium m" +
                " WHERE m.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["MediumId"] = GetInt32(mySqlDataReader, "MediumId", false, -1);
                            }
                            rue.Row["Timestamp"] = GetDateTime(mySqlDataReader, "Timestamp", false, new DateTime());
                        }
                    }
                }
            }
        }

        private static void InsertParams(MySqlCommand mySqlCommand)
        {
            MySqlParameter mySqlParameter = null;
            mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("MediumId", MySqlDbType.Int32, 0));
            mySqlParameter.Direction = ParameterDirection.Input;
            mySqlParameter.SourceColumn = "MediumId";
            mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("Medium", MySqlDbType.VarChar, 5));
            mySqlParameter.Direction = ParameterDirection.Input;
            mySqlParameter.SourceColumn = "Medium";
            mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("Beschreibung", MySqlDbType.VarChar, 50));
            mySqlParameter.Direction = ParameterDirection.Input;
            mySqlParameter.SourceColumn = "Beschreibung";
            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 UpdateParams(MySqlCommand mySqlCommand)
        {
            MySqlParameter mySqlParameter = null;
            mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("MediumId", MySqlDbType.Int32, 0));
            mySqlParameter.Direction = ParameterDirection.Input;
            mySqlParameter.SourceColumn = "MediumId";
            mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("Medium", MySqlDbType.VarChar, 5));
            mySqlParameter.Direction = ParameterDirection.Input;
            mySqlParameter.SourceColumn = "Medium";
            mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("Beschreibung", MySqlDbType.VarChar, 50));
            mySqlParameter.Direction = ParameterDirection.Input;
            mySqlParameter.SourceColumn = "Beschreibung";
            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