MySql - Unity

1. 연결하기

1.1 MySql.Data.dll 을 임포트한다. 설치가 필요하면 다운로드 해주세요.

1.2 최초 1회 연결할 스크립트를 작성한다.

string ConnectionData = $"Server={MariaDBData.DBServerIPAdress};Database={MariaDBData.DBName};Uid ={MariaDBData.DBUserID};Pwd={MariaDBData.DBPassword};";

void Connect()
{
  		  try
            {
                using (MySqlConnection conn = new MySqlConnection(ConnectionData))
                {
                    conn.Open();
                }
                return true;
            }
            
            ...
}

2. 각종 기능 ( 예시 )

2.1 Exist

 public static bool ExistDB(string dataTableName, string columnName, string compareValue)
 {
            try
            {
                string sql = $"select * from {dataTableName}";

                using (MySqlConnection conn = new MySqlConnection(ConnectionData))
                {
                    conn.Open();

                    MySqlCommand cmd = new MySqlCommand(sql, conn);
                    MySqlDataReader reader = cmd.ExecuteReader();
                    
                    var index = reader.GetOrdinal(columnName);
                    var columnDataType = reader.GetFieldType(index);

                        while (reader.Read())
                        {
                            if (columnDataType == typeof(string))
                            {
                                if (reader.GetString(index) == input)
                                    return true;
                            }
                            else if (columnDataType == typeof(float))
                            {
                                if (reader.GetFloat(index) == float.Parse(input))
                                    return true;
                            }
                            else if (columnDataType == typeof(int))
                            {
                                if (reader.GetInt32(index) == int.Parse(input))
                                    return true;
                            }    
                        }
                        reader.Close();
                }
                return false;
            }
 }

2.2 Insert

 public static void InsertDB(string dataTableName, params (string, string)[] CNameCValuePairs)
 {
            try
            {
                var columnsNames = string.Empty;
                var columnsValues = string.Empty;

                 for (int i = 0; i < CNameCValuePairs.Length; i++)
                {
                    var current = CNameCValuePairs[i];
                    var isLastIndex = i == CNameCValuePairs.Length - 1;
                    
                    columnsNames += isLastIndex ? current.Item1 : $"{current.Item1},";
                    columnsValues += isLastIndex ? $"'{current.Item2}'" : $"'{current.Item2}',";
                }

                string sql = $"Insert Into {dataTableName} ({columnsNames}) values ({columnsValues})";

                using (MySqlConnection conn = new MySqlConnection(ConnectionData))
                {
                    conn.Open();
                    MySqlCommand cmd = new MySqlCommand(sql, conn);
                    cmd.ExecuteNonQuery();
                }
           }
 }

2.3 Update

 public static void UpdateDB(string dataTableName, string whereColumnName, string whereColumnValue, string updateColumn, string updateValue)
 {
            try
            {
                string sql = $"Update {dataTableName} Set {updateColumn} ='{updateValue}' where {whereColumnName} ='{whereColumnValue}'";

                using (MySqlConnection conn = new MySqlConnection(ConnectionData))
                {
                    conn.Open();

                    MySqlCommand cmd = new MySqlCommand(sql, conn);
                    cmd.ExecuteNonQuery();
                }
            }
}

2.4 Multiple Column Update

StringBuilder sb = new StringBuilder();

  public static void UpdateMultipleColumnDB(string dataTableName, string whereColumnName, string whereColumnValue params (string ,string)[] updateColumnValuePairs)
  {
            try
            {
                sb.Clear();
				
                // sql 작성
                for (int i = 0; i < updateColumnValuePairs.Length; i++)
                {
                    var current = updateColumnValuePairs[i];
                    var isLastIndex = i == updateColumn_Values.Length - 1;
                    
                    sb.Append(isLastIndex ? $"{current.Item1} ='{current.Item2}'" : $"{current.Item1} ='{current.Item2}',");
                }
            
            	// 적용
                string sql = $"Update {dataTableName} Set {sb}  where {whereColumnName} = '{whereColumnValue}'";
            
                using (MySqlConnection conn = new MySqlConnection(ConnectionData))
                {
                    conn.Open();

                    MySqlCommand cmd = new MySqlCommand(sql, conn);
                    cmd.ExecuteNonQuery();
                }
            }
  }

2.5 Delete

 public static void DeleteRow(string dataTableName, string whereColumnName, string whereColumnValue)
 {
            try
            {
                string sql = $"Delete From {dataTableName} where {whereColumnName} = '{whereColumnValue}'";
                AlarmConnectionDB(sql);

                using (MySqlConnection conn = new MySqlConnection(ConnectionData))
                {
                    conn.Open();
                    MySqlCommand cmd = new MySqlCommand(sql, conn);
                    cmd.ExecuteNonQuery();
                }
            }
}